SSIS - Lesson learnt, Case is very very important
I've been tearing my hair out for the
past day trying to figure out why my SQL Server Integration services packages aren't working
correctly.Whats not working was the configuration of them.
I've set up an indirect configuration framework that uses a batch file to run
my packages. This batch file sets a group of evironment variables for the
configuration each package needs. This allows me to break down my package
configuration into smaller reuseable chunks. It also allows me to specify an
environment for the running of a package so I can have multiple environments on
the same machine (dev, test, live etc)
The reason for all this is the crap way that package configurations work in
SQL 2005. You can't config the location of the configuration server (if using
SQL configurations) unless you do it this way. You can't pass the connection
string of the configuuration server in on the command line. Nicely they have
changed it in SQL 2008 but that doesn't help me.
Whats odd is that I was getting different behaviour on 2 different servers.
Now one is at SP2 Cu3 and one is at SP3 so I could expect some differences but
debugging these has taken forever. What I found was that I was getting an
Acquire connection error during validation "[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified". This to me
suggested the connection was being set incorrectly. Due to use of indirect
configurations there are a number of areas that could be causing the
problem.
So what have I learnt well it appears that if you have indirect SQL
configurations you will have to be careful if you have different versions of
SQL.
You will have a string like <server>;<table>;<filter>, for
some reason I was setting the string as follows
"<server>";"<table>";"<filter>". I must have got this from
somewhere and it worked fine on SQL 2005 SP2. However on my dev server it
didn't, but rather than complain SQL Server Integration services just silently continues. Thanks SSIS.
Whats more the package was configured by default with the dev settings and so I
didn't notice the configuration wasn't working.
Lesson 1: Always configure you package with rubbish so that you know
if your configurations are working. (The down side is you have to pray to the
environment variable god when doing dev and you want to change any indirect
config).
Once I found that if I removed the quotes I was getting the same error in
dev. Great so now I new something in my configuration was wrong. All the values
were correct I re-copied the connection strings but no luck still the problem.
So I added some debug to output the connection strings. This should that the
connection sting was blank, what could be doing that.
Well the answer is, the configuration was. SSIS is strongly typed however in
certain areas its a bit bad at reporting bad conversions. Configurations is one
of those. I had populated my SQL Configuration table from a list of values I
had. I created a new table and put a value in and noticed that in the new table
the ConfiguredValueType was string in my table and String in the new table.
Do you notice the difference.
Yep Case.
I changed the string to String and it all worked correctly. What was annoying
was that SSIS was reading the configuration and then just failing silently to
set the values correctly. That sucks.
Lesson 2: Be careful when building system data by scratch, always
compare against the data the tool produces.
Lesson 3: SSIS is never as easy as you think. A bit like driving down
the roads in britain at the moment, stay on the main road and you will be ok.
Veer off slightly and you will hit the snow and your in a whole world of
hurt.
Lesson 4: CASE is very important. C#, XML, SSIS all have case
dependencies, get into the habbit of standarising on your
case.