SSIS Talk

So, one of the pain points in SSIS is that the Lookup Component performs CaSE sensitive matching, as well as retaining any trailing spaces that may be in the data. So be forewarned that when using the lookup component, it may behoove you to use a derived column upstream to TRIM([SSISColumn]) your data to get rid of any leading/trailing whitespaces and to perform the same (if needed) on the SQL Server side via an SQL statement – ltrim(rtrim(SQLServerColumn)). Also, if you have differing CaSEs among the incoming data and the reference data, you may need to convert one or the other, or both to UPPER/lower case before performing the lookup.

If you’d at least like to see CaSE INsensitive searches, please visit my submission and lets see if we can get this feature added to SSIS in SQL Server 2008.

One workaround for you, should you want to use it, is to use the Fuzzy Lookup Component instead. This has its own problems, as I’ve made mention to in the above feature request submission.

Edit: Jamie Thomson submitted an identical request before me but I’ve made reference to that submission in mine. The idea was to get it in SSIS 2008. Never-the-less, Michael Entin claims this feature request has been dropped due to lack of time.

The November CTP (CTP5) has been released. Go get it and start looking into some major new features added to this release.

Officially, the only change for SSIS are to the lookup components. I’ll have to get my hands dirty to see if there are any other changes that they didn’t comment on. One thing to note, is that C# is the default language type for SSIS Script Tasks/Components in SQL Server 2008.

I’m looking forward to some non-SSIS changes, though, like T-SQL IntelliSense, Transparent Data Encryption, and FILESTREAM support.

Many users have issues when trying to use a stored procedure in an OLE DB Source component. Here are some tips:

• Ensure that there is a final select statement at the end of the stored procedure to ensure the correct resultset is returned
• At the top of the stored procedure as part of the procedure code, add the following line: SET NOCOUNT ON
• At the top of the OLE DB Source SQL statement and before you issue the EXEC YourSproc line, add the following: SET FMTONLY OFF.

This should ensure that when you click on the column mapping tab, the columns are correctly displayed.

Also another approach is to use a table variable in the stored proc. Then at the end, select from that table variable.

Jamie Thomson has posted four SSIS lookup component feature requests that have been recently closed as “fixed” by Microsoft. Hopefully these will make it into the next CTP of SQL Server 2008. Take a look because they all have very high potentials to increase your productivity and useability of SSIS.

See them here: http://blogs.conchango.com/jamiethomson/archive/2007/08/21/SSIS_3A00_-Big-improvements-to-Lookup-in-SQL-Server-2008.aspx

Over here, Oren posted a list of 15 issues with SSIS. Some are founded, most are not. Read on for more:

(more…)

Please see the following post by Bob Bojanic on the SSIS development team: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1855810&SiteID=1

It seems that a change to the CLR has caused binary incompatibilities between assemblies and the VB runtime.

To ensure you are not affected, be sure that you have SQL Server Service Pack 2 (SP2) installed.

A fellow MVP, Jeff Johnson, has been having some issues (Access is Denied) with respect to connecting to a remote SSIS service. Books Online does not, apparently contain complete information. (Reference: http://msdn2.microsoft.com/en-us/library/aa337083.aspx)

Bottom line, a few steps should be added to that document to ensure your users can connect. (The root problem is the need to keep the user out of the server’s local administrator group.)

Instead of documenting them here, I’ll just point you to the thread that contains the complete picture. http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/24eed7bd-b21a-4c64-9af2-8d0756ef727d/

In summary:

• Note: These steps are performed on the remote server that you cannot connect to
• Edit Launch Permissions to include the Distributed COM (DCOM) user group
• Be sure to customize “Access permissions” to include the Distributed COM (DCOM) user group
• Make sure all remote access boxes are checed appropriately
• Add the user account/group to the DCOM user group
• You may need to restart the SSIS Service after making these changes — in fact, I recommend it.

Last night a user posted to the SSIS Forum a situation where he needed to be able to dynamically substring one field based on the substring rules contained in a table. So I put together a package that does just this. Before we go there though, I just want to mention that there are many ways, progmatically of course, to tackle this problem. The example below strictly follows my interpretations of Bill’s challenge. There is a better way by using the split() function, but never-the-less here’s the example using substring().

(more…)

Just a quick tutorial on using a script component as a source… Follow the link for screenshots.

(more…)

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’