SSIS Script Component(transformation) to call SQL command and add result to Data flow

Question

SSIS Script Component(transformation) to call SQL command and add result to Data flow

mwomersleycarter

Mr or Mrs. 500

Points: 513
More actions
March 9, 2010 at 11:13 am

#138626

I am rying to call a SQL expression that will return a value +1 and update the table value, then add this value to the data flow.
Here is my 2008 C# code so far:
using System.Data.SqlClient;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
IDTSConnectionManager100 connMgr;
SqlConnection sqlConn;
SqlDataReader sqlReader;
public override void AcquireConnections(object Transaction)
{
connMgr = this.Connections.SKLookup;
sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
}
public override void PreExecute()
{
SqlCommand Cmd = new SqlCommand("BEGIN DECLARE @NextKey int SELECT @NextKey = SUM(NextSupplierID+1) FROM dbo.TempKeyTest UPDATE dbo.TempKeyTest SET NextSupplierID = @NextKey SELECT @NextKey END", sqlConn);
SqlDataReader sqlReader = Cmd.ExecuteReader();
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.SurrogateKey = sqlReader.GetInt32(0);
}
public override void ReleaseConnections()
{
connMgr.ReleaseConnection(sqlConn);
}
}
I think the problem lies in the sqlReader Call to return the last value. As I am unfamiliar with this library, I would appreciate any help anyone can provide.

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Hold on, you want to take a variable add one to it and update the table? Have you looked at using an EXEC SQL task and pass the variable in as an input or feed it into a sproc and have the sproc do the work.. Script task has a lot of overhead to make this happen.

CEWII

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

You beat me to it Elliot - i strongly second the EXEC SQL approach you outline, *way* easier.

Steve.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Elliott Whitlow

SSC Guru

Points: 102402

March 9, 2010 at 12:40 pm

#1130897

GMTA..

CEWII

mwomersleycarter Mr or Mrs. 500 Points: 513 More actions · Answer 4

Guys,

Thanks for the replies. yes I had considered the EXEC SQL task. I wanted to update the table as each row passes through. So if there is a mid process failure, the table holding the variable remains up to date(given the batch of rows processed). I guess I can always manage errors another way.

My reservations lie in the control process; something like this:

Get VarNextkey(Sql Task)

DO data flow with a Script component that use VarNextKey and increments per processed row

End data flow and write the last value from VarNextKey back to table.

Thus competing the objective in question.

This is a surrogate key operation if anyone hadn't guessed so I am sure lots of people have views on managing surogate keys.

Actually, in writing this, I've had a eureka moment. Talked out the problem and found the solution in doing so.

Many thanks for all your help.

MWC

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I guess I missed this was a pipeline operation..

Also, I see it is SSIS 2008..

Have you looked at the OLE DB Command data-flow component. You could throw the update against the database in the pipeline fairly quickly and easily..

CEWII

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

MWC, maybe you could also make use of checkpoints and the event model? Checkpoints to be able to restart from the point of failure (or at least the batch containing it) and the event model to handle saving off the current value of the FK variable on failure.

Also, if you're going to be the only process loading data, then getting the next key value and incrementing within the package (catering for records not loaded etc) should be safe. Retrieving the next value for each record seems like overkill (again, based on your SSIS package being the only process loading data).

Steve.

mwomersleycarter Mr or Mrs. 500 Points: 513 More actions · Answer 7

Both very Valid points. I hadn't even considered checkpoints; mostly because I have never been posed with this problem until now and I have never used the OLE DB transform before.

Time to put theory into practice, I will update this thread for the benefit of others(if you are interested).

Thank you, again, for your time.

MWC