To get the Physical path of SSIS package using ScriptTask

  • 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

  • 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.

     

Viewing 2 posts - 1 through 2 (of 2 total)

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