After responding to a few posts over on the SSIS forum today, I thought I’d post a few helpful tidbits.

  • If you are going to use expressions in a variable (PLEASE DO! They can be quite helpful.) and are assigning the result of the expression to a string data type, you will have to ensure that the output of the string expression can be cast to unicode (DT_WSTR in SSIS talk). Casting the output of an expression to DT_STR will result in an error. Why when they are both strings? The underlying data type of “string” inside SSIS is part of the .net framework where a string is defined as “A sealed class type representing Unicode character strings.” Official documentation here.
  • Many data types can be implicty cast to string, so in some cases you don’t have to explicitly cast to string.
  • Various date formats can be achieved in SSIS a few ways. One of the most simple is to use the CONVERT() function in Transact-SQL. A format of 101 will return a date in the form of mm/dd/yyyy. 112 will return YYYYMMDD. Very handy indeed. The other way is to use expressions inside SSIS. For this you can use variable expressions (if you need to store it) or you can use derived columns (if you need to use it inside the data flow). Script tasks/components surely can do this as well, but that’s not out of the box functionality. An expression of “YEAR(getdate()) ” will return an integer in the form of YYYY. If you want to build your own YYYYMMDD format, either by using getdate() or by converting a date value inside the flow, you can use this expression: “(DT_WSTR,4)YEAR(GETDATE()) + RIGHT(“00″ + (DT_WSTR,2)MONTH(getdate()),2) + RIGHT(“00″ + (DT_WSTR,2)DAY(getdate()),2)”
  • If you edit DtsDebugHost.exe.config, prepare to run into weird problems.
  • When using the /SET option to dtexec.exe to set the value of a variable (say MyVariable = 10), use the following format (note, variable names are CaSE SenSITiVE): \Package.Variables[User::MyVariable].Properties[Value];10
  • When selecting @@IDENTITY from SQL Server, the return type is numeric, not an integer. This is important because the temptation is to just select @@IDENTITY and store it in an SSIS Int data type. It doesn’t work that way, so cast it to INT first, in the query: select cast(@@IDENTITY as int) as ‘Ident’