Private Sub BulkCopyDelimitedFile(ByVal batchSize As Integer, _
ByVal columnDelimiter As String, _
ByVal extractFileID As Int32, _
ByVal extractFileFullPath As String, _
ByVal stageTableName As String)
Dim SqlConnectionManager As Microsoft.SqlServer.Dts.Runtime.ConnectionManager
'Set a connection manager object equal to the connection manager named "Stage"
SqlConnectionManager = Dts.Connections("Stage")
'Since the "Stage" connection manager is of type OLEDB we need to modify it to make it compatible with an ADO.NET connection
Dim dbConn As SqlConnection = New SqlConnection(SqlConnectionManager.ConnectionString.Replace("Provider=SQLNCLI10.1;", ""))
'Create a new StreamReader object and pass in the path of the file to be read in
Dim sr As StreamReader = New StreamReader(extractFileFullPath)
'Read in the first line of the file
Dim line As String = sr.ReadLine()
'Create an array of strings and fill it with each column within the extract file by using the Split function
Dim strArray As String() = line.Split(columnDelimiter)
'Create a DataTable object
Dim dt As DataTable = New DataTable()
'Create a DataRow object
Dim row As DataRow
'Open the database connection
dbConn.Open()
'Create a SQLTransaction object in which to execute the Bulk Copy process so that if it fails, all writes are rolled back
Dim bulkCopyTransaction As SqlTransaction = dbConn.BeginTransaction()
'Instantiate our SqlBulkCopy object and set appropriate properties
Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.Default, bulkCopyTransaction)
'Set the BulkCopy destination table name equal to the staging table name provided by the Foreach Loop Container
bc.DestinationTableName = stageTableName
'Set the BulkCopy batch size equal to the size contained in the metadata provided by the Foreach Loop Container
bc.BatchSize = batchSize
'For each column that was found in the extract file
For Each columnName As String In strArray
'Add a column in the data table
dt.Columns.Add(New DataColumn(columnName))
'Add a column mapping in the SqlBulkCopy object
bc.ColumnMappings.Add(columnName, columnName)
Next
'Add the ExtractFileID column to the data table since it doesn't exist in the extract file and wasn't added in the previous For Each loop
dt.Columns.Add(New DataColumn("ExtractFileID", System.Type.GetType("System.String")))
'Add the ExtractFileID column mapping since it doesn't exist in the extract file and wasn't added in the previous For Each loop
bc.ColumnMappings.Add("ExtractFileID", "ExtractFileID")
'Move the the first row in the extract file after the header
line = sr.ReadLine()
Dim rowCount As Integer
'Loop through all rows in the extract file
While Not line = String.Empty
rowCount += 1
'Create a new data table row to store the extract file's data in
row = dt.NewRow()
'Add all column values to the row
row.ItemArray = line.Split(columnDelimiter)
'Add the ExtractFileId
row("ExtractFileID") = extractFileID
'Add the newly created row to the data table
dt.Rows.Add(row)
'Move to the next row in the extract file
line = sr.ReadLine()
End While
Try
'Write the data table to the staging table
bc.WriteToServer(dt)
'If successful, commit the transaction
bulkCopyTransaction.Commit()
Catch ex As Exception
'If an exception occurs, rollback the transaction
bulkCopyTransaction.Rollback()
'Set the Task result to Failure
Dts.TaskResult = ScriptResults.Failure
Throw ex
End Try
'Close the database connection
dbConn.Close()
'Close the BulkCopy object
bc.Close()
'Set the SSIS metadata variable equal to the number of rows loaded
Dts.Variables("ExtractActualRowCount").Value = rowCount
