Sql Transactions in Script Task?

Question

Sql Transactions in Script Task?

virtualeffect

Right there with Babe

Points: 736
More actions
November 4, 2008 at 9:46 am

#128495

I've found some sample code, but it's causing errors and I think It's confusing me. I did get the transactions to work by using a sql task, but this code to write to the database is in a script task.
for starters here's the code I found online.. the LookupConnections is causing an error.. that's not one of the options under the intellisence for Connections. Maybe none of this code will help me. Suggestions? What is below is all the code I found but that's not the part that processes the rows..
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Net.Mail
Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlParam As SqlParameter
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("SELECT KeyCustomer, CustomerName FROM tblCustomer WHERE(KeyCustomer = @KeyCustomer)", sqlConn)
sqlParam = New SqlParameter("@KeyCustomer", SqlDbType.Int)
sqlCmd.Parameters.Add(sqlParam)
End Sub
UPDATE: Ok, I see that the lookupconnection was added to the script task outside of the script and then it would be available in the intellisence.. so I am working forward and I'll see how it goes..

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

The LookupConnection is a connection setup in the sample script component. You have to setup the connection you have in use in your package. This is done in the script component main dialog. Select Connection Managers tab and insert it there.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

virtualeffect Right there with Babe Points: 736 More actions · Answer 2

Ok, I backed up and made my own connection and added it to the script component. Then I used a couple of different variations trying to start a transaction with something like this:

Public Class ScriptMain

Inherits UserComponent

Dim connMgr As IDTSConnectionManager90

Dim oConn As SqlConnection

Dim oCmd As New SqlCommand

Dim trans As SqlClient.SqlTransaction

Dim rowCounter As Integer = 0

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.Connection

oConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub

Public Overrides Sub PreExecute()

trans = oConn.BeginTransaction

oCmd.Transaction = trans

....

then , fist I tried to put a trans.Commit() in either the PostExecute() or the ReleaseConnections() .. My thought was that if it didn't reach the commit then it would roll back.. but I could never get it to rollback.. I then added a if that checked for a predetermined row column value that was about 1/2 way through the rows.. and one it was reached --> Rollback like this..

If Row.EmpID = "78118" Then

MsgBox("check data" + rowCounter.ToString + " , " + Variables.writeSuccessCount.ToString)

ComponentMetaData.FireError(0, ComponentMetaData.Name, "Forced Error, Testing", "", 0, False)

trans.Rollback()

End If

that wasn't rolling back.. so I commented that if block out and put the rollback before the executenonquery, like this..

If Not oConn.State = ConnectionState.Open Then

oConn.Open()

End If

trans.Rollback()

oCmd.ExecuteNonQuery()

oConn.Close()

what ended up happening.. is that it rolls back every record entry except for the one.. probably the last one.. so I did get it to rollback, but I need it to only roll back if there is a an error and if so, roll back all records..

Hopefully this code would help you see the problem..

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

It is better to send the complete script . With pieces here and there, I don't quite get the idea of what you are trying to accomplish.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

virtualeffect Right there with Babe Points: 736 More actions · Answer 4

Ok, I was trying to avoid that a little bit becuase it's probably more info than you need, but I can definatly see your point about is seeming "scattered".. so here's the script.. as it is right now.. I had found a link on another page that was supposed to Commit() if a certain number of rows had been processed.. that is when I added the rowcounter to the code... but that isn't working.. here's that link..

http://markmal.blogspot.com/2007/07/ssis-2005-fast-load-into-non-ms-ole-db.html

ok, thanks for your help here..

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Data.SqlClient

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Net.Mail

Public Class ScriptMain

Inherits UserComponent

Dim connMgr As IDTSConnectionManager90

Dim oConn As SqlConnection

Dim oCmd As New SqlCommand

Dim CholesterolReplaceDate As DateTime

Dim BPReplaceDate As DateTime

Dim HRAReplaceDate As DateTime

Dim MedPlanBeginReplaceDate As DateTime

Dim MedPlanTerminationReplaceDate As DateTime

Dim EmploymentTerminationReplaceDate As DateTime

Dim trans As SqlClient.SqlTransaction

Dim rowCounter As Integer = 0

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.Connection

oConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub

Public Overrides Sub PreExecute()

trans = oConn.BeginTransaction

oCmd.Transaction = trans

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Try

oCmd.CommandType = Data.CommandType.StoredProcedure

oCmd.CommandText = "AliquantHRAImportSuccessful"

oCmd.Connection = oConn

If Row.CholesterolResultDate = CType("12/31/9999 11:59:59 PM", DateTime) Then

CholesterolReplaceDate = CType("1/1/1901 12:00:00 AM", Date)

Else

CholesterolReplaceDate = Row.CholesterolResultDate

End If

If Row.BPResultDate = CType("12/31/9999 11:59:59 PM", DateTime) Then

BPReplaceDate = CType("1/1/1901 12:00:00 AM", Date)

Else

BPReplaceDate = Row.BPResultDate

End If

If Row.HRACompDeclinedDate = CType("12/31/9999 11:59:59 PM", DateTime) Then

HRAReplaceDate = CType("1/1/1901 12:00:00 AM", Date)

Else

HRAReplaceDate = Row.HRACompDeclinedDate

End If

If Row.MedPlanBeginDate = CType("12/31/9999 11:59:59 PM", DateTime) Then

MedPlanBeginReplaceDate = CType("1/1/1901 12:00:00 AM", Date)

Else

MedPlanBeginReplaceDate = Row.MedPlanBeginDate

End If

If Row.MedPlanTerminationDate = CType("12/31/9999 11:59:59 PM", DateTime) Then

MedPlanTerminationReplaceDate = CType("1/1/1901 12:00:00 AM", Date)

Else

MedPlanTerminationReplaceDate = Row.MedPlanTerminationDate

End If

If Row.EmploymentTerminationDate = CType("12/31/9999 11:59:59 PM", DateTime) Then

EmploymentTerminationReplaceDate = CType("1/1/1901 12:00:00 AM", Date)

Else

EmploymentTerminationReplaceDate = Row.EmploymentTerminationDate

End If

oCmd.Parameters.Add("@Cholesterol", SqlDbType.VarChar, 11).Value = Row.TotalCholesterol

If Row.CholesterolResultDate_IsNull = True Then

oCmd.Parameters.Add("@CholesterolDate", SqlDbType.DateTime).Value = DBNull.Value

Else

oCmd.Parameters.Add("@CholesterolDate", SqlDbType.DateTime).Value = CholesterolReplaceDate

End If

oCmd.Parameters.Add("@CholesterolLocation", SqlDbType.VarChar, 50).Value = Row.CholesterolLocation

oCmd.Parameters.Add("@BloodPressureTop", SqlDbType.VarChar, 11).Value = Row.bptop

oCmd.Parameters.Add("@BloodPressureBottom", SqlDbType.VarChar, 11).Value = Row.bpbottom

If Row.BPResultDate_IsNull = True Then

oCmd.Parameters.Add("@BloodPressureDate", SqlDbType.DateTime).Value = DBNull.Value

Else

oCmd.Parameters.Add("@BloodPressureDate", SqlDbType.DateTime).Value = BPReplaceDate

End If

oCmd.Parameters.Add("@BloodPressureLocation", SqlDbType.VarChar, 25).Value = Row.BPScreenLoc

If Row.WeightInPounds_IsNull Then

oCmd.Parameters.Add("@WeightInPounds", SqlDbType.Int).Value = DBNull.Value

Else

oCmd.Parameters.Add("@WeightInPounds", SqlDbType.Int).Value = Row.WeightInPounds

End If

If Row.HeightInFeet_IsNull Then

oCmd.Parameters.Add("@HeightInFeet", SqlDbType.Int).Value = DBNull.Value

Else

oCmd.Parameters.Add("@HeightInFeet", SqlDbType.Int).Value = Row.HeightInFeet

End If

If Row.HeightInInches_IsNull Then

oCmd.Parameters.Add("@HeightInInches", SqlDbType.Int).Value = DBNull.Value

Else

oCmd.Parameters.Add("@HeightInInches", SqlDbType.Int).Value = Row.HeightInInches

End If

oCmd.Parameters.Add("@SmokeLast3Mos", SqlDbType.VarChar, 1).Value = Row.SmokeLast3Mos

If Row.Exercise_IsNull = True Then

oCmd.Parameters.Add("@Exercise", SqlDbType.Int).Value = DBNull.Value

Else

oCmd.Parameters.Add("@Exercise", SqlDbType.Int).Value = Row.Exercise

End If

oCmd.Parameters.Add("@BMI", SqlDbType.VarChar, 3).Value = Row.BMI

oCmd.Parameters.Add("@MeetHealthCoach", SqlDbType.VarChar, 1).Value = Row.MeetHealthCoach

oCmd.Parameters.Add("@GroupNumber", SqlDbType.VarChar, 50).Value = Row.GroupNumber

oCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = Row.FirstName

oCmd.Parameters.Add("@MidName", SqlDbType.VarChar, 30).Value = Row.MidName

oCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value = Row.LastName

oCmd.Parameters.Add("@SSN", SqlDbType.VarChar, 11).Value = Row.ssncorrected

oCmd.Parameters.Add("@ElectNotCompleteHra", SqlDbType.VarChar, 1).Value = Row.ElectNotCompleteHRA

oCmd.Parameters.Add("@EngWithHealthCoach", SqlDbType.VarChar, 50).Value = Row.EngWithHealthCoach

If Row.HRACompDeclinedDate_IsNull = True Then

oCmd.Parameters.Add("@HraCompDeclinedDate", SqlDbType.DateTime).Value = DBNull.Value

Else

oCmd.Parameters.Add("@HraCompDeclinedDate", SqlDbType.DateTime).Value = HRAReplaceDate

End If

oCmd.Parameters.Add("@EmpID", SqlDbType.VarChar, 50).Value = Row.EmpID

oCmd.Parameters.Add("@TimestampGood", SqlDbType.DateTime).Value = Variables.sessionTimestamp

oCmd.Parameters.Add("@PersonID", SqlDbType.Int).Value = Row.PersonID

oCmd.Parameters.Add("@FlexCreditAmt", SqlDbType.Decimal, 18).Value = 0

oCmd.Parameters.Add("@Sourcerow", SqlDbType.Int).Value = Row.ClientKey

If Row.DMParticipant_IsNull = True Then

oCmd.Parameters.Add("@DMParticipant", SqlDbType.VarChar, 1).Value = DBNull.Value

Else

oCmd.Parameters.Add("@DMParticipant", SqlDbType.VarChar, 1).Value = Row.DMParticipant

End If

If Row.MedPlanBeginDate_IsNull = True Then

oCmd.Parameters.Add("@MedPlanBeginDate", SqlDbType.DateTime).Value = DBNull.Value

Else

oCmd.Parameters.Add("@MedPlanBeginDate", SqlDbType.DateTime).Value = MedPlanBeginReplaceDate

End If

If Row.MedPlanTerminationDate_IsNull = True Then

oCmd.Parameters.Add("@MedPlanTerminationDate", SqlDbType.DateTime).Value = DBNull.Value

Else

oCmd.Parameters.Add("@MedPlanTerminationDate", SqlDbType.DateTime).Value = MedPlanTerminationReplaceDate

End If

oCmd.Parameters.Add("@EmploymentType", SqlDbType.Int).Value = Row.EmploymentType

oCmd.Parameters.Add("@EmploymentStatus", SqlDbType.VarChar, 1).Value = Row.EmploymentStatus

oCmd.Parameters.Add("@Address1", SqlDbType.VarChar, 35).Value = Row.Address1

oCmd.Parameters.Add("@Address2", SqlDbType.VarChar, 35).Value = Row.Address2

oCmd.Parameters.Add("@City", SqlDbType.VarChar, 25).Value = Row.City

oCmd.Parameters.Add("@State", SqlDbType.VarChar, 2).Value = Row.State

oCmd.Parameters.Add("@ZipCode", SqlDbType.VarChar, 10).Value = Row.ZipCode

oCmd.Parameters.Add("@HDL", SqlDbType.Int).Value = Row.HDL

oCmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 20).Value = Row.PhoneNumber

If Row.EmploymentTerminationDate_IsNull = True Then

oCmd.Parameters.Add("@EmploymentTerminationDate", SqlDbType.DateTime).Value = DBNull.Value

Else

oCmd.Parameters.Add("@EmploymentTerminationDate", SqlDbType.DateTime).Value = EmploymentTerminationReplaceDate

End If

If Row.BestContactTime_IsNull = True Then

oCmd.Parameters.Add("@BestContactTime", SqlDbType.VarChar, 20).Value = DBNull.Value

Else

oCmd.Parameters.Add("@BestContactTime", SqlDbType.VarChar, 20).Value = Row.BestContactTime

End If

If Row.EmailAddress_IsNull = True Then

oCmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 50).Value = DBNull.Value

Else

oCmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 50).Value = Row.EmailAddress

End If

oCmd.Parameters.Add("@RetVal", SqlDbType.Int).Value = "1"

If Not oConn.State = ConnectionState.Open Then

oConn.Open()

End If

'If Row.EmpID = "XXXXX" Then

' MsgBox("check data" + rowCounter.ToString + " , " + Variables.writeSuccessCount.ToString)

' trans.Rollback()

' 'ComponentMetaData.FireError(0, ComponentMetaData.Name, "Forced Error, Testing", "", 0, False)

trans.Rollback()

'End If

oCmd.ExecuteNonQuery()

oConn.Close()

oCmd.Parameters.Clear()

rowCounter += 1

If Variables.writeSuccessCount = rowCounter Then

MsgBox(rowCounter.ToString + " , " + Variables.writeSuccessCount.ToString)

trans.Commit()

End If

Catch e As Exception

oCmd.Parameters.Clear()

Dim vars As IDTSVariables90

Me.VariableDispenser.LockForRead("RptErrorsList")

Me.VariableDispenser.GetVariables(vars)

Dim ErrorMsg As String = "AliquantHRA" + "~" + Row.EmpID.ToString() + "~" + "xxxxx" + Row.ssncorrected.Substring(5, 4) + "~" + e.Message

Dim mySuccessfulError As String = vars("RptErrorsList").Value.ToString() + ErrorMsg + "|"

vars.Unlock()

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForWrite("RptErrorsList", variables)

variables(0).Value = mySuccessfulError

variables.Unlock()

End Try

End Sub

Public Overrides Sub PostExecute()

MsgBox("post : " + rowCounter.ToString + " , " + Variables.writeSuccessCount.ToString)

MyBase.PostExecute()

End Sub

Public Overrides Sub ReleaseConnections()

connMgr.ReleaseConnection(oConn)

End Sub

End Class

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

Okay, I think I know what is happening. You should not open/close the connection after every row processing. I'm not even sure you have to close the connection because connMgr.ReleaseConnection method will take care of this. Here is what you have to do. You must initialize your oCmd object with the connection object before you setup the transaction. Insert the following code for your PreExecute method.

Public Overrides Sub PreExecute()

oCmd.Connection = oConn

oCmd.Transaction = oConn.BeginTransaction()

oCmd.CommandType = Data.CommandType.StoredProcedure

oCmd.CommandText = "AliquantHRAImportSuccessful"

End Sub

Remove from Input0_ProcessInputRow method:

oCmd.CommandType = Data.CommandType.StoredProcedure

oCmd.CommandText = "AliquantHRAImportSuccessful"

oCmd.Connection = oConn

and then also remove:

If Not oConn.State = ConnectionState.Open Then

oConn.Open()

End If

and also remove:

oConn.Close()

Give it a try and let us know the result.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

virtualeffect Right there with Babe Points: 736 More actions · Answer 6

ok.. that was very helpful.. It is committing or Rolling back now, but I can't catch the right part of the ComponentMetaData that is changed when an error is generated to decide if I should Rollback or Commit.. I had thought that it would Commit unless there was an error, but instead, it does whatever line is entered.. either oCmd.Transaction.Commit() or oCmd.Transaction.Rollback()..

So I thought I would but something like this in the ReleaseConnections() instead of just the Commit() or RollBack() line.. :

If ComponentMetaData.(some boolean property that is flipped when an error if fired) then

oCmd.Transaction.Rollback()

else

oCmd.Transaction.Commit()

End IF

The only problem here is that I haven't figured out what part of the ComponentMetaData Object Model I can use to make it go to one or the other.. The code I have is below and the line I manually change from Commit to Rollback will be bolded... This is the farthest that I have gotten so far..

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Data.SqlClient

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Net.Mail

Public Class ScriptMain

Inherits UserComponent

Dim connMgr As IDTSConnectionManager90

Dim oConn As SqlConnection

Dim oCmd As New SqlCommand

Dim CholesterolReplaceDate As DateTime

Dim BPReplaceDate As DateTime

Dim HRAReplaceDate As DateTime

Dim MedPlanBeginReplaceDate As DateTime

Dim MedPlanTerminationReplaceDate As DateTime

Dim EmploymentTerminationReplaceDate As DateTime

Dim trans As SqlClient.SqlTransaction

Dim rowCounter As Integer = 0

Dim rowsInVal As Integer = 0

Dim Timestmpwrite As DateTime