Avatar of EdZeppelin
EdZeppelin asked on

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

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>
Microsoft DevelopmentMicrosoft Server AppsMicrosoft Applications

Avatar of undefined
Last Comment
SavindraSingh

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
SavindraSingh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23