SQL Server Performance Forum – Threads Archive
parameterizing DTS packages
We have separate development, QA and prod environments. Let’s say we are developing a simple dts package to copy a table from Oracle to SQL Server. During development, we code the dts package to access a development Oracle database and development SQL Server instance. When we are ready for QA, we have to edit the dts package to now point to a QA Oracle database and QA SQL Server instance. Finally, when we are ready for production, we have to change the dts package now to access the production Oracle database and production SQL Server instance. I don’t like having to make changes to the dts package during each stage of development (dev, QA and prod). It is troublesome and prone to error. Is there any way we can parameterized a dts package so that we can feed in the Oracle database, username, password and SQL Server instance? This way the core of the dts package does not have to be changed for each phase of development. We are saving the dts package in SSF format. Thank You,Jethro.
jethrojaw,
I had the same issue some time back.
I did following things.
1.Save the DTS package to VB script (.bas)
2.Change the .bas fiel so that it can take the parameters
3.Run the .bas prg from a VB prg
This works fine. But I had some issues when changes needed to be done.
quote:Originally posted by jethrojaw
We have separate development, QA and prod environments. Let’s say we are developing a simple dts package to copy a table from Oracle to SQL Server. During development, we code the dts package to access a development Oracle database and development SQL Server instance. When we are ready for QA, we have to edit the dts package to now point to a QA Oracle database and QA SQL Server instance. Finally, when we are ready for production, we have to change the dts package now to access the production Oracle database and production SQL Server instance.
I don’t like having to make changes to the dts package during each stage of development (dev, QA and prod). It is troublesome and prone to error.
Is there any way we can parameterized a dts package so that we can feed in the Oracle database, username, password and SQL Server instance?
This way the core of the dts package does not have to be changed for each phase of development. We are saving the dts package in SSF format.
Thank You,
Jethro.
Jethro.
Our dts development standard is to have one graphical representation of each connection at the beginning of package (top left corner). Then we design tasks bellow always using existing connection. When we need to repoint connections to different environment we simply update connection editing their top left corner graphical representation. I hope explanation is understandable.
Use the UDL file to do this. You do not have to change the connection string. Its just you have to have the same copy of UDL file on production/acceptance server Sanjeev
Hi, Another way is to use Dynamic Properties Task. You can find numerous articles on this. Its easy to do also. One can define the values in ini file/through query/xml file and assign them to the DTS during run time. Visit this link for more info
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=21587&DisplayTab=Article
]]>