Hi Padmaja, Thank you for posting the code.

But, I still have a problem while loading the header array. Below is my code

Public Class ScriptMain

Sub Main()

' Create a package and add a Data Flow task.

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package()

package.PackageType = DTSPackageType.DTSDesigner90

package.Name = "MySSISPackage"

'Add Data Flow task

Dim taskDF As TaskHost = TryCast(package.Executables.Add("DTS.Pipeline.1"), TaskHost)

taskDF.Name = "DataFlow"

Dim DTP As MainPipe

DTP = TryCast(taskDF.InnerObject, MainPipe)

' Creating OLEDB Connection Manager

Dim oleDbConn As ConnectionManager = package.Connections.Add("OLEDB")

oleDbConn.Name = "MyOLEDBConnection"

oleDbConn.ConnectionString = "Provider=SQLOLEDB.1;" & _

"Data Source=" & Dts.Variables("DestServer").Value.ToString & ";Initial Catalog=" & Dts.Variables("DestDB").Value.ToString & ";Integrated Security=SSPI;"

MsgBox(Dts.Variables("DestServer").Value.ToString)

MsgBox(Dts.Variables("DestDB").Value.ToString)

'Creating FlatFile Connection Manager

Dim cnFile As ConnectionManager = package.Connections.Add("FLATFILE")

cnFile.Properties("Name").SetValue(cnFile, "MyFlatFileConnection")

cnFile.Properties("ConnectionString").SetValue(cnFile, Dts.Variables("SourceFile").Value.ToString)

cnFile.Properties("Format").SetValue(cnFile, "Delimited")

cnFile.Properties("Unicode").SetValue(cnFile, False)

cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, True)

cnFile.Properties("DataRowsToSkip").SetValue(cnFile, 0)

cnFile.Properties("RowDelimiter").SetValue(cnFile, vbCrLf)

cnFile.Properties("TextQualifier").SetValue(cnFile, """")

cnFile.Properties("HeaderRowsToSkip").SetValue(cnFile, 0)

cnFile.Properties("HeaderRowDelimiter").SetValue(cnFile, vbCrLf)

cnFile.Properties("CodePage").SetValue(cnFile, 1252)

Dim MyFlatFilecn As SSISRuntime.IDTSConnectionManagerFlatFile90 = Nothing

MyFlatFilecn = TryCast(cnFile.InnerObject, SSISRuntime.IDTSConnectionManagerFlatFile90)

DtsConvert.ToConnectionManager90(cnFile)

Dim name As SSISRuntime.IDTSName90

Dim col As SSISRuntime.IDTSConnectionManagerFlatFileColumn90

cnFile.AcquireConnection(Nothing)

' Add columns to the FlatFileConnectionManager

Dim headers As String()

headers = Split((Dts.Variables("col1").Value.ToString), ",")

'Dim headers As String() = {"col1", "col2", "col3", "col4"}

'Get the header row from flat file. This can be stored in a database table as well.

'Assuming that "headers" Array contains the headers

Dim i As Integer = 0

While i < headers.Length

col = MyFlatFilecn.Columns.Add()

If i = headers.Length - 1 Then

col.ColumnDelimiter = vbCrLf

Else

col.ColumnDelimiter = ","

End If

col.ColumnType = "Delimited"

col.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR

name = TryCast(col, SSISRuntime.IDTSName90)

name.Name = headers(i)

'get the converSion information from the destination table

'this should assign the appropriate datatype, precision, scale and length to col

'getConversionInfo(col, name.Name) 'implement this

i = i + 1

End While

cnFile.ReleaseConnection(Nothing)

'Creating Data Flow which does the actual job of copying the data from flat file to table

'For that, creating flat file Source

Dim DFSource As IDTSComponentMetaData90

DFSource = DTP.ComponentMetaDataCollection.New()

DFSource.ComponentClassID = "DTSAdapter.FlatFileSource"

DFSource.Name = "FlatFileSource"

Dim Sourceinst As CManagedComponentWrapper

' Get the design time instance of the component.

Sourceinst = DFSource.Instantiate()

' Initialize the component.

Sourceinst.ProvideComponentProperties()

' Specify the connection manager.

'If DFSource.RuntimeConnectionCollection.Count > 0 Then

DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("MyFlatFileConnection").ID()

DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("MyFlatFileConnection"))

'End If

' Reinitialize the metadata.

Sourceinst.AcquireConnections(Nothing)

Sourceinst.ReinitializeMetaData()

Dim exOutColumn As IDTSExternalMetadataColumn90

For Each outColumn As IDTSOutputColumn90 In DFSource.OutputCollection(0).OutputColumnCollection

exOutColumn = DFSource.OutputCollection(0).ExternalMetadataColumnCollection(outColumn.Name)

Sourceinst.MapOutputColumn(DFSource.OutputCollection(0).ID, outColumn.ID, exOutColumn.ID, True)

Next

Sourceinst.ReleaseConnections()

'Add the OLEDB Destination

Dim DFdestination As IDTSComponentMetaData90

DFdestination = DTP.ComponentMetaDataCollection.[New]()

DFdestination.ComponentClassID = "DTSAdapter.OLEDBDestination"

DFdestination.Name = "OLEDBDestination"

Dim DestInst As CManagedComponentWrapper = DFdestination.Instantiate()

DestInst.ProvideComponentProperties()

If DFdestination.RuntimeConnectionCollection.Count > 0 Then

DFdestination.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("MyOLEDBConnection").ID()

DFdestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("MyOLEDBConnection"))

End If

'Set the customproperties of the source

DestInst.SetComponentProperty("OpenRowset", Dts.Variables("DestTable").Value.ToString)

DestInst.SetComponentProperty("AccessMode", 0)

'reinitialize the Component

DestInst.AcquireConnections(Nothing)

DestInst.ReinitializeMetaData()

DestInst.ReleaseConnections()

'Map a Connection betwen the source and destination

Dim path As IDTSPath90 = DTP.PathCollection.[New]()

path.AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFdestination.InputCollection(0))

Dim input As IDTSInput90 = DFdestination.InputCollection(0)

Dim destinationInputID As Integer = CInt(input.ID)

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' This will create an input column on the component.

DestInst.SetUsageType(destinationInputID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

' Get input column.

Dim inputColumn As IDTSInputColumn90 = input.InputColumnCollection.GetInputColumnByLineageID(vColumn.LineageID)

' Getting the corresponding external column.

' Ex : We will use the column name as the basis for matching data flow columns to external columns.

Dim externalColumn As IDTSExternalMetadataColumn90 = input.ExternalMetadataColumnCollection(vColumn.Name)

' Tell the component how to map.

DestInst.MapInputColumn(destinationInputID, inputColumn.ID, externalColumn.ID)

Next

'Validate the package

MsgBox("Validating the package")

Dim pkgStatus As DTSExecResult = package.Validate(Nothing, Nothing, Nothing, Nothing)

'System.Console.WriteLine("Validationresult: " & pkgStatus.ToString())

'Save the package

Dim SSISExe As New Application()

SSISExe.SaveToXml("C:\temp\MySSISPackage.dtsx", package, Nothing)

'Execute the package

If pkgStatus = DTSExecResult.Success Then

MsgBox("Executing the Package")

Dim pkgResult As DTSExecResult = package.Execute()

MsgBox("Package validation success")

Else

MsgBox("Package Validation failed")

End If

End Sub

End Class

As you can see in the above code, I have tried to pass the column names into the header array in 2 ways. When I give the column names directly into the array like:

(Dim headers As String() = {"col1", "col2", "col3", "col4"}) script is going good and the data is loaded into the destination table. But When I am trying to pass the column names using a variable like:

( Dim headers As String()

headers = Split((Dts.Variables("col1").Value.ToString), ","), it throws the following error

Error: The script threw an exception: Exception from HRESULT: 0xC0010009,DTS_E_ELEMENTNOTFOUND

Please let me know, where am I going wrong