• Another way to do it, instead of leaving the data in an .ini file is to put it into the registry. You can then use regread functionality to pull the information from the registry, this way you can change the appropriate registry entry for each environment using a standard naming convention which will then change the connection string information on the fly depending on the server that the dts resides. The advantage is that this can also be done for text, excel, db files, in fact for pretty much anything.....

    Example code below


    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Private Const DTS_KEY = "HKEY_LOCAL_MACHINE\SOFTWARE\DTSVariables\"

    Function Main()

     Dim oConn, oWSH, oText

     Set oConn = DTSGlobalVariables.Parent.Connections("self")

     Set oWSH = CreateObject("WScript.Shell")

     oConn.DataSource = oWSH.RegRead(DTS_KEY & "SelfConnection")

     

     

     

     

    'TEXT CONNECTION 

     

     Set oText = DTSGlobalVariables.Parent.Connections("random text file")

     

     oText.DataSource = oWSH.RegRead(DTS_Key & "TextConnection") & ("allnewtest.txt")

     

    'BULK INSERT TASK

        'Get Package Object

        Set oPKG = DTSGlobalVariables.Parent

        ' Set new ProcessCommandLine value for "DTSTask_DTSBulkInsertTask_1"

        oPKG.Tasks("DTSTask_DTSBulkInsertTask_1").CustomTask.Properties._

          Item("DataFile").Value =oWSH.RegRead(DTS_KEY & "ServerName") & ("D$\testfiles\test.csv")

        ' Clear Up

        Set oPKG = Nothing

     

     

     Main = DTSTaskExecResult_Success

    End Function

     



    Shamless self promotion - read my blog http://sirsql.net