Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have several packages with hard coded connection managers which I would like to merge into one package. Each individual package is currently loading a single data file to a single table is a single database and I would like to make the process more streamlined and also do some transformations on the data which would be more useful in a single package than in the many disparate ones.

I created a package configuration for one of the packages and changed the connection manager in the XML Package Configuration and am now looking to add the other packages to this one.

I have tried copying the tasks and then their data flows. This results in errors like:

Error 1 Error loading Package1.dtsx: The connection "{450E96E4-3233-424A-BC4E-2F0A3EE1B2AA}" is not found. This error is thrown by Connections collection when the specific connection element is not found. C:\PathToCofigFile\ConfigFileName.dtsx 1 1

and

Error 6 Validation error. Create Master Table(s) Execute SQL Task: Connection manager "{DE947E19-E854-47F6-9B84-DDE1343AEDDB}" does not exist. Location.dtsx 0 0

Possibly, the best way is just to edit the XML of all the packages to have the same connection name first and then merge them. Just wondering if anyone has any experience with this and could recommend a better way.

UPDATE: I was able to resolve the errors by copying the connection managers from the source packages and then either editing the package XML or changing the connection in the GUI. Still seems there must be a better way...

share|improve this question
2  
How are your programming skills? There are different routes one can take but that would be my go to approach on this. Also, until you stated that you wanted to perform transformations on the data in the single package approach, I was going to advise against it. Small, tightly focused packages are easier to maintain and troublehsoot than everything+kitchen+sink models. –  billinkc Oct 10 '13 at 16:04
    
Good advice on the small focused packages and I would agree BUT the data files are dumps of individual tables in source systems and I will need to merge & transform some of the as they go into the alternate schema of the warehouse destination. –  Thronk Oct 10 '13 at 16:21
3  
Connection Managers have a Name which shows up in all the dialogs and a GUID as their true id. Renaming a connection manager within a single package doesn't cause any trouble because the GUID the assorted tasks are using doesn't change. When you move tasks between package, even if there is a connection manager with the same name in the destination package, the GUID is likely to be different (unless they copy/pasted). The designer can't find the referenced GUID in its CM collection and throws up the id as the name. Make sense? –  billinkc Oct 10 '13 at 16:29
    
+1 for answering why I got the errors and the way around them. –  Thronk Oct 10 '13 at 16:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.