Changing the db name in DTS designer.

[manohars]

In DTS designer, I am using excel sheet object and MS SQL Server connection object to port data from excel sheet to MS SQL Server database. And, I have many Transform Data Task links between the same excel sheet and the target sql server connection. Every time, when I change the database in the target SQL Server connection, I am changing the DB name in destination tab in each Transform Data Task link to represent the table name of the new database. I tried to use Microsoft UDL to take the connection string from an .udl file. Still, db name doesn't change in destination tab of Transform Data Task link. I am not in a position to convert them to Custom Data tasks. Is there anyway to change the db name in connection object without changing the dbname/tablename in destination tab of Transform Data Task link.

Thanks in Advance,
Manohar

 

[EdwinGene]

You can change DTS Transform Data Task (a.k.a. Data Pump Task) properties on the fly using the DTS Dynamic Properties Task. You can get the value for the property from an ini file, SQL Query, DTS Global Variable, Environment Variable, Constant, or Data File.

 

[manohars]

It doesn't work. Since some other database name was previously set, in the destination tab of Transform Data Task link, the old db only is referred. When I executed the link by changing the values in dynamic properties sheet using ini file, it threw error.

Thanks,
Manohar

 

[yksvaan]

My suggestion:

You of course have to change the sql connection to point to right database with proper login information, you can do this manually or with DynamicProperties.

But also you must have the DestinationObjectName property (what you see as Table name: in Destination tab) of the Transform Data Task (aka DataPumpTask) in form that it only contains tablename, excluding the server.schema. name.

You can accomplish this two ways. First, you can edit the tablename(s) with Disconnected Edit feature, find your DataPumpTask, click it and 2-click DestinationObjectName property and type. If you do it like this, don't go to the task's destination tab from the designer to select table name again! But you can look at it and it will have only tablename.

Other way is to automate what I told in the previous paragraph, make a Global variable(or..), which holds only the tablename, and set that to DestinationObjectName using DynamicProperties Task. I've done this, so it is possible.

Just ask if there's something fuzzy about how I wrote this.

Cheers

 

[manohars]

Thanks a lot. Disconnected Edit works fine.

Manohar

 

[manohars]

Still I have one issue. If I have 2 MS SQL Connections, I am not able to give connection string thru dynamic properties. I have tried to use single dynamic properties task object for both connections and separate task objects for each connection. Second MS SQL Connection object always fail. Is there any workaround? This is urgent, pl.

Thanks,
Manohar

 

[yksvaan]

Why wouldn't you be able able to give connection string thru dynamic properties ?

Are you trying to change the DestinationConnectionID in the DataPump Tasks, you should't be. Just change DataSource property in then Connection objects.

Or maybe I didn't get it ?

Cheers

 

[manohars]

I am changing the destination connection only. I am porting data from excel sheet to sql server database. I had a requirement to use 2 connections. Connection string changes correctly for one connection, thru dynamic properties task but not for other one.

Manohar

 

[yksvaan]

The first Pump task's DestinationConnectionID (in Disconnected edit) should be, lets say 1, and for the other one 2. And there's no reason to touch these. And you have two connections objects, other one's ID property is 1 and the other one's 2. DataSource property contains sql-server name and UserID/Password/UseTrusted.. the logon information.

Tell me more.

Cheers