October 16, 2008 at 9:20 pm
i want to write a if statement in transformations in ssis packages ......
IF Partner = active .........'1' else '0'
thank you
Vasu
October 17, 2008 at 9:09 am
You can do this in a script transformation using something simlar to the script below. Please note it makes use of an ADO.NET connection for the lookup (represented as LookupConnection).
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Public Class ScriptMain
'Author: Tommy Bollhofer (thomas_bollhofer@symantec.com)
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlParam As SqlParameter
Dim RowExist As Integer
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.LookupConnection
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
sqlCmd = New SqlCommand("IF (SELECT Partner FROM dbo.MyTable WHERE PrimaryKey = @IDENT AND Active = 1) SELECT 1 AS RowExist ELSE SELECT 0 AS RowExist", sqlConn)
sqlCmd.CommandTimeout = 0
sqlParam = New SqlParameter("@IDENT", SqlDbType.BigInt)
sqlCmd.Parameters.Add(sqlParam)
End Sub
Public Overrides Sub SourceInput_ProcessInputRow(ByVal Row As SourceInputBuffer)
sqlCmd.Parameters("@IDENT").Value = Row.AIFID
RowExist = CInt(sqlCmd.ExecuteScalar)
If RowExist = 1 Then
Row.DirectRowToOutputDeletes()
GoTo AllDone
ElseIf RowExist = 0 Then
Row.DirectRowToOutputInserts()
GoTo AllDone
End If
AllDone:
End Sub
Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(sqlConn)
End Sub
End Class
Tommy
Follow @sqlscribeViewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply