script task and configuring to different environment

Question

Post reply

script task and configuring to different environment

keywestfl9

SSCertifiable

Points: 5215
More actions
November 25, 2009 at 8:21 am

#214253

i have written a package in script task using vb .net and it running fine
i have done the connection string hardcoded in the vb .net
DBA are telling they can't move different package to different environment, i should control with the configuration file the different environment like dev,uat ,prod and contingency.
please advise how to go about it as i have done this before.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 1

[font="Comic Sans MS"]Hello - here you go :

http://www.lmgtfy.com/?q=SSIS+Easy+Package+Configuration

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 2

Thanks that link takes me to google.

i have done configuration using xml file for data flow task and other controls.

i want to know how to do it inside script task that is where i needed help

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The likely reason the DBA does not want to move it is due to hard-coded connection managers to the dev environment. Due to that the DBA is recommending the config file. Alternatives to the config file would be to use a database table to control the connection. This kind of setup requires additional planning in order to avoid the need to recompile the package prior to deployment to prod.

Another alternative is to pass a variable into the package at job run time that overrides the connstring being used.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 4

[font="Comic Sans MS"]

Hello - if you have used configuration through xml already - I assume you know how to put the database details in the xml configuration file.

Assume your database connection name is : ABC_DB_Connection (which is mapped appropriately to configuration file)

Now:

Step 1: Go to the connection manager tab of script component/transformation editor.

Step 2 :Put the connection manager [ABC_DB_Connection] and name [ say ABCDb] there.

Step 3: Inside script use System.Data.OleDb

Step 4 : Use the below code ..

'Define and open the database connection

.Connection = New OleDbConnection(Connections.ABCDb.ConnectionString)

.Connection.Open()

Try

'Execute the procedure or command here

Finally 'Always finalize expensive objects

.Connection.Close()

.Connection.Dispose()

End Try

And you are ready to go ...

Let me know if you need anymore help ..

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 5

thanks for the information

Can you please elaborate how to pass a variable and overwrite at run time?

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 6

keywestfl9 (11/25/2009)
thanks for the information
Can you please elaborate how to pass a variable and overwrite at run time?

[font="Comic Sans MS"]

Please refer to BOL for this - I think you would need a better understanding on script component. BOL has many excellent example of script task/transform.

For a quicky you may refer to Jamie's blog (it's a lifesaver for me):

http://consultingblogs.emc.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx

[/font]

[font="Comic Sans MS"]--
Sabya[/font]