SSIS Error while loading a package: "The connection xxx is not found"

When opining a DTSX file with a SSIS (SQL Server Integration Services) package, the following message appeared:

Error 1
Error loading XXX.dtsx: The connection "{XXX}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

However, none of the executables inside the package showed a red error sign and thus the big question was: WTF?

It turns out that the package was configured to use SQL Server Logging and the connection used for this log was removed!

So if you ever came across this error have a look at the package Logging or Configurations.

SQL Server 2005 Hidden Gems

I just came across an article on MSDN that lists ten great features of SQL Server 2005 you most likely not know of.

The article shows you (among others):

• how easily you can enable login/logout auditing
• boosting performance through PCC
• setting the DEFAULT_SCHEMA for a user so SQL Server no longer checks [USERNAME/SCHEMA].Objectname
• Using Dynamic Management Views (DMV's) to find the top queries on your database

SQL Server Integration Services – Unicode is not Unicode

Today I came across an UTF-8 (Unicode) file I should read into SQL Server using Integration Services (IS). Fortunately, it offers a check box "Unicode" so I though I simply activate this and I'm done.

Wrong! The flag "Unicode" inside the connection manager (for the Flat File source) does mean any Unicode it ONLY means "UTF-16". However, this is not noted inside the help:

Unicode

Indicate whether to use Unicode. If you use Unicode, you cannot specify a code page.

So, if you have to deal with UTF-8 file as I did, you need to deselect the "Unicode" check box and select as Code page "65001 (UTF-8)". Tricky.

SQL Server Integration Services (SSIS) Lookup does not like large data from SQL Server 2000

If you are using the Lookup component of SSIS inside a package and your lookup is really big (let's say 400,000 rows) and this lookup comes from an SQL Server 2000 table, you will have the strange effect that Lookup will report all rows as "not found".

All rows are then send to the error output so the data flow will look like this:

I have seen this behavior only if the data comes from SQL Server 2000, when using data from an SQL Server 2005 the component worked just fine.

The solution is simple: Just limit the cache size on the Lookup component and everything will work.