August 1, 2016 at 9:52 am
I am trying to improve the script below, but I know virtually nothing about VB or OOP. This script task is in one of our SSIS Import packages. It is parsing columns from a tab-delimited text file into a staging table. The error message we're capturing is emailed to a team to contact the customer. Is there a way to get the column NAME rather than the Index here? (Bolded location)
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
Protected Structure FileSpecStructure
Dim FileTypeId As Integer
Dim HasHeaders As Boolean
Dim ColumnDelimiter As String
Dim TextQualified As Boolean
Dim SourceColumnsArray As Integer()
Dim ImportColumnsArray As Integer()
Dim FixedWidthLengthsArray As Integer()
End Structure
Private mySqlCN As SqlConnection
Private myFileSpec As FileSpecStructure
Private myComponentMetaData As IDTSComponentMetaData100
Private importsSqlConnectionManager As IDTSConnectionManager100
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
importsSqlConnectionManager = Me.Connections.importsSqlConnectionManager
mySqlCN = CType(importsSqlConnectionManager.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub ReleaseConnections()
importsSqlConnectionManager.ReleaseConnection(mySqlCN)
End Sub
Public Overrides Sub PreExecute()
myComponentMetaData = Me.ComponentMetaData
myComponentMetaData.FireInformation(0, "PreExecute", "Initializing import file parsing for file [" & Me.Variables.importFilePath & "]", "", 0, False)
myComponentMetaData.FireInformation(0, "PreExecute", "Loading import file specifications for AOKey [" & Me.Variables.aoKey & "]", "", 0, False)
End Sub
Public Overrides Sub CreateNewOutputRows()
Using parser As New FileIO.TextFieldParser(Me.Variables.importFilePath)
Dim data As String()
parser.SetDelimiters(New String() {vbTab})
parser.TrimWhiteSpace = True
parser.HasFieldsEnclosedInQuotes = False
'LOOP THROUGH IMPORT FILE - FOR EACH ROW MAP FIELDS TO THE CORRECT IMPORT COLUMNS
While parser.EndOfData = False
data = parser.ReadFields()
Output0Buffer.AddRow()
'VALIDATE COLUMN COUNT IN THE IMPORT FILE
If UBound(data) < 9 Then
myComponentMetaData.FireError(10003, "CreateNewOutputRows", "Import file column count is not equal to the defined column column count for this client.", "", 0, True)
Exit Sub
End If
'LOOP THROUGH THE PARSED FIELDS ASSIGNING THE DATA TO THE CORRECT IMPORT COLUMN
For idx As Integer = 0 To UBound(data)
outputMapper(idx, data(idx), parser.LineNumber)
Next
End While
End Using
End Sub
Private Sub outputMapper(ByVal index As Integer, ByVal value As String, ByVal lineNumber As Integer)
If value.Length = 0 Then
Exit Sub
End If
Try
Select Case index
Case 0 : Output0Buffer.institutionorgnodecode = value
Case 1 : Output0Buffer.username = value
Case 2 : Output0Buffer.CertifyingBody = value
Case 3 : Output0Buffer.CertificationName = value
Case 4 : Output0Buffer.Issystemmanaged = value
Case 5 : Output0Buffer.ExpirationDate = value
Case 6 : Output0Buffer.certificationnumber = value
Case 7 : Output0Buffer.countryname = value
Case 8 : Output0Buffer.stateabbr = value
Case 9 : Output0Buffer.remove = value
End Select
Catch ex As Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException
Throw New Exception("An overflow exception occured while trying to write the value (" & value & ") to column index:" & (index + 1).ToString & " at line number: " & (lineNumber - 1).ToString & ".")
End Try
End Sub
Private Function IntegerFromString(ByVal value As String) As Integer
Return CInt(value)
End Function
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply