March 4, 2014 at 11:50 pm
Hello All,
I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.
The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.
I've tried returning the values as:
Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update'
and
Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'
I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).
What am I doing wrong?
Thanks.
March 5, 2014 at 2:06 am
I tried doing this though I was successful but I feel there has to be a easy way around... I do not have SQL 2012 so did it in 2008... Here is what I did
1: declared 4 variables , @action - String,@Deleted,@Inserted -Int, @Output - object
2: Execute SQL task with FullRowset... Merge statement with OUTPUT clause as OUTPUT $ACTION AS ACTION,INSERTED.*,DELETED.*;
Mapped this result set with the Output variable ( object type )
3: Then ForeachLoop COntainer to loop over output variable mapping $Action with @Action
4: Script task inside container used to increment the value of the @Deleted and @Inserted depending on the value in @Action.
Dim i, j As Integer
i = Dts.Variables("Inserted").Value
j = Dts.Variables("Deleted").Value
If Dts.Variables("Action").Value = "INSERT" Then
i = i + 1
Else
j = j + 1
End If
Dts.Variables("Inserted").Value = i
Dts.Variables("Deleted").Value = j
I do not know VB at all... so if this can be simplified please do so...
March 5, 2014 at 2:26 am
check this one out it will give you a better idea
http://www.sqlservercentral.com/Forums/Topic1232689-392-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply