Create Flat File source programatically

I created a SSIS package, added Script task. created data flow task programatically, trying to add a flat file source component programatically. stuck at this point.

my goal is to add flat file source component to the data flow task and insert into a table in sql server using oledb destination component all programatically.

any help is appreciated. thanks.

You have to build a Flat File Connection Manager first and then reference it in your Flat File Source.

Have you looked at the BOL and previous posts in this forum for guidance?

Thanks.

|||

Iam creating the connection, I want know how I can read the flat file, and insert into a table. he is the code Iam using....

' Add the component to the dataFlow metadata collection

flatfileSource = dataFlowTask.ComponentMetaDataCollection.New()

' Set the common properties

flatfileSource.ComponentClassID = "DTSAdapter.FlatFileSource"

flatfileSource.Name = "FlatFileSource"

flatfileSource.Description = "Flat file source"

' Create an instance of the component

Dim inst As CManagedComponentWrapper = flatfileSource.Instantiate()

inst.ProvideComponentProperties()

' Associate the runtime ConnectionManager with the component

flatfileSource.RuntimeConnectionCollection(0).ConnectionManagerID _

= package.Connections("FlatFileConnection").ID

flatfileSource.RuntimeConnectionCollection(0).ConnectionManager _

= DtsConvert.ToConnectionManager90( _

package.Connections("FlatFileConnection"))

|||

You need to configure your connection manager by defining the file to use, column formats, and all additional properties before setting up your source.

There are also three more methods to call on your flat file source after the code you displayed:

AcquireConnections()

ReinitializeMetadata()

ReleaseConnections()

After that, you should hook up your source with the rest of a data flow and try to execute it.

HTH.

|||

sorry, Iam not explaining the problem correctly.

I do have those 3 lines of code. Iam lot where I have to loop through the columns or rows of my flat file. and how to map the columns to the destination table columns. any good example or reference please. thanks.

|||

Iam using the CreatePackage example in the samples. Iam able to create flat file source connection. not able to read the file.

Private Sub MapFlatFileDestinationColumns()

Dim wrp As CManagedComponentWrapper = flatfileDestination.Instantiate()

Dim vInput As IDTSVirtualInput90 = flatfileDestination.InputCollection(0).GetVirtualInput()

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

wrp.SetUsageType(flatfileDestination.InputCollection(0).ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

' For each column in the input collection

' find the corresponding external metadata column.

Dim exCol As IDTSExternalMetadataColumn90

For Each col As IDTSInputColumn90 In flatfileDestination.InputCollection(0).InputColumnCollection

exCol = flatfileDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)

wrp.MapInputColumn(flatfileDestination.InputCollection(0).ID, col.ID, exCol.ID)

Next

End Sub

trying use this code to map the columns, now my flat file connection is a source instead of destination. hope Iam explaining the problem.

thanks.

|||

External columns are automatically mapped to output columns when you call ReinitializeMetadata of your flat file source.

A good way for testing this would be to configure your package to some point (only a flat file source initially), save the package to a file and then open that file in the designer and inspect the metadata using the advanced component editor.

Thanks.

|||

Hi, i'm trying to 'emulate' a flat file source component using the script component in a data flow task. I need to be able to create a flat file connection and get the columns in the script (the columns change now and then, so i can't create a 'fixed' flatfile connection manager).

After getting the columns, i need to create output columns for the script component and to go through each row of the file and assign the column details.

I have derived column transformation after this source file step, do i need to make changes to the input / output columns of the derived column component in any way?

I also have an oledb destination, how can i generate an sql command to be used in the oledb destination? I know i can use sql from variable in an oledb source, but i can't find it in the oledb destination

I don't need real code examples, but i need some help on what objects need to be created, what functions need to be called etc... I'm very new to ssis programming.