Insufficient data error in SSIS package

When multiple developers are working on a group of SSIS packages and one of them tries to run a package created by another user, they could receive an error saying there is insufficient data to connect to the data source.


After a little googling and a lot of trial and errors, I think I have narrowed down the issue and how to fix it.

As far as I can tell, the error has nothing to do with the driver or ODBC, it has to do with how SSIS packages handles sensitive data such as passwords.

In the package properties, there is a parameter called ProtectionLevel. By default this is set to EncryptSensitiveWithUserKey. That means that the login info on the data sources will be encrypted in the package file with a key generated from the user who was logged in when they were created. So later, when I log in and try to run it, the decryption returns the wrong value based on my user key and the connection bombs.

You can change this value to any of the other choices, however, you can't use DontSaveSensitive or ServerStorage if you are working with local package files that are not stored in SQL. Leaving the value alone will also work, you just need to add data sources to the project (especially ODBC) and make sure that all of the tasks and data flows reference those data sources.

Now the bad news. Since you are working with local files, Visual Studio does not expect a different user to access them while local. So it over-protects them to the point that they are almost un-usable.

In order for a different user to run the package, they need to

1. Open each data source
1. Edit the connection string
2. Enter the user and password data (it will be missing)
3. Test the connection
2. Open each task that accesses a connection
1. Choose the connection property
2. Choose new connection and map to the data source defined in the project
3. Parse the query to make sure the connection is good
4. Click OK
3. Open each data flow object
1. Click New and map to the existing data source
2. You will probably need to entirely rebuild each data flow object, so pay attention to the other properties before you change the data source.

The package should now run.

There is one saving grace. Once both users have followed this process, their individual data source will show up in the drop downs above (rather than having to use new).

Update:
However, I just couldn;t let this one go.  On a hunch I started exploring configuration files in SSIS.  Success!

This is by far the best solution to this problem.  It streamlines moving packages from one server to another, and almost totally eliminates the multi-developer issue above.

Basically, if you enable configuration files on a package, the package will override any properties listed in the config file with the values from the config file. 

This link gives a great alkthrough on how to use configuration packages with SSIS:
http://decipherinfosys.wordpress.com/2008/08/15/ssis-creating-package-configurations/

Follow those instructions, then just choose the connection object property for each object that has one, and choose the username and password for the connection manager connections.

Then add the config file to the project and open it.  Edit the password entries and type in the password and save.  Now when a another developers opens the package, they may get a couple of load warnings and errors related to the encryption.  Ignore them and load anyway.  Once you run the package, the errors will go away, since the object properties will get overwritten by the config properties.



I hope this keeps someone else from chasing their tail.




Did this help you?  If so, please leave a comment!