?
Solved

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

Posted on 2010-11-13
1
Medium Priority
?
447 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>
0
Comment
Question by:EdZeppelin
1 Comment
 
LVL 5

Accepted Solution

by:
SavindraSingh earned 500 total points
ID: 34342354
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.

Regards,
Savindra
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This collection of functions covers all the normal rounding methods of just about any numeric value.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question