Programmatically developing an entire package..

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.