How do I clean old data connections out of an SSIS package?

Question

How do I clean old data connections out of an SSIS package?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Well, this is what I got when I tried that:
Error1Error loading 'Weekly_FullSnapshots - Forecasts Live - xml edit.dtsx' : The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).G:\Mhub_Operations\DATA\SSIS\Archive_Snapshots\Weekly_FullSnapshots - Forecasts Live - xml edit.dtsx11

Forgot one thing that must be done before deleting the <DTS:ConnectionManager>. Each has a <DTS:Property DTS:Name="DTSID"> tag which has a GUID value. These appear where the connection is referenced in the package file and need to be removed.

In a control flow -> Connection="{478ca604-9df0-4bd2-983e-19ed1c6b77ed}"

in a data flow -> connectionManagerID="{55438835-4d17-4afe-8525-a1d58a94f164}"

Another way would be deleting only those connecctions which DTSID cannot be found elsewhere in the file.

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 2

Eirikur Eiriksson (3/27/2014)
Well, this is what I got when I tried that:
Error1Error loading 'Weekly_FullSnapshots - Forecasts Live - xml edit.dtsx' : The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).G:\Mhub_Operations\DATA\SSIS\Archive_Snapshots\Weekly_FullSnapshots - Forecasts Live - xml edit.dtsx11
Forgot one thing that must be done before deleting the <DTS:ConnectionManager>. Each has a <DTS:Property DTS:Name="DTSID"> tag which has a GUID value. These appear where the connection is referenced in the package file and need to be removed.
In a control flow -> Connection="{478ca604-9df0-4bd2-983e-19ed1c6b77ed}"
in a data flow -> connectionManagerID="{55438835-4d17-4afe-8525-a1d58a94f164}"
Another way would be deleting only those connecctions which DTSID cannot be found elsewhere in the file.

Excellente! This is working. Thanks.

forrest.pugh SSC Journeyman Points: 75 More actions · Answer 3

Someone more specifically trained and experienced than me is welcome to correct me, however I have found the following solution is quite simple and has never broken me:

1. Copy the GUID {WITH curly braces}

2. Alt+V, C - opens the XML text

3. Ctrl+H

3a. Scope your "Look In" to "ENTIRE SOLUTION"

3b. Paste your GUID to the "Find" box

3c. Make sure your "Replace" box is empty

4. Replace ALL

This will force SSIS to re-identify all connection managers using that stale old GUID with a fresh new GUID!