SSIS - Editing DTSX-project with lots of connections = slow
A DTSX-package with lot of faulty connections can be very hard to edit due to SSIS trying to resolve all the connections.
First try to edit in offlinemode (Note: you cannot test the connections in offlinemode)
If that won’t work you can hand-edit the XML of the package. The way I did it was:
- Close the DTSX-package,
- Rename it to nnnn.dtsx.xml
- Open in visual studio. Select all XML-code and press Ctrl+K+F to format and prettify the code.
At the beginning of the XML-file you find all the connection managers. Usually you only need one or two of these. If all tasks just use one datasource you can exchange all connections to a single one.
1 <DTS:ConnectionManager>
2 <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
3 <DTS:Property DTS:Name="ObjectName">connDBStat</DTS:Property>
4 <DTS:Property DTS:Name="DTSID">{097BFD2F-AA7F-4C5D-BC91- 3A57D14CE709}</DTS:Property>
5 <DTS:Property DTS:Name="Description"></DTS:Property>
6 <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property>
7 <DTS:ObjectData>
8 <DTS:ConnectionManager>
9 <DTS:Property DTS:Name="Retain">0</DTS:Property>
10 <DTS:Password DTS:Name="Password" Sensitive="1"></DTS:Password>
11 <DTS:Property DTS:Name="ConnectionString">Data Source=SERVER_XYZ;User ID=sa;Initial Catalog=SomeDatabase;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;</DTS:Property>
12 </DTS:ConnectionManager>
13 </DTS:ObjectData>
14 </DTS:ConnectionManager>
Remember the DTSID for the connection you want to keep. For each component/task that uses a connection, change the GUID in connectionManagerID to the GUID of the connection you want to use.
1 <connections>
2 <connection id=”36″ name=”OleDbConnection”
3 description=”The OLE DB runtime connection used to access the database.” connectionManagerID=”{097BFD2F-AA7F-4C5D-BC91- 3A57D14CE709}“/>
4 </connections>
