Biml version of SSIS expressions

gravatar

Peter Avenant

This is the Biml version of the examples posted by bonk.dk.

published 07.24.15

last updated 07.24.15


Share

I'm merely the Biml scribe and the source article can be found here.http://ow.ly/Q18my

                    <DerivedColumns Name="DER - Biml version of SSIS expressions by bonk">
                        <Columns>
                            <#* Strings *#>
                            <!-- Filename from fully qualified pathstring -->
                            <Column Name="FileName" DataType="String" Length="200" ReplaceExisting="false">RIGHT([FilePath],FINDSTRING(REVERSE([FilePath]),"\\",1) – 1)</Column>
                            <!-- Folderpath from fully qualified pathstring -->
                            <Column Name="FolderPath" DataType="String" Length="200" ReplaceExisting="false">SUBSTRING([FilePath], 1, LEN([FilePath]) – FINDSTRING(REVERSE([FilePath] ), "\\" ,1 ) + 1)</Column>
                            <!-- Foldername from fully qualified pathstring -->
                            <!-- This is only for SSIS2012 and onwards. The TOKEN and TOKENCOUNT expressions are not in prior versions of SQL Server -->
                            <Column Name="FolderName2012" DataType="String" Length="200" ReplaceExisting="false">TOKEN[FilePath],"\\",TOKENCOUNT([FilePath],"\\") – 1)</Column>
                            <!-- For prior versions of SQL Server -->
                            <Column Name="FolderName2008" DataType="String" Length="200" ReplaceExisting="false">SUBSTRING([FilePath],LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),"\\",2) + 2,(LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),"\\",1)) – (LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),"\\",2)) – 1)</Column>
                            <!-- Replace empty strings -->
                            <!-- With SQL Server 2012 the new REPLACENULL function was implemented, making it alot easier to replace empty values. -->
                            <Column Name="ColumnName1" DataType="String" Length="200" ReplaceExisting="true">REPLACENULL([ColumnName1], "Replace Value")</Column>
                            <!-- For earlier versions of SQL Server -->
                            <Column Name="ColumnName2" DataType="String" Length="200" ReplaceExisting="true">ISNULL([ColumnName2]) ? "Replace Value" : [ColumnName2]</Column>

                            <#* Date and time *#>
                            <!-- Date from datetime -->
                            <Column Name="Date1" DataType="Date" ReplaceExisting="false">(DT_DATE)(DT_DBDATE)@[User::datetimeVariable]</Column>
                            <Column Name="Date2" DataType="Date" ReplaceExisting="false">(DT_DATE)(DT_DBDATE)[datetimeColumn]</Column>
                            <Column Name="Date3" DataType="DateTime" ReplaceExisting="false">(DT_DBTIMESTAMP)(DT_DBDATE)GETDATE()</Column>
                            <!-- Time from datetime -->
                            <Column Name="Time1" DataType="AnsiString" Length="8" CodePage="1252" ReplaceExisting="false">(DT_STR,8,1252)(DT_DBTIME)@[User::datetimeVariable]</Column>
                            <Column Name="Time2" DataType="AnsiString" Length="8" CodePage="1252" ReplaceExisting="false">(DT_STR,8,1252)(DT_DBTIME)[datetimeColumn]</Column>
                            <Column Name="Time3" DataType="AnsiString" Length="8" CodePage="1252" ReplaceExisting="false">(DT_STR,8,1252)(DT_DBTIME)GETDATE()</Column>
                            <!-- First day of the current month -->
                            <Column Name="FirstDay1" DataType="DateTime" ReplaceExisting="false">DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())</Column>
                            <Column Name="FirstDay2" DataType="DateTime" ReplaceExisting="false">(DT_DBTIMESTAMP)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())</Column>
                            <!-- Last day of the current month -->
                            <Column Name="LastDay1" DataType="DateTime" ReplaceExisting="false">DATEADD("d", -DAY(GETDATE()), DATEADD("m", 1, GETDATE()))</Column>
                            <Column Name="LastDay2" DataType="DateTime" ReplaceExisting="false">(DT_DBTIMESTAMP)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()), DATEADD("m", 1, GETDATE()))</Column>
                            <Column Name="LastDaySeconds" DataType="DateTime" ReplaceExisting="false">DATEADD("s", -1,DATEADD("d", -DAY(GETDATE()) + 1, DATEADD("m", 1, (DT_DBTIMESTAMP)(DT_DBDATE)GETDATE())))</Column>
                            <!-- Weeknumber of the month --> 
                            <Column Name="WeekNumber" DataType="Int32" ReplaceExisting="false">(DATEPART("ww",[YourDate]) – DATEPART("ww",DATEADD("d", -DAY([YourDate]) + 1, [YourDate]))) + 1</Column>
                            <!-- Datetime as concatenated string -->
                            <Column Name="DateToString" DataType="String" Length="20" ReplaceExisting="false">(DT_STR, 4, 1252)DATEPART("yyyy", @[System::StartTime]) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::StartTime]), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::StartTime]), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART(“hh”, @[System::StartTime]), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::StartTime]), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::StartTime]), 2)</Column>
                        </Columns>
                    </DerivedColumns>
You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

There are no comments yet.