Clear Thinking in Data, Cloud, and AI

Sometime its kind a hard to remember different SSIS syntax and how to use them. I build up a list to help me out. Expressions used are from AdventureWorks sample provided with Microsoft SQL Server.

Converting String to Guid in Derived column expression;

(DT_GUID)("{" + [ColumnName] + "}")

If using dynamic Sql in script component or variables, get Guid from database as String;

CAST([GuidColumn] AS NVARCHAR(60)) AS GuidColumn

Database will convert string to Guid on query submission.

Boolean expression

If incoming data type is text then use this for Boolean conversion;

(DT_BOOL)((DT_WSTR,1)Rejected == “1” ? TRUE : FALSE )

This will also handle null values in incoming data.

How to Get file name and file extension in SSIS Expression?

Suppose this is the file name;

@FileName = 6be8bf19-b715-ec11-b1cb-000d3adde0a7.xlsx

This is how we will get extension and file name;

--get file extensions
REVERSE(left(REVERSE(@[User::FileName]), FINDSTRING(REVERSE(@[User::FileName]) , "." , 1 ) - 1))

Result
------
xlsx

------get file name
SUBSTRING(@[User::FileName], 1, FINDSTRING(@[User::FileName] , "." , 1 ) - 1)

Result
------
6be8bf19-b715-ec11-b1cb-000d3adde0a7

--same result can be achieved by using this statement

REVERSE(LEFT(@[User::FileName], FINDSTRING(@[User::FileName] , "." , 1 ) - 1))

Result
------
6be8bf19-b715-ec11-b1cb-000d3adde0a7

How to get only Date from DateTime variable?

SUBSTRING( (DT_STR,50, 1256)DATEADD("DAY",-1,GETDATE()) , 1, 10)

Add to favorites