Solved

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

Posted on 2010-11-13
1
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 5

Accepted Solution

by:
SavindraSingh earned 125 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - Use a Button to Select a specific Record on a form 12 97
Need more details 5 192
VS2015 Redefinition errors 4 101
sIMULTANEOUS USAGE ON NETWORK-ACCESS 2010-2013 7 75
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…

737 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