SSIS 2008 changes in Designer not in sync with XML code

Question

SSIS 2008 changes in Designer not in sync with XML code

glenn_thomas

SSC Enthusiast

Points: 127
More actions
April 13, 2010 at 3:05 am

#220385

I designed a data extraction package to extract data from 1 of 13 oracle sites to load into SQL Server DW.
After I got the package working I made 12 copies of it.
I then proceeded to change the connection to other oracle connections. I found the previous connection code was still in the xml and was giving me errors.
I got around this untile I found an answer by changing the errorlevel.
If I go and edit the xml code and delete the wrong code the package is corrupt.
How can I sync the designer with the xml code or try to edit the xml code???

Viewing 10 posts - 1 through 10 (of 10 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

How did you make the copies?

This also sounds like a maintenance nightmare for the future. Is there any reason why you don't do all the processing in the same package?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

There are substantially better ways to accomplish the goal. Can I ask how the packages are called? SQL Agent perhaps?

CEWII

glenn_thomas SSC Enthusiast Points: 127 More actions · Answer 3

THe initial package was saved to disk. I made copies of each, renamed them then added them into VS designer, and in designer I changed and/or created connections to other databases.

The packages are loaded into sql servers Integration Services. Within SSIS I change the package role to public. Then In SQL Server Agent create a job to run at a specific time.

I must have incorrectly changed the packsge connection in designer to cause this error.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

I am wondering whether the fact that you used file-level copy with the packages has confused SSIS because of the duplicate IDs all over the place.

You may like to try generating new IDs for all the packages (click the ID drop-down on package properties and 'Generate New ID'), in case this is an issue.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I am questioning the entire methodology. It sounds like the only difference is the connection string and the packages are being called from SQL Agent.

Why not use the options available there to modify the connection strings at run-time, then there is only ONE package and none of the Rube Goldberg stuff..

CEWII

glenn_thomas SSC Enthusiast Points: 127 More actions · Answer 6

WHen I execute the package there are code within the xml that should not exist any more because I've changed the designer, but they do and is causing the errors....this is the errors when I run the package in designer.

Information: 0x40043008 at Load Inventory to Staging table, SSIS.Pipeline: Post Execute phase is beginning.

Information: 0x4004300B at Load Inventory to Staging table, SSIS.Pipeline: "component "OLE DB Destination" (84)" wrote 21550 rows.

Information: 0x40043009 at Load Inventory to Staging table, SSIS.Pipeline: Cleanup phase is beginning.

Error: 0xC001000E at InventoryUpdateWGC: The connection "ADONET_KALAC_1" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error: 0xC001000E at InventoryUpdateWGC: The connection "OLEDB_INVENTORY_1" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Information: 0x4004300A at SalesTotal for last 3 Months to staging table, SSIS.Pipeline: Validation phase is beginning.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

What is InventoryUpdateWGC?

It seems that that component wants to use both ADONET_KALAC_1 and OLEDB_INVENTORY_1 but neither is found.

After you explain what that one component is we might make some headway. Also, why are you stuck on doing this processes this way there is a cleaner way to do it?

CEWII

glenn_thomas SSC Enthusiast Points: 127 More actions · Answer 8

Thats correct those connections don't existing because I changed the connection to another database server connection in this package to InventoryUpdate_WGC , but the xml code behind the gui designer still exists which is causing the errors.

If there is a better way to connect please let me know, thanks.

The package was converted from dts 2000 version to SSIS 2008, when I changed the package I was learning as I went (SSIS 2008) and may have change the connection the wrong way.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

All you are really doing is changing the connection string on each of the packages and the packages are executed from SQL Agent? If so, you can change the connection string right from the SQL Agent step without changing the package AT ALL.

CEWII