I create a SSIS Package for ETL on my own machine. During development database was also on my machine. For access to this database an OLE DB connection was defined within a package in BI Development Studio. Everything worked well both in debug mode and testing package itself.
Finally I need to load data to a database on a different machine using this package.
I used several scenaries:
1) simply copied the package-file to estination machine, open it for execution, in section "Connection Managers" I edited connection string manually - changed server name and Initial Catalog. And try to execute.
2) on the destination machine I manually created an OLE DB connection (using Microsoft Data Link) to a different database (test succeded), Changed the extention of the connection file 'udl' for ' txt ' and copied its connection string to the field connection string in section "Connection Managers" (pointed in variant 1) ).
3) use Package Configurations, copied the deployment to destination machine, installed the package the way like written here - http://msdn2.microsoft.com/en-us/library/ms365338.aspx. Changed exported properties - Server name, Initial Catalog and also the whole Connection String. Also try to execute.
In all cases I recieved the same error execution message :
"Errors in the metadata manager. Either the database with ID of " OLD_DATABASE_NAME " does not exist in the server with ID of " NEW_SERVER_NAME " or the user does not have permissions to access the object."
As for access (username/pass) settings they are the same for both of them, I have the same administrative rights on both machines. And more with the same rights the ole db connection made was made manually in variant 2 - succeded!!! So I don't think the problem is here.
As for Error message - I think somewhere the OLD name of database (Initial Catalog) is saved, though I tried to change it. Though the NEW value for the server name is substituted.
Please, help me. I don't know what else can I try. And it is not a single case for my practice. So I think - something wrong in my actions.
Don't store the ConnectionString property AND all of the other properties as configurations. Store EITHER the ConnectionString property OR all of the other properties (my advice would be to store only the ConnectionString property)
-Jamie
|||
Jamie Thomson - why to store only the ConnectionString?
Then config-file will overwrite only Connection String value itself, written in the package. But other properties would not be changed, but they will also been included into the package (the content of the package is independent with what properties I decided to include into configurations. Am I right? ).
Will you explain me? Thanks.
|||The reply, posted before Jamie Thomson's one, was very helpfull to me. I marked it as it was helpful, but suddenly your post disapeared ? Sorry, I am new to this forum, but anywhere you help me.
I tried to watch the package file in text editor, and find the property to which the old database name value was saved, then include this property to configurations to make it changable at run time.
Thanks, it works!!!!
|||Was this the post that disappeared?
|||sachin.rao wrote:
Hi Iris
The only place it could be persisting this information is in the package itself. Is the catalog name being stored in the dtsx file? Open it up in your favourite text editor and check it out.
Sachin
Rafael Salas - yes, it is! Thanks.
Please, tell me what I did wrong and how can I mark a post if it was helpful and/or was a reply/solution to my question.
|||For some reason that post was deleted...only the author or a moderator can do that...I don't know who did it. You should have a buton 'Mark as anserwer'...|||Jamie Thomson - why to store only the ConnectionString?
Then config-file will overwrite only Connection String value itself, written in the package. But other properties would not be changed, but they will also been included into the package (the content of the package is independent with what properties I decided to include into configurations. Am I right? ).
Will you explain me? Thanks.
Because the ConnectionString is made up of allthose individual properties (e.g. CatalogName, Username, password, ServerName etc...). Changing ConnectionString automatically changes allthe other properties. With ConnectionString you only need to maintain one thing however and I see that as an adavantage.
-Jamie
