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.mast er" AutoEventWireup="false" CodeFile="SSISExcel.aspx.v b" Inherits="SSISExcel" title="Untitled Page" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Sql Client" %>
<%@ Import Namespace="System.Configur ation" %>
<%@ Import Namespace="Microsoft.sqlse rver.dts.d tsclient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="Microsoft.sqlse rver.dts.r untime" %>
<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(strPa th)
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("ConnectionS tring", 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 .CommandBe havior.Def ault)
Dim ssisDs As Data.DataSet = New Data.DataSet
ssisDs.Load(ssisReader, Data.LoadOption.OverwriteC hanges, ssisReader.GetSchemaTable( ).TableNam e)
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.Fil eName
If GetFilePath.HasFile = True Then
Dim di As DirectoryInfo = New DirectoryInfo(GetFilePath. PostedFile .FileName)
FilePath = di.Root.ToString
' Response.Write("fileloadte st")
FileNameLength = Len(GetFilePath.FileName) 'cint(di.Root.tostring)
FileTest.Text = FilePath
''' Response.Write(Server.MapP ath(GetFil ePath.File Name))
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.Fil eName.ToSt ring
Response.Write(GetFilePath .PostedFil e.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>
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.mast
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Sql
<%@ Import Namespace="System.Configur
<%@ Import Namespace="Microsoft.sqlse
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="Microsoft.sqlse
<script runat="server">
Dim strPath As String = _
"C:\Documents and Settings\Administrator.EdZ
"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(strPa
End Sub
Private Function connectToSSISPackage(ByVal
'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("ConnectionS
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
Dim ssisDs As Data.DataSet = New Data.DataSet
ssisDs.Load(ssisReader, Data.LoadOption.OverwriteC
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.Fil
If GetFilePath.HasFile = True Then
Dim di As DirectoryInfo = New DirectoryInfo(GetFilePath.
FilePath = di.Root.ToString
' Response.Write("fileloadte
FileNameLength = Len(GetFilePath.FileName) 'cint(di.Root.tostring)
FileTest.Text = FilePath
''' Response.Write(Server.MapP
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.
lblFileType.Text = "Excel"
srcFiles.Insert()
srcFiles.Delete()
FileTest.Text = GetFilePath.PostedFile.Fil
Response.Write(GetFilePath
End If
End If
End Sub
Function CheckFileType(ByVal fileName As String) As Boolean
Return Path.GetExtension(fileName
End Function
</script>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.