Dynamically create an SSIS Bulk Insert Package

Question

Hi, Darren!

I have read some of your posts and would like to ask if you have ever created a SOURCE from a flat text file that you will map to an OLE DB destination.  What I am trying to accomplish is writing a custom class that will accept a flat file source and some other parameters.  The applicaiton will then build the data sources necessary and then BULK INSERT the data from the flat text file into the OLD DB Destination.

 

I have the flat file source created and the OLEDB destination created.  My issue is that I don't know how to map the output columns of the SOURCE flat file to the columns in the DESTINATION OLEDB table. 

 

Any thoughts???

 

Here is my code so far:

 

' Create the Package

Dim myPackage As New Package()

'myPackage.PackageType = DTSPackageType.DTSDesigner90

myPackage.Name = "SSISPackage BULK INSERT"

myPackage.Description = "SSIS Package for BULK INSERT"

myPackage.CreatorComputerName = System.Environment.MachineName

myPackage.CreatorName = "MGrimmett"

Dim exeBULK As Executable = myPackage.Executables.Add("STOCK:BulkInsertTask")

Dim thBulkInsert As TaskHost = CType(exeBULK, TaskHost)

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

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

cnFile.Properties("ConnectionString").SetValue(cnFile, strImportFile)

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, """")

'Add the OLE DB and Flat File Connection Managers

Dim cnOLEDB As ConnectionManager = myPackage.Connections.Add("OLEDB")

cnOLEDB.Name = "OLEDBConnection"

cnOLEDB.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=Inbound_Stage; Data Source=NP2SQL3\QA_DMR;Uid=dbabackup;Pwd=pokemon"

thBulkInsert.Properties("Name").SetValue(thBulkInsert, "Bulk Insert Task")

thBulkInsert.Properties("DataFileType").SetValue(thBulkInsert, DTSBulkInsert_DataFileType.DTSBulkInsert_DataFileType_Native)

thBulkInsert.Properties("DestinationConnection").SetValue(thBulkInsert, cnOLEDB.ConnectionString)

thBulkInsert.Properties("DestinationTableName").SetValue(thBulkInsert, strDestTBL)

thBulkInsert.Properties("FormatFile").SetValue(thBulkInsert, strFFileXML)

thBulkInsert.Properties("SourceConnection").SetValue(thBulkInsert, strImportFile)

thBulkInsert.Properties("UseFormatFile").SetValue(thBulkInsert, True)

'Add a Data Flow Task

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

taskDF.Name = "DataFlow"

Dim dfMainPipe As MainPipe

dfMainPipe = TryCast(taskDF.InnerObject, MainPipe)

' Add the Flat File Source

Dim DFSource As IDTSComponentMetaData90

DFSource = dfMainPipe.ComponentMetaDataCollection.New()

DFSource.ComponentClassID = "DTSAdapter.FlatFileSource"

DFSource.Name = "FlatFileSource"

' Add the OLE DB Destination

Dim DFDestination As IDTSComponentMetaData90

DFDestination = dfMainPipe.ComponentMetaDataCollection.New()

DFDestination.ComponentClassID = "DTSAdapter.OLEDBDestination"

DFDestination.Name = "OLEDBDestination"

' Connect, populate the Input collections and disconnect

Dim SourceInst As CManagedComponentWrapper = DFSource.Instantiate()

SourceInst.ProvideComponentProperties()

DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("FlatFileConnection").ID

DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("FlatFileConnection"))

SourceInst.AcquireConnections(Nothing)

SourceInst.ReinitializeMetaData()

SourceInst.ReleaseConnections()

' Create an instance of the destination component

Dim DestInst As CManagedComponentWrapper = DFDestination.Instantiate()

DestInst.ProvideComponentProperties()

DFDestination.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("OLEDBConnection").ID

DFDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("OLEDBConnection"))

DestInst.SetComponentProperty("OpenRowset", strDestTBL)

DestInst.SetComponentProperty("AccessMode", 0)

DestInst.AcquireConnections(Nothing)

DestInst.ReinitializeMetaData()

DestInst.ReleaseConnections()

' Map a connection between the source and destination

dfMainPipe.PathCollection.New().AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFDestination.InputCollection(0))

 

Cheers~

 

Answer 1

KingMonty,

 

Did you receive a response to your requeat. I am trying to do the same fumction. Where can I find a book or documentation on how to do the above? I can't do "Properties" on my OLEDB connection manager. Would you know why? Thanks

 

JGL

Answer 2

The code appears to be a modification of an example found in Chapter 10 of SQL Server 2005 Developer's Guide by Otey.  The chapter is available online at www.windowsitpro.com/common/images/Otey-SSIS%20ch10.pdf

See the last pages for the entire example with some explanations.