How to change connection property at runtime 1

[sqldevrequiretektips]

I have Designed DTS Package on My Local SQL Server.
Job & Flow :..

On DTS Design screen I have only three things

1) I have Microsoft OLEDB Provider which connect to my local SQL server

2) Execute SQL Task : Which contain 5 store procedures (this store procedures update/delete some tables)

3) Email Task : When everything Successfully completed then it send an email to me.

Reason to Develop DTS Package : Every month we have to update some tables so that's why I decide to create DTS Package.

My Question :

When Production server's (DBA) copy my package to production server, Is there anyway we can change connection property automatically to production server.

As you know rigth now Connection property has been setup to my local SQL server machine.

My question in Another words, When DBA copy my package to production server, DBA don't have to change anything and DTS Package should ready for run. Is it possible?

F.Y.I : All Necessary tables and Store procedures are allredy on Production server(Tables,SP which i am using in my package)

Any advise would really appriciate..

Thx
AD

 

[EdwinGene]

You could put an .ini file on the same place on each server's hard drive (so the DTS package would be able to find it) which contains the server name, then use the Dynamic Properties task to read the server name from the .ini file and update the DTS OLE DB Connection's data source property.

 

[sqldevrequiretektips]

That's pretty interesting...

So you mean i have to create .ini file which contain server name and copy to production server..

please focus me in detail..

Thx..
AD

 

[EdwinGene]

amardesai13,

In the DTS package, use a Dynamic Properties Task as your starting point. Open the properties of the Dynamic Properties Task; click the "Add..." button; select the connection from the left-hand pane of the window; and then double click "Data Source", or single-click "Data Source" and then click the "Set..." button.

From the "Source:" dropdown of the "Add/Edit Assignment" window, select "INI File".

I can't give you details on how to create the .ini file, as I have never created one myself. Perhaps someone else viewing this forum can help you out?

 

[EdwinGene]

amardesai,

I did a little test yesterday. The ini file idea works.

A sample format of the ini file is:

[Startup]
ServerName=MySqlServer

In the "Add/Edit Assignment" window "Section" would be "Startup", and "Key" would be "ServerName" (without the double-quotes, of course.