Hi all,
I am trying to write a program that creates packages on the fly depending on the variables you pass. for eg. It should create connection managers on the fly specific to a certain file in the variable (eg. sample.csv). the package has a dataflow task and it has flat file source and oledb destination.The problem I am facing is the flat file source when assigned to a flat file connection manager(dynamically), it is not giving any source output columns. i.e, the value for DFSource.OutputCollection(0).OutputColumnCollection.Count is Zero. But when I use the same code and reverse the source and destination(oledb as source and flatfile as destination), it is working fine. I searched everywhere for resources on how to develop packages programmatically, but could not find any except one example on msdn. If anyone knows about this prob or any useful resources on this subject, it would be really helpful.
Thanks,
Prithvi.
This is the code for creating connections and adding dataflow task. I need to add a script component transformation, but initially I wanted to see if a one-to-one mapping works fine.
Public Sub CreateConnections()
'Add the OLE DB and Flat File Connection Managers
Console.WriteLine("Creating the MyOLEDBConnection")
Dim cnOLEDB As ConnectionManager = myPackage.Connections.Add("OLEDB")
cnOLEDB.Name = "MyOLEDBConnection"
cnOLEDB.ConnectionString = "<connection string>"
Console.WriteLine("Creating the MyFlatFileConnection")
Dim cnFile As ConnectionManager = myPackage.Connections.Add("FLATFILE")
cnFile.Name = "MyFlatFileConnection"
cnFile.Properties("ConnectionString").SetValue(cnFile, "C:\sample.csv")
cnFile.Properties("Format").SetValue(cnFile, "Delimited")
cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, False)
cnFile.Properties("DataRowsToSkip").SetValue(cnFile, 0)
cnFile.Properties("RowDelimiter").SetValue(cnFile, vbCrLf)
cnFile.Properties("TextQualifier").SetValue(cnFile, """")
End Sub
Public Sub AddDataFlowTask()
'Add a Data Flow Task
Console.WriteLine("Adding a Data Flow Task")
Dim e As Executable = myPackage.Executables.Add("DTS.Pipeline")
Dim taskDF As TaskHost = CType(e, TaskHost)
taskDF.Name = "DataFlow"
Dim DTP As MainPipe
DTP = CType(taskDF.InnerObject, MainPipe)
' Add the FLAT FILE Source
Console.WriteLine("Adding the File Source")
Dim DFSource As IDTSComponentMetaData90
DFSource = DTP.ComponentMetaDataCollection.New()
DFSource.ComponentClassID = "DTSAdapter.FlatFileSource.1"
DFSource.Name = "FlatFileSource"
' Connect, populate the Input collections and disconnect
Dim SourceInst As CManagedComponentWrapper = DFSource.Instantiate()
SourceInst.ProvideComponentProperties()
DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyFlatFileConnection").ID
DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyFlatFileConnection"))
SourceInst.AcquireConnections(vbNull)
SourceInst.ReinitializeMetaData()
SourceInst.ReleaseConnections()
'checking If the external metadata columns are available as source output columns and the output is 0 which means no columns are being passed as output from source
Dim column As IDTSOutputColumn90
Try
Console.WriteLine("connection name: " & DFSource.RuntimeConnectionCollection(0).Name.ToString)
Console.WriteLine("output collection name: " & DFSource.OutputCollection(0).Name)
Console.WriteLine("output collection description :" & DFSource.OutputCollection(0).Description)
Console.WriteLine("source output columns count :" & DFSource.OutputCollection(0).OutputColumnCollection.Count.ToString
Catch ex As Exception
Console.WriteLine(ex.InnerException.Message.ToString)
End Try
'tried to print col names but it does not print any
For Each column In DFSource.OutputCollection(0).OutputColumnCollection
Console.WriteLine(column.Name.ToString)
Console.WriteLine(column.DataType.ToString)
Next
' Add the OLEDB Destination
Console.WriteLine("Adding OLEDB Destination")
Dim DFDestination As IDTSComponentMetaData90
DFDestination = DTP.ComponentMetaDataCollection.New()
DFDestination.ComponentClassID = "DTSAdapter.OLEDBDestination"
DFDestination.Name = "OLEDBDestination"
' Create an instance of the component
Dim DestInst As CManagedComponentWrapper = DFDestination.Instantiate()
DestInst.ProvideComponentProperties()
If DFDestination.RuntimeConnectionCollection.Count > 0 Then
DFDestination.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyOLEDBConnection").ID
DFDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyOLEDBConnection"))
End If
DestInst.SetComponentProperty("AccessMode", 0)
DestInst.SetComponentProperty("OpenRowset", "tempSSIS")
DestInst.SetComponentProperty("FastLoadKeepNulls", True)
' Map a connection between the source and destination
Dim path As IDTSPath90 = DTP.PathCollection.New()
path.AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFDestination.InputCollection(0))
Dim InColumns As IDTSVirtualInputColumnCollection90 = DFDestination.InputCollection(0).GetVirtualInput().VirtualInputColumnCollection()
' the number of input columns to destination is zero
Console.WriteLine("input columns : " & InColumns.Count.ToString)
Try
DestInst.AcquireConnections(vbNull)
Catch ex As Exception
Console.WriteLine(ex.InnerException.Message)
End Try
DestInst.ReinitializeMetaData()
'Console.WriteLine("input columns : " & DFDestination.InputCollection(0).InputColumnCollection.Count.ToString)
For Each input As IDTSInput90 In DFDestination.InputCollection
' Get the virtual input column collection for the input.
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
' Iterate through the virtual column collection.
For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' Call the SetUsageType method of the design time instance of the component.
Console.WriteLine(vColumn.Name.ToString)
Console.WriteLine(vColumn.DataType)
DestInst.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
Next
Dim exCol As IDTSExternalMetadataColumn90
For Each col As IDTSInputColumn90 In DFDestination.InputCollection(0).InputColumnCollection
exCol = DFDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)
DestInst.MapInputColumn(DFDestination.InputCollection(0).ID, col.ID, exCol.ID)
Next
DestInst.ReleaseConnections()
End Sub
Please see if i did wrong any where(which I always happen to do). But based on the msdn material, the above code should work, i guess.
