Output Paramaters in OLE DB Command in SSIS

Monday, March 26, 2007



Today when i was nearly at the verge of finishing my SSIS package i just found out that i really can not complete it at the moment. I am having lot of validations on the input data, lot of look ups to get the reference values and after that i need to insert the rows into a SQL server database. It is not simple insertion, there is a big logic behind inserting rows, checking if the rows containing individual numbers form a sequence so that those can be inserted into a range rather than individual entries. Not only that i need to check if that number is already in the table in some range, if yes i need to discard it, redirect that row to an error output file.

So ultimately, i have to use an OLE DB command component, write my SQL in it to call the stored procedure handling the logic to insert in the database. I was able to map the input columns of the stored procedure with my source input columns without any difficulty. But the problem was how to know which row already existed and how to redirect that row to error output with proper custom message. Well i just did it by hit and trial by having two output parameters in my stored procedure, added two columns to my input and mapped them with the out parameters in the OLE DB command. Then the task was simple, just to have a conditional split after the OLE DB command, check if indicator (one of the OUT parameters) is TRUE, suggesting insertion failed and then copying those rows to an error file that i am maintaining with the other out parameter @Message.

If you want to log general SQL errors that may occur during the execution of the OLE DB command component you can simply configure its error outputs and log the ErrorMessage.

However i am still not able to figure out how can we use the return parameters in a stored procedure to map them using an OLE DB command.

4 comments:

Vahehoo said...

In order to map an output parameter to on in stored procedure you have to use : ? output. So, your SQLCommand property would look like this:

SPName ?, ? output

February 25, 2008 at 6:30 AM
Anonymous said...

It seems like this works for some people, but not for others even if they configure the task correctly. I get errors whenever I include output parameters in the SQLStatement. Yes, I use the correct syntax, but SSIS is not only not returning values, it errs out. I have tried ADO.NET for a ConnType also, and that didn't work either.

April 1, 2008 at 5:14 AM
freak said...

@vahehoo: yes, syntax is correct but still if you try you wont get the value mapped back to the output parameter from the stored procedure

April 18, 2008 at 3:47 AM
sbo said...

CoPilot Live can help make a planned getaway even more enjoyable. With millions of Points of Interest including restaurants, parks to grill, and a variety of attractions, CoPilot Live can help you find and navigate to your ideal destinatisbobeton this weekend. Whether it’s a short drive to the beach or a long vacation getaway, GPS navigation on a mobile device is proving to be a very popular traveling companion

March 13, 2012 at 4:48 PM

Post a Comment

Followers

 

2009 ·Techy Freak by TNB