To get the Physical path of SSIS package using ScriptTask

• Balakrishnan K

  Ten Centuries

  Points: 1220

  More actions

  November 29, 2006 at 6:03 am

  #121680

  Hi All,

  I am in a situation to set the path of excel file connection manager in run time. The file will be always stored in the path where the SSIS package is deployed / created. The end user can put the package anywhere in the system and able to import the data from the excel file without making any changes to the excel file connection manager.

  Please help me out from this situation.

  Thanks In ADVANCE.

  Regards,

  Balakrishnan.K

• Jambu Krishnamurthy

  SSCrazy

  Points: 2054

  More actions

  November 30, 2006 at 8:41 am

  #674947

  Step 1

  ======

  Create an Environment variable

  Name:   PathToExcelFiles

  Value:  c:\whatever\

  Step 2

  ======

  Drop an Excel connection onto the DTS designer

  Give the path to the Excel File. This path could be anywhere. (d:\whatever\ for example)

  We will change this dynamically at runtime, to the path we want.

  Step 3

  ======

  Create an ActiveX Script task and here is the code

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

  '  Visual Basic ActiveX Script

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

  Option Explicit

  Function Main()

   'USING ENV VARIABLES

   '====================

   Dim wshShell, PathToExcelFiles

   Dim MyExcelConn

   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

  All this I tested in DTS 2000, but SSIS should definitely do the same, if not better.

   If you get stuck with some errors, pl bug me again.