Tuesday, January 4, 2011

SQL Server SSIS: The connection "{SSIS Object ID, crazy long string}” is not found.

I would “hanker” a guess you copied a connection into a new SSIS Package, changed the name and successfully ran the package from Visual Studio. Yet, deploying it to the server produced the above error and when you search for the ID of the object the error message specifies, you can’t find it. Am I right? Even with a visual search through the IDs, you are unable to find the pesky ID stated in the error in your package, correct?

If so, try searching the entire solution for the ID – you should find the original object you copied and the search will also give you a hint of what object it was copied to that you need to reproduce.

In the package, do a Edit -> Find and Replace -> Entire Solution and paste in the annoying and mysterious ID in the error message received from the SQL Server Agent job that fails. This search should reveal at least two, most likely four lines where this ID shows up. Select which object is in the .dtsx package which is failing. If you scroll to the right in the Find Results screen, you should find the user friendly name of the object. It will be something like:

</DTS:Property DTS:Name=”ObjectName”>Some Object Name</DTS:Property>

You can also view the package contents by finding it with explorer and opening it with Notepad to reveal the XML. In this view search for the string of your object id not being found, and this will show you your friendly name for the object.

Now you have the user friendly name of the object. You should RE-create (from scratch – do not copy/paste) the connection. Use this new connection in the task(s) necessary, then delete the old connection, rebuild and deploy. Test again, if possible, from SQL Server Agent.

From my research it seems that Visual Studio has issues sometimes with copying connections from one package to another. Sometimes it keeps the old ID in the manifest or logging, and sometimes it even erases the ID and coughs up blood.

Hopefully, helpfully yours… Lara

No comments: