ADO.NET Command Transformation

Script File:

Overview

Data flow transformation, executing SQL command for each row in a data set. It is similar to the standard OLE DB Command transformion, however the executed SQL command is for ADO.NET connection manager. The script has the following parameters:

Connection – specifies ADO.NET connection manager.
Statement – specifies SQL statement to execute.
Timeout – specifies number of seconds before command times out. Set 0 for infinite time-out.
ErrorRowDisposition – specifies how error rows are handled:
- RD_NotUsed – Same as RD_FailComponent.
- RD_IgnoreFailure – Errors are ignored.
- RD_RedirectRow – Errors are redirected to ‘Error Output’.
- RD_FailComponent – Errors cause component to fail.
ParseExpression – specifies regular expression to parse statement parameters. This option is provided because there is no standard specification of parameters in ADO.NET. You can read more about ADO.NET parameters here. Sample regular expressions for specific providers:
- SQL Server – @(\w+)
- OLEDB, ODBC – (?‘param’\?)
- Oracle – :(\w+)
InputColumns – specifies input columns for command parameters.

Requirements

Depends on CozyRoc Script Component Plus component for implementing parameters user interface.
Requires installation of CozyRoc SSIS+ 1.4 SR-1 library.

Library references

System
System.Data
Microsoft.SqlServer.TxScript
Microsoft.SqlServer.PipelineHost
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SqlServer.DTSRuntimeWrap
Microsoft.SqlServer.ManagedDTS
CozyRoc.SSISPlus

ScriptMain

' CozyRoc SQL Server Integration Services user script component ' ' Copyright (c) 2006-2009 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.ComponentModel Imports System.Data Imports System.Data.Common Imports System.Text.RegularExpressions Imports System.Collections Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() {"Connection", "Statement", "Timeout", "ParseExpression", "InputColumns"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent Private Const ColumnSeparator As String = "," + vbLf ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) ' Setup parameters value. Dim paramsCount As Integer = m_inputIndexes.Length For paramIndex As Integer = 0 To paramsCount - 1 Dim colValue As Object = DBNull.Value Dim inputIndex As Integer = m_inputIndexes(paramIndex) If Not Row.Buffer.IsNull(inputIndex) Then colValue = Row.Buffer(inputIndex) End If m_command.Parameters(paramIndex).Value = colValue Next Select Case Me.ErrorRowDisposition Case DTSRowDisposition.RD_IgnoreFailure Try Call m_command.ExecuteNonQuery() Catch ex As Exception End Try Call Row.DirectRowToOutput() Case DTSRowDisposition.RD_RedirectRow Try Call m_command.ExecuteNonQuery() Call Row.DirectRowToOutput() Catch ex As Exception Row.ErrorDescription = ex.Message Call Row.DirectRowToErrorOutput() End Try Case DTSRowDisposition.RD_FailComponent, DTSRowDisposition.RD_NotUsed Call m_command.ExecuteNonQuery() Call Row.DirectRowToOutput() End Select End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() ' Initialize ADO.NET command. Dim connection As DbConnection = CType( _ MyBase.Connections.AdoNet.AcquireConnection(Nothing), _ DbConnection) m_command = connection.CreateCommand() m_command.CommandText = Me.Statement m_command.CommandTimeout = Me.Timeout Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) ' Get parameters. Dim params() As String = GetParameters_() ' Setup parameters. Dim paramsCount As Integer = params.Length For paramIndex As Integer = 0 To paramsCount - 1 Dim parameter As DbParameter = m_command.CreateParameter() parameter.ParameterName = params(paramIndex) Call SetupParameter_(input.InputColumnCollection(paramIndex), parameter) Call m_command.Parameters.Add(parameter) Next ' Setup dynamic input columns indexes. m_inputIndexes = New Integer(paramsCount - 1) {} For colIndex As Integer = 0 To paramsCount - 1 m_inputIndexes(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(colIndex).LineageID) Next End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PostExecute() Call MyBase.PostExecute() If Not m_command Is Nothing Then Call m_command.Dispose() m_command = Nothing End If End Sub ' PostExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.Connection) Then Throw New ApplicationException("Select ADO.NET connection.") End If If String.IsNullOrEmpty(Me.Statement) Then Throw New ApplicationException("Specify SQL statement.") End If If String.IsNullOrEmpty(Me.ParseExpression) Then Throw New ApplicationException("Specify regular expression for parameters parse.") End If Dim params() As String = GetParameters_() Dim selectedCols() As String = {} If Not String.IsNullOrEmpty(Me.InputColumns) Then selectedCols = Me.InputColumns.Split( _ New String() {ColumnSeparator}, _ StringSplitOptions.None) End If If params.Length > selectedCols.Length Then ' Selected input columns are not enough. Throw New ApplicationException("Select input columns for these parameters: " & String.Join(", ", params)) End If ' Store connection information in the runtime connection collection, too. ' Cannot directly use RuntimeConnectionCollection in the property get/set ' because of issues with multi-threading. Me.ComponentMetaData.RuntimeConnectionCollection("AdoNet").ConnectionManagerID = Me.Connection ' Setup input columns based on selection. Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput() Call input.InputColumnCollection.RemoveAll() For Each col As String In selectedCols If Not String.IsNullOrEmpty(col) Then Dim virtColumn As IDTSVirtualInputColumn90 = virtInput.VirtualInputColumnCollection(col) Call virtInput.SetUsageType(virtColumn.LineageID, DTSUsageType.UT_READONLY) End If Next result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select ADO.NET Connection Manager.")> _ <Category("Command")> _ <Connection("AdoNetConnectionType", True)> _ Public Property Connection() As String Get Connection = m_connection End Get Set(ByVal value As String) m_connection = value End Set End Property ' Connection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specify SQL statement to execute.")> _ <Category("Command")> _ <TextDialog("Specify SQL statement", "", "SQL")> _ Public Property Statement() As String Get Statement = m_statement End Get Set(ByVal value As String) m_statement = value End Set End Property ' Statement ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specify number of seconds before a command times out. Set 0 for infinite time-out.")> _ <Category("Command")> _ <DefaultValue(0)> _ Public Property Timeout() As Integer Get Timeout = m_timeout End Get Set(ByVal value As Integer) m_timeout = value End Set End Property ' Timeout ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specify how to handle error rows.")> _ <Category("Error")> _ <DefaultValue(DTSRowDisposition.RD_FailComponent)> _ Public Property ErrorRowDisposition() As DTSRowDisposition Get ErrorRowDisposition = m_errorRowDisposition End Get Set(ByVal value As DTSRowDisposition) m_errorRowDisposition = value End Set End Property ' ErrorRowDisposition ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specify regular expression to parse statement parameters.")> _ <Category("Parameters")> _ <RegularExpressionDialog()> _ <DefaultValue("@(\w+)")> _ Public Property ParseExpression() As String Get ParseExpression = m_parseExpression End Get Set(ByVal value As String) m_parseExpression = value End Set End Property ' ParseExpression ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select input columns for command parameters.")> _ <Category("Parameters")> _ <InputVirtualColumn("", False)> _ Public Property InputColumns() As String Get InputColumns = m_inputColumns End Get Set(ByVal value As String) m_inputColumns = value End Set End Property ' InputColumns #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property AdoNetConnectionType() As String() Get AdoNetConnectionType = New String() {"ADO.NET"} End Get End Property ' AdoNetConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Parse specified statement with specified regular expression to retrieve parameters. Private Function GetParameters_() As String() Dim result As New ArrayList Dim mc As MatchCollection = Regex.Matches( _ Me.Statement, _ Me.ParseExpression, _ RegexOptions.IgnoreCase Or RegexOptions.Multiline) Dim foundDuplicates As Boolean = False Dim paramsCount As Integer = mc.Count For paramIndex As Integer = 0 To paramsCount - 1 Dim parameter As String = mc(paramIndex).Groups(1).Value If result.Contains(parameter) Then ' Found duplicate parameter. Stop processing. foundDuplicates = True Call result.Clear() Exit For End If Call result.Add(parameter) Next If foundDuplicates Then ' Setup uniquely named parameters. For paramIndex As Integer = 0 To paramsCount - 1 Call result.Add("Param" & CStr(paramIndex + 1)) Next End If GetParameters_ = CType(result.ToArray(GetType(String)), String()) End Function ' GetParameters_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Setup parameter data type. Private Sub SetupParameter_( _ ByVal inputCol As IDTSInputColumn90, _ ByVal parameter As DbParameter) parameter.DbType = GetDbType_(inputCol.DataType) parameter.Size = inputCol.Length ' Get precision. Dim precision As Byte = CByte(inputCol.Precision) If inputCol.DataType = DataType.DT_DECIMAL AndAlso precision = 0 Then ' Decimal type has precision 29. ' http://msdn.microsoft.com/en-us/library/ms141036.aspx precision = 29 End If ' Setup precision and scale. Dim iDbDataParameter As IDbDataParameter = parameter iDbDataParameter.Precision = precision iDbDataParameter.Scale = CByte(inputCol.Scale) End Sub ' SetupParameter_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Return ADO.NET data type from SSIS data type. Private Function GetDbType_(ByVal dt As DataType) As DbType Select Case dt Case DataType.DT_BOOL Return DbType.Boolean Case DataType.DT_STR, DataType.DT_WSTR Return DbType.String Case DataType.DT_I1 Return DbType.SByte Case DataType.DT_UI1 Return DbType.Byte Case DataType.DT_I2 Return DbType.Int16 Case DataType.DT_UI2 Return DbType.UInt16 Case DataType.DT_I4 Return DbType.Int32 Case DataType.DT_UI4 Return DbType.UInt32 Case DataType.DT_I8 Return DbType.Int64 Case DataType.DT_UI8 Return DbType.UInt64 Case DataType.DT_R4 Return DbType.Single Case DataType.DT_R8 Return DbType.Double Case DataType.DT_NUMERIC, DataType.DT_DECIMAL Return DbType.Decimal Case DataType.DT_CY Return DbType.Currency Case DataType.DT_DBTIMESTAMP Return DbType.DateTime Case DataType.DT_DBDATE Return DbType.Date Case DataType.DT_DBTIME Return DbType.Time End Select Throw New ArgumentException("Unhandled type.") End Function ' GetDbType_ #End Region ' Internals #Region "Attributes" Private m_connection As String Private m_statement As String Private m_timeout As Integer Private m_errorRowDisposition As DTSRowDisposition Private m_parseExpression As String Private m_inputColumns As String Private m_command As DbCommand Private m_inputIndexes() As Integer #End Region ' Attributes End Class ' ScriptMain

ADO.NET Command Transformation

Overview

Requirements

Library references

ScriptMain

PASS Summit '10