October 8, 2007 at 3:47 pm
Is there a way to dynamically update the column mappings of an OLE DB Destination Connection to the columns of an OLE DB Source Connection?
I am trying to copy data from a dynamic list of tables to a destination table on a different server. During this process I need to be able to dynamically define the destination and source tables so that the Schema ownership chain can be modified between various servers. Currently I have a package which will dynamically populate two variables from a SQL Script Task which are iterated through using a For Each Loop Container:
1. SourceTableName (includes dbo Schema)
2. DestinationTableName (includes custom Schema)
Each iteration of the For Each Loop sets the SourceTableName to a different table name as defined within my reference table. The same is done with the DestinationTableName variable value. The ForEachLoop contains a DataFlow task which is configured with a OLE DB Source Connection and an OLE DB Destination Connection. Both connections are set to use there respective variable values for the source and destination tables. I have tried to use the approach of extracting the contents of the table and running a seperate import process but without forcing explicit column mappings and having multiple data flow tasks which need to be maintained each time a new data table is added to my reference table I can't get this to work.
Any ideas on how to remove this column mapping issue?
Thanks, Scott Clark
October 9, 2007 at 1:01 pm
Nevermind. I have gone down the path of creating a parent package which programatically builds and executes a child package. This will allow me to build schema independent DataFlow based packages and store them for prosperity.
October 10, 2007 at 7:07 pm
Thought you all might like to see the solution I built to resolve this issue for myself. It is a composite of code based upon examples from various sources mostly Microsoft. A parent package is used to build a list of tables in a source database dynamically from a meta table stored in a utilities database. This list is then used to populate the variables used by the script via a ForEachLoop container. A new package is generated for each table processed so that they can be run individually if desired or as a whole. Following the execution of this script the child package which is assembled is then executed via an ExecutePackage task in the parent package. Simple workflow but it gets the job done. Here is the code from the script task housed within my ForEachLoop container:
' Package Variables Used:
' Read-Only:
' SourceDB
' SourceServer
' SourceTable
' DestinationServer
' DestinationDB
' DestinationTable
' ChildPackagePath - Storage location for dynamically generated packages.
'
' Read-Write:
' ChildPackageName - Name given to package when saved to file system.
'
' References Used(including defaults for Script Task):
' Microsoft.SqlServer.DTSPipelineWrap
' Microsoft.SqlServer.DTSRuntimeWrap
' Microsoft.SqlServer.ManagedDTS
' Microsoft.SqlServer.PipelineHost
' Microsoft.SqlServer.ScriptTask
' Microsoft.VisualBasic
' Microsoft.Vsa
' mscorlib
' System
' System.Data
' System.Windows.Form
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Public Class ScriptMain
Public Sub Main()
Try
' Create a package and add a Data Flow task.
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package()
package.Name = "From" & _
Replace(Replace(Replace(Dts.Variables("SourceTable").Value.ToString, ".", ""), "]", ""), "[", "") & "To" & _
Replace(Replace(Replace(Dts.Variables("DestinationTable").Value.ToString, ".", ""), "]", ""), "[", "")
package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive
Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
thMainPipe.Name = "DFTExtractData"
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Add an OLE DB connection manager to the package to manage the Source database connection.
Dim conMgrSource As ConnectionManager = package.Connections.Add("OLEDB")
conMgrSource.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=" & Dts.Variables("SourceServer").Value.ToString & ";Initial Catalog=" & Dts.Variables("SourceDB").Value.ToString & ";Integrated Security=SSPI;"
conMgrSource.Name = "OLESource"
conMgrSource.Description = "OLE DB connection to the " & Dts.Variables("SourceDB").Value.ToString & " database."
' Create and configure an OLE DB source component.
Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New
source.ComponentClassID = "DTSAdapter.OleDbSource.1"
' Create the design-time instance of the source.
Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
' The ProvideComponentProperties method creates a default output.
srcDesignTime.ProvideComponentProperties()
' Specify the connection manager.
If source.RuntimeConnectionCollection.Count > 0 Then
source.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("OLESource").ID()
source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("OLESource"))
End If
' Set the custom properties of the source.
srcDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("SourceTable").Value.ToString)
srcDesignTime.SetComponentProperty("AccessMode", 0)
' Connect to the OLESource data source,
' and then update the metadata for the source.
srcDesignTime.AcquireConnections(Nothing)
srcDesignTime.ReinitializeMetaData()
srcDesignTime.ReleaseConnections()
' Add an OLE DB connection manager to the package to manage the Destination database connection.
Dim conMgrDest As ConnectionManager = package.Connections.Add("OLEDB")
conMgrDest.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=" & Dts.Variables("DestinationServer").Value.ToString & ";Initial Catalog=" & Dts.Variables("DestinationDB").Value.ToString & ";Integrated Security=SSPI;"
conMgrDest.Name = "OLEDestination"
conMgrDest.Description = "OLE DB connection to the " & Dts.Variables("DestinationDB").Value.ToString & " database."
' Create and configure an OLE DB destination.
Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New
destination.ComponentClassID = "DTSAdapter.OleDbDestination.1"
' Create the design-time instance of the destination.
Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate
' The ProvideComponentProperties method creates a default input.
destDesignTime.ProvideComponentProperties()
' Specify the connection manager.
If destination.RuntimeConnectionCollection.Count > 0 Then
destination.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("OLEDestination").ID()
destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("OLEDestination"))
End If
MsgBox(Dts.Variables("DestinationTable").Value.ToString)
' Set the custom properties of the source.
destDesignTime.SetComponentProperty("AccessMode", 0)
destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("DestinationTable").Value.ToString)
'reinitialize the component
destDesignTime.AcquireConnections(Nothing)
destDesignTime.ReinitializeMetaData()
destDesignTime.ReleaseConnections()
'map the columns
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(source.OutputCollection(0), destination.InputCollection(0))
Dim input As IDTSInput90 = destination.InputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
Dim vColumn As IDTSVirtualInputColumn90
For Each vColumn In vInput.VirtualInputColumnCollection
Dim vCol As IDTSInputColumn90 = destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE)
destDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection(vColumn.Name).ID)
Next
Dim app As Application = New Application()
Dim strPath As System.String
strPath = Dts.Variables("ChildPackagePath").Value.ToString & package.Name.ToString & ".dtsx"
app.SaveToXml(strPath, package, Nothing)
'Set the value for the return variable to the name of the new package
Dts.Variables("ChildPackageName").Value = package.Name.ToString & ".dtsx"
'Send success result if no exceptions are raised
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
'Send failure result if exceptions are raised
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class
April 22, 2008 at 7:32 am
Just what I need. Thanks for posting the code.
April 22, 2008 at 7:56 am
Nicely done - might be worth sending it in as an article... there aren't enough walk throughs out there for this...
I know from my own website that this is a frequently requested topic...
Kindest Regards,
Frank Bazan
May 5, 2008 at 2:18 pm
I have been reviewing the code you provided, and was wondering how you then execute and delete the resultant package you have created? I am thinking that this may be useful for a project I am currently working on at the moment.
😎
May 6, 2008 at 10:35 pm
Lynn Pettis,
This script returns the full Package path as a variable which is then used as the package source within an Execute Package Task.
Dts.Variables("ChildPackageName").Value = package.Name.ToString & ".dtsx"
this is the line of code within the script which sets the variable value passed to the Execute Package Task within the Control Flow of the parent package.
Frank Bazan,
I agree walkthroughs for this nature of SSIS scripting are few and far between. If I get time I will submit an article as I agree there aren't enough walkthroughs out there for anything beyond the basics of SSIS package development with out of the box tasks.
pablo.mugica,
Glad this script helped you out.
November 17, 2008 at 11:53 pm
Hi ,
I have try the Code which you give.but I facing a problem.When there is no primary in the Source and dest Table.Then it working fine.But in case Table contains Primary key then package which is created dynamically is getting failed. Can you please suggest me in the Issue.....
kindly help me in this issue...
With Regrads,
Naveen T.
November 19, 2008 at 9:11 am
I would like to “second the motion” of putting an article together around this.
Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
November 19, 2008 at 12:25 pm
Naveen T.,
Post your error message and I will take a look at your issue with the script. This code was actually written against tables which contain unique primary key's.
Scott
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply