Mar 302007

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’
Mar 092007

It is one of the more common questions over on the SSIS Forums. How can I check if a record exists and if so, how can I check (quickly) if it has changed. Jamie Thomson has a blog post that outlines this, and I’ve made it a sticky post on the forums. However, if you need to check many, if not all of the columns in a table for changes, Jamie’s blogged method might be a bit laborious. This is where the Konesan’s Checksum transformation comes in. Read on…

Continue reading »