How do I pass path/file aka connectionstring as variable to Package in Integration Services 2005


How do I pass a variable to a Integration Services package at run time?  The string variable will be the path and filename of an excel file that is specified in an ASPX page/web form.  The package is straightforward: it consists of an Excel source that feeds a Data Reader Destination. (Later, I'll change the Data Reader that can append the Excel data to a SQL Server database table).
The function "connectToSSISPackage" does run and populate the GridView with the Excel file's data, I want to make the package more dynamic by having having Excel path/filename passed as a variable that'll change the Package connectionstring- just having trouble figuring out how.  

Any suggestions would be greatly appreciated I don't use IS all that much. Thanks


<%@ Page Language="VB" MasterPageFile="~/ALC.master" AutoEventWireup="false" CodeFile="SSISExcel.aspx.vb" Inherits="SSISExcel" title="Untitled Page" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<%@ Import Namespace="Microsoft.sqlserver.dts.dtsclient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="Microsoft.sqlserver.dts.runtime" %>

<script runat="server">

    Dim strPath As String = _
             "C:\Documents and Settings\Administrator.EdZ\My Documents\Visual Studio 2005\" & _
             "Projects\Integration Services Project1\Integration Services Project1\" & _
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
    Private Function connectToSSISPackage(ByVal path As String) As Integer
        'Dim DtsConnectionssisCN As New DtsConnection
        Dim ssisCN As DtsConnection = New DtsConnection 'ssisCN.ConnectionString = String.Format("-f ""{0}""", path)
        ssisCN.ConnectionString = String.Format("-f ""{0}""", path)
        ' ssisCN.ConnectionString = String.Format("ConnectionString", path)

        Dim ssisCmd As DtsCommand = New DtsCommand(ssisCN)
        ssisCmd.CommandText = "DataReaderDest" 'was: DataReaderDest | test: OLE DB Destination

        Dim ssisReader As Data.IDataReader = ssisCmd.ExecuteReader(Data.CommandBehavior.Default)

        Dim ssisDs As Data.DataSet = New Data.DataSet
        ssisDs.Load(ssisReader, Data.LoadOption.OverwriteChanges, ssisReader.GetSchemaTable().TableName)

        GridView1.DataSource = ssisDs

    End Function

    Function ReturnFilePath(ByVal path As String)  'chg connstr
        'Create a package and set two properties.
        Dim DTSConn As DtsConnection
        'DTSConn.m_pkg = strPath
        'Dim pkg As Package = New Package()
        'pkg.EnableConfigurations = True
        'Dim config As Configuration = pkg.Configurations.Add()
    End Function
    Protected Sub GetFilePath_OnLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles GetFilePath.Load
        Dim FilePath As String 'New IO.Path
        Dim FileNameLength As Integer
        'FileTest.Text = GetFilePath.PostedFile.FileName
        If GetFilePath.HasFile = True Then
            Dim di As DirectoryInfo = New DirectoryInfo(GetFilePath.PostedFile.FileName)
            FilePath = di.Root.ToString
            ' Response.Write("fileloadtest")
            FileNameLength = Len(GetFilePath.FileName)      'cint(di.Root.tostring)
            FileTest.Text = FilePath
            ''' Response.Write(Server.MapPath(GetFilePath.FileName))
        End If
    End Sub
    Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
        'Insert-Delete is work around for FileUpload not returning path info for IS package
        If GetFilePath.HasFile Then
            If CheckFileType(GetFilePath.FileName) Then
                lblFileType.Text = "Excel"
                FileTest.Text = GetFilePath.PostedFile.FileName.ToString
            End If
        End If
    End Sub
    Function CheckFileType(ByVal fileName As String) As Boolean
        Return Path.GetExtension(fileName).ToLower() = ".xls"
    End Function
Who is Participating?
SavindraSinghConnect With a Mentor Commented:
Sorry, I didn't go through entire code but

Instead of passing the Connection string as variable you can save the connection string in a Web.config file (if the that is a web application) or save it in App.config file if it is Windows application.

Once the page/program is compiled you can make changes to the .config files without making changes to the executable.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.