SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSIS: great idea, but buggy as heck.

Oh SSIS, how I love and hate you.

As DBA at my company, I don't actually develop much in SSIS. But I do have to get SSIS packages running through SQL Agent for our various ETL tasks.

The developers are by no means SQL BI experts, and their packages may not be perfect, but there's nothing technically wrong with them either. And yet, getting them to successfully run in UAT or production is a constant headache.

The first issue is the old package protection level problem. There are countless pages on the web explaining that the default protection level (encrypt sensitive with user key) is, essentially, "broken", in that a package deployed with this protection level will not run. That isn't actually true though. There are already several ETL jobs on our systems running packages with this protection level set. No proxy accounts, no funny configs, they're just deployed and they run.

Some of them. But others don't. And this is where the fun really starts.

When you set up an SSIS step in an agent job and get an error, you won't see anything useful in the job history.... sometimes. Again, we have some packages that return useful error information. Others simply say "the package execution failed". Again, there are no configuration differences that I can find anywhere between the jobs. And I know that on occasion the packages aren't configured any differently either... because it's the same package, with some slight modifications to a data flow task or some other unrelated component, that fails once the change is deployed, and gives no useful information when it used to give at least something.

In response to this problem, you'll see recommendations to use a cmdExec step in agent to launch dtexec instead of using a SSIS task step. On the plus side, this does essentially guarantee useful information in the SSIS job history. Unfortunately this introduces another problem: dtexec can't parse arguments correctly. Several of our packages have been developed with connection names that include spaces. Again, there's nothing techically wrong with this. BIDS is quite happy to let you name your connections this way, and dtexec is supposed to be able to handle them using quotes. But it doesn't.... sometimes. Specifically, it doesn't if you change your connection strings using a /connection argument to dtexec, and the connection you are changing includes a space.

Now, I know this, so I don't create connections with spaces in them. But the developers do not. And they shouldn't really need to: this is stuff that is supposed to work.

There is a way to fix it, and that is to change the quotes (but not all of them!) in the command line to "\" instead of just ". But you won't find that information anywhere you might begin looking (like in the help, or the Microsoft books on the subject, or msdn pages). And good luck getting this right when you have 10 different connections in your package, resulting in a command line with quotes all over the place.

"Use configuration files" I hear you say. Ah, the horror that is configuration files. It is usually the case that your ETL jobs will have some common connections, with some packages using a connection or two that the other packages don't. This shouldn't be an issue. If SSIS was well designed, you could create one configuration file with all of your connections, and point all of your packages to it. If a package has a connection named in the file, it should pick it up, and ignore the ones it doesn't need. But no, SSIS is not well designed. If you put connections in your config that the package doesn't use, you'll get an error. This means you end up with not just a "development" config, a "UAT" config and a "production" config. No, you wind up with a proliferation of configuration files. Often one for every package. This is a maintenance nightmare.

So how do you resolve THAT? Well, one way is to create a parent package. I like these anyway, for the purpose of control and auditing. You can create all of your connections in this package, then use script tasks to set package variables that the child packages then use (via a parent package variable configuraiton) to set their connection strings. Now you CAN have just one config file (for your parent package). But this takes a lot of tedious coding to set up, and it really should not be necessary.

The other problem with this is setting things up so you can re-run just some of the child packages in the parent. If each package was its own SQL agent job step, this would be easy. But with all the children contained in the parent you only get the one agent step. If you want to be able to execute just some components (due to partial faliure), you can:

1) Try to use checkpoints. Good luck. I don't recommend it.
2) Open the package in bids, disable the steps you don't want to run, and rerun. Doing this takes a long time, and BIDS is buggy as hell. Oh, and make sure you change it all back afterwards too.
3) Write your own logic to use yet more configuration information to decide which children to run. In my opinion this is the best solution, but again, it can be a tedious task to set up if you have a lot of child packages to conditionally execute. It is made easier if you have very well designed package architecture. Do you? As I mentioned, we don't. But what we have is supposed to work.

I've seen a few notes from Microsoft that they are aware of at least some of these issues. Unfortunately, several on connect have been marked as "resolved, won't fix" for at least a couple of years now.

Don Halloran

SQL Server, SSIS, SSAS and maybe even SSRS rants and raves (and, on occasion, useful code or designs).


Posted by Anonymous on 12 December 2010

Pingback from  Dew Drop – December 12, 2010 | Alvin Ashcraft's Morning Dew

Posted by allmhuran on 13 December 2010

Oh, and one more thing about package configurations that really annoys me: Once you make your XML config indirect through an environment variable, you lose the ability to map properties to the config file via the treeview. The only solution I know of is to add another configuration, point it to the same file, say "reuse existing", add the things you want, and then remove the extra config. And you have to be careful doing this, because if you collapse and then expand a node in the tree (just using the -+, without ever going near a checkbox) the treeview automatically checks every property under the expanded node...

Posted by Jorge Segarra on 13 December 2010

You and your devs may want to check out the free webcast tomorrow from Tim Mitchell on Expressions in SSIS from the PASS App Dev Virtual Chapter http://appdev.sqlpass.org/

Posted by Anonymous on 13 December 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, SSIS: great idea, but buggy as heck. - Don Halloran         [sqlservercentral.com]        on Topsy.com

Posted by Andy Leonard on 13 December 2010

There are several steep slopes on the SSIS learning curve; SSIS Configurations are one of them.

SSIS Configurations are complex - especially if you want to march SSIS packages through your environments without issue, edit, or error. I cover one complex (yet functional) way to address this in my SSIS training courses. Email me from sqlblog.com/.../contact.aspx and I'll be happy to reply with one solution.


Posted by allmhuran on 13 December 2010

Thanks Andy, I think I saw you speak at the OttawaSQL group about 18 months ago, where you put forward a configuration architecture with an environment variable pointing to an XML config file, which then points to the SQL server database containing SQL based configurations (was that you?). I like this architecture, but IIRC the indirectness does still mean losing the ability to use the treeview in BIDS to set configured properties. It would be nice if BIDS would read the env variable and the XML config at design time to allow you to still do this. (On SQL 2005 at least, I haven't tried this in 08 yet).

It's this kind of problem that makes SSIS feel somewhat unfinished to me.

Posted by julie.smith 54188 on 16 December 2010

Hi Don, using indirect configurations is tricky to learn. There was a time where I was on my own at a company and simply didn't believe they worked until a former coworker helped me get through the learning curve.  I'm trying to understand your concern about losing the ability to use the tree view in BIDS with them.  Are you talking about the  Package Configuration Window where you select properties to configure?  There is a way to get your configurations set up to move a package from environment to environment and still see all your configurations.  Take Andy up on his offer to help :)--once you get through it your life will be easier.

Posted by allmhuran on 16 December 2010

Hi julie,

Yes, I did indeed contact Andy. His architecture is pretty close to something I've sketched out for the developers at my work, with a bit more smarts (my design uses the parent package more directly and has a different SQL configuration location for each environment, with not as much use of BLOCKED EXPRESSION

Regarding the treeview: yeah, I'm talking about the GUI with checkboxes next to each property. If you go down the route of a normal indirect configuration you can no longer edit the config file using the GUI, because AFAIK BIDS does not read the path environment variable at design time.

Posted by julie.smith 54188 on 23 December 2010

Hi Don,

Whoo time has flown, sorry I missed your response.  What my group has done is to use sql server configurations (in a table) for all of the configs but one (and then you can continue using the tree view to configure them).  The one left out of the sql server table is in the indirect config and what IS in the xml config file is the location of the table.  From there, it is a matter of setting up your database and table on each environment with the configurations that server would use.  Usually it is a one set up and done for the rest of the dev time.  Hope that makes sense and may be of use to you.  

Posted by Don Halloran on 1 January 2011

Yes, I also think SQL Server configurations are ultimately the way to go, it seems to offer the most flexibility, with an XML config pointing to the SQL Server, and then an environment variable to set the location of the XML config. That environment variable seems to be what causes the problem, I will try without in dev and perhaps only use that for UAT and prod, thanks.

Leave a Comment

Please register or log in to leave a comment.