We help IT Professionals succeed at work.
Get Started

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

495 Views
Last Modified: 2012-05-10
Greetings,

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\" & _
             "SSISExcel.dtsx"
   
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        connectToSSISPackage(strPath)

    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)
                ssisCN.Open()

        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
        GridView1.DataBind()

        ssisCN.Close()
    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
        'pkg.
        '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"
                srcFiles.Insert()
                srcFiles.Delete()
                FileTest.Text = GetFilePath.PostedFile.FileName.ToString
                Response.Write(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
   
</script>
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE