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>
Comments
There are no comments yet.