Changing "Connection String" in SSIS package ?

Hi!

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'...|||

IrisFresco wrote:

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

Change request: Parameter mapping

In some places in the SSIS enviroment, you can use parameters in queries, for examples in the query part of the OLE DB Command component.

You do this by specifying placeholders using question marks. The question marks result in parameters on the Column Mappings tab of the Advanced Editor named Param_0, Param_1, etc. When adding question marks to the query later on, new parameters are created named Param_<x>, where ‘x’ is the next higher index number, no matter where the question mark appears in the query. If, for example, your initial query contains two parameters and later on you add a third one that appears as the first parameter in the query, it results in a new Param_3 on the Column Mappings tab.

This is all a bit confusing and not very wanted from a maintenance point of view. I know this might be a limitation caused by using OLE DB connections; we tried to do something with ADO.NET connections, but until now, not very succesful. Therefore we think this is something to put on a wish list for a next SSIS version. It would be great to have the opportunity to give parameters a meaningful name, in stead of the question marks in the queries and the generated Param_<x> names in other places.
Hans Geurtsen
MoreInfo BV

I couldn't agree more. I recently had to do the same thing for a pipeline with >50 columns in it. Debugging it when it didn't work was near impossible.

You should raise this request at http://lab.msdn.microsoft.com/productfeedback/default.aspx (and let us know because I will vote for it) and also on the "SSIS Requests" thread at the top of this forum.

-Jamie