Data Source Name = Global variable

• W R

  Mr or Mrs. 500

  Points: 523

  More actions

  November 30, 2006 at 8:11 am

  #76622

  I am trying to set a Data Source name of a connection (Excel 2000, in this case) dropdown to be a global variable instead of string. There is no drop down. I do not want to write a ActiveX script to set connect properties. Is this possible.

   

  Thank you.

  -W

• W R

  Mr or Mrs. 500

  Points: 523

  More actions

  November 30, 2006 at 9:57 am

  #674990

  I used DTS Dynamic Properties to fix this. But, I get an error "MAPI returned an error" when I try to add DTS Mail Task.

  Any suggestions?

  -W

• Jambu Krishnamurthy

  SSCrazy

  Points: 2054

  More actions

  December 1, 2006 at 7:21 am

  #675123

  From whatever I have tried/looked into/researched, atleast in DTS 2000, ActiveX Script alone seems to solve this problem. I use an environment variable because, I do not want to change anything in the package when I am moving from dev to prod. If you wish here is what I do:

  I would be happy if there is a way to get an env variable into a DTS global variable, without any code.

  Dynamically setting Source File path for "Excel File (Source)" connections in DTS 2000

  Setup an environment variable

  Variable name: PathToSourceTextFiles

  Variable value: c:\whatever

   

   'USING ENV VARIABLES

   =================

  function Main()

   Dim MyExcelConn, PathToExcelFiles, wshShell

   Set wshShell = CreateObject("Wscript.Shell")

   PathToExcelFiles = wshShell.ExpandEnvironmentStrings("%PathToExcelFiles%")

   Set MyExcelConn = DTSGlobalVariables.Parent.Connections("MyExcelConn")

   MyExcelConn.DataSource = PathToExcelFiles & "MyExcelFile1.xls"

   Main = DTSTaskExecResult_Success

  End Function