I have created a package that aims to achieve a high level of flexibility, allowing mappings from a flat file to a custom component to be determined at runtime. I have done so by doing the following:
Created a package which fetches column mappings from a database table. This table contains the "flat file source columns" and the "database table columns". This data is stored in a package variable.
I created a custom pipeline component (destination adapter) that processes the rows from the flat file and then populates a data table with the data from the flat file into the "database table columns". This data table is stored in a variable. (This custom component was not added to the package through the designer)
In order to achieve "dynamic mapping", I created a script task which ran after my initial SQL query which fetched the column mappings. The script task does the following:
- Creates a package
- Adds a Data Flow task
- Adds a flat file source (and connections) to the Data Flow Task
- Adds my custom component
- Maps the flat file source to my custom component
- Passes in my "Parent package variables" and executes the package
- Retrieves the "Data table variable" from the newly created package
I then run another script task which runs insert statements for each record in the data table variable.
The issue that I am having is that my package will not execute unless I add a flat file destination to the package that I created programatically.
I discovered this by saving the package that I created programatically and then added the flat file destination through the designer. I then opened a script task, loaded this package and executed it and it ran.
Below is the code for the creating the child package programatically:
Try
'CREATE PACKAGE
package = New Microsoft.SqlServer.Dts.Runtime.Package()
'-----------------------
'CREATE DATA FLOW TASK
'-----------------------
Dim PipelineTask As Executable = package.Executables.Add("STOCK:PipelineTask")
Dim DataFlowTask_Runtime As Dts.Runtime.TaskHost = CType(PipelineTask, Dts.Runtime.TaskHost)
DataFlowTask_Runtime.Name = "MyCustom Data Flow Task"
Dim DataFlowTask_DesignTime As MainPipe = CType(DataFlowTask_Runtime.InnerObject, MainPipe)
'------------------
'SETUP CONNECTIONS
'------------------
'CREATE FLAT FILE CONNECTION
Dim FFConnectionManager As ConnectionManager = package.Connections.Add("FLATFILE")
FFConnectionManager.ConnectionString = FileName
FFConnectionManager.Name = "Flat File Source Connection"
'SETUP FLAT FILE COLUMNS AND DELIMETERS
GetColumnsFromFlatFile(FFConnectionManager, DelimeterType.CarriageReturnLineFeed, DelimeterType.LineFeed, DelimeterType.Tab, 0)
'-----------------------
'CREATE SOURCE COMPONENT
'-----------------------
' Set up the souce component of the Dataflow task
Dim FFSourceComponent As IDTSComponentMetaData100 = DataFlowTask_DesignTime.ComponentMetaDataCollection.New
FFSourceComponent.Name = "Flat File Source"
' The managed source is an instantiation of the source.
FFSourceComponent.ComponentClassID = "{D23FD76B-F51D-420F-BBCB-19CBF6AC1AB4}"
Dim FFSourceComponent_DesignTime As Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapper = FFSourceComponent.Instantiate
FFSourceComponent_DesignTime.ProvideComponentProperties()
' Link the connection to the source component.
If FFSourceComponent.RuntimeConnectionCollection.Count > 0 Then
FFSourceComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(FFConnectionManager)
FFSourceComponent.RuntimeConnectionCollection(0).ConnectionManagerID = FFConnectionManager.ID
End If
FFSourceComponent_DesignTime.AcquireConnections(Nothing)
FFSourceComponent_DesignTime.ReinitializeMetaData()
'------------------------------------------
'Create Output Mappings from the flat file.
'------------------------------------------
Dim OutputColumnCollection As IDTSOutputColumnCollection100
OutputColumnCollection = FFSourceComponent.OutputCollection(0).OutputColumnCollection
Dim exOutColumn As IDTSExternalMetadataColumn100
For Each OutputColumn As IDTSOutputColumn100 In OutputColumnCollection
exOutColumn = FFSourceComponent.OutputCollection(0).ExternalMetadataColumnCollection(OutputColumn.Name)
FFSourceComponent_DesignTime.MapOutputColumn(FFSourceComponent.OutputCollection(0).ID, OutputColumn.ID, exOutColumn.ID, True)
'Add the output column name and ID to an array so that we can identify them at a later stage
outputColumnLineageIDs.Add(OutputColumn.Name, OutputColumn.ID)
Next
FFSourceComponent_DesignTime.ReleaseConnections()
'----------------------------
'CREATE MYCUSTOM SCRIPT COMPONENT
'----------------------------
Dim MyCustomScriptComponent As IDTSComponentMetaData100 = DataFlowTask_DesignTime.ComponentMetaDataCollection.New
MyCustomScriptComponent.ComponentClassID = "MyCustomBuffer.CustomComponents.MyCustomScriptComponent, MyCustomBuffer, Version=1.0.0.0, Culture=neutral, PublicKeyToken=516f7fc6acf0e6e0"
MyCustomScriptComponent.Name = "MyCustom Script"
Dim MyCustomScriptComponent_DesignTime As CManagedComponentWrapper = MyCustomScriptComponent.Instantiate
MyCustomScriptComponent_DesignTime.ProvideComponentProperties()
' Create the path from source to destination.
Dim path As IDTSPath100 = DataFlowTask_DesignTime.PathCollection.New
path.AttachPathAndPropagateNotifications(FFSourceComponent.OutputCollection(0), MyCustomScriptComponent.InputCollection(0))
' Get the destination's default input and virtual input.
Dim InputMyCustom As IDTSInput100 = MyCustomScriptComponent.InputCollection(0)
Dim vInputMyCustom As IDTSVirtualInput100 = InputMyCustom.GetVirtualInput
MyCustomScriptComponent_DesignTime.ReinitializeMetaData()
' Iterate through the virtual input column collection.
For Each vColumn As IDTSVirtualInputColumn100 In vInputMyCustom.VirtualInputColumnCollection
' Call the SetUsageType method of the destination
' to add each available virtual input column as an input column.
Dim InputCol As IDTSInputColumn100 = MyCustomScriptComponent_DesignTime.SetUsageType(InputMyCustom.ID, vInputMyCustom, vColumn.LineageID, DTSUsageType.UT_READONLY)
Next
'----------------------------
'CREATE FLAT FILE DESTINATION
'----------------------------
Dim DestFFConnManager As ConnectionManager = package.Connections.Add("FLATFILE")
DestFFConnManager.ConnectionString = "C:\Test\MyCustomBufferTest.txt"
DestFFConnManager.Name = "Flat File Destination Connection"
Dim DestFFConnManager_DesignTime As IDTSConnectionManagerFlatFile100 = DestFFConnManager.InnerObject
DestFFConnManager_DesignTime.ColumnNamesInFirstDataRow = True
DestFFConnManager_DesignTime.CodePage = 1252
DestFFConnManager_DesignTime.HeaderRowDelimiter = vbTab
DestFFConnManager_DesignTime.Format = "Delimited"
DestFFConnManager_DesignTime.HeaderRowsToSkip = 0
'FlatFileConnection.TextQualifier = ""
DestFFConnManager_DesignTime.RowDelimiter = vbTab
'CREATE FLAT FILE DESTINATION
Dim FFDestination As IDTSComponentMetaData100 = DataFlowTask_DesignTime.ComponentMetaDataCollection.New
FFDestination.ComponentClassID = "{8DA75FED-1B7C-407D-B2AD-2B24209CCCA4}"
FFDestination.Name = "Flat File Destination"
Dim FFDestination_DesignTime As CManagedComponentWrapper = FFDestination.Instantiate
FFDestination_DesignTime.ProvideComponentProperties()
' Link the connection to the source component.
If FFDestination.RuntimeConnectionCollection.Count > 0 Then
FFDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(DestFFConnManager)
FFDestination.RuntimeConnectionCollection(0).ConnectionManagerID = DestFFConnManager.ID
End If
Dim Destpath As IDTSPath100 = DataFlowTask_DesignTime.PathCollection.[New]
Destpath.AttachPathAndPropagateNotifications(MyCustomScriptComponent.OutputCollection(0), FFDestination.InputCollection(0))
Dim DestInput As IDTSInput100 = FFDestination.InputCollection(0)
Dim VDestinput As IDTSVirtualInput100 = DestInput.GetVirtualInput()
Dim VDestInputColCollection As IDTSVirtualInputColumnCollection100 = VDestinput.VirtualInputColumnCollection
Dim indexMax As Integer = VDestInputColCollection.Count - 1
For index As Integer = 0 To indexMax
' Get input column to replicate in flat file
Dim virtualInputColumn As IDTSVirtualInputColumn100 = VDestInputColCollection(index)
' Add column to Flat File connection manager
Dim flatFileColumn As IDTSConnectionManagerFlatFileColumn100 = TryCast(DestFFConnManager_DesignTime.Columns.Add(), IDTSConnectionManagerFlatFileColumn100)
flatFileColumn.ColumnType = "Delimited"
flatFileColumn.ColumnWidth = virtualInputColumn.Length
flatFileColumn.DataPrecision = virtualInputColumn.Precision
flatFileColumn.DataScale = virtualInputColumn.Scale
flatFileColumn.DataType = virtualInputColumn.DataType
Dim columnName As IDTSName100 = TryCast(flatFileColumn, IDTSName100)
columnName.Name = virtualInputColumn.Name
If index < indexMax Then
flatFileColumn.ColumnDelimiter = vbTab
Else
flatFileColumn.ColumnDelimiter = vbCrLf
End If
Next
FFDestination_DesignTime.AcquireConnections(Nothing)
FFDestination_DesignTime.ReinitializeMetaData()
For Each virtualInputColumn As IDTSVirtualInputColumn100 In VDestInputColCollection
' Select column, and retain new input column
Dim inputColumn As IDTSInputColumn100 = FFDestination_DesignTime.SetUsageType(DestInput.ID, VDestinput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY)
' Find external column by name
Dim externalColumn As IDTSExternalMetadataColumn100 = DestInput.ExternalMetadataColumnCollection(inputColumn.Name)
' Map input column to external column
FFDestination_DesignTime.MapInputColumn(DestInput.ID, inputColumn.ID, externalColumn.ID)
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
Adding the destination flat file component has allowed my package to execute, however it does not make sense as my custom component type is a destination adapter. The additional destination flat file component is inefficient and I would like to know what the need for this is.
