Write to table from within script tranformation component

Question

Write to table from within script tranformation component

BobMcC

SSCrazy

Points: 2260
More actions
September 7, 2010 at 2:36 pm

#225702

I'm using a script transform to feed data from a text file into a database table.
Sometimes a column of data is too long for the defined size of the database table.
What I'm currently doing is chopping off the extra data, and allowing the data to be inserted into the table.
I'd like to add in my script the ability to insert a warning row into a table. Thus allowing the script transform to complete but let me know that the data wasn't entered exactly as it was sent to me.
i.e. pseudo code...
db table col1 is defined as varchar(8)
txtfileField comes in with "col1data with extra characters here"
Row.col1 = left(txtfileField, 8)
execute sql "insert into statustable 'Col1 data was longer than expected'"
return
You get the idea. No need to correct the structure above, I know it's not syntactically correct.
My question is: is it possible (and probable) to execute a sql insert from a script component in ssis?
What other ways would you handle this?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

Another way to handle this is to add an output to your script component. You would add a row to the second output with the information that you want to log.

This gives how to create multiple outputs.

http://consultingblogs.emc.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx

Russel Loski, MCSE Business Intelligence, Data Platform

BobMcC SSCrazy Points: 2260 More actions · Answer 2

Russel Loski (9/7/2010)
Another way to handle this is to add an output to your script component. You would add a row to the second output with the information that you want to log.

Duh! I can't believe I didn't think of that. That might be a really good idea. Thanks for the suggestion Russel. Keep 'em coming. I'm always looking for alternatives.

BobMcC SSCrazy Points: 2260 More actions · Answer 3

The second output method worked great. Now I have a corollary question for anyone willing to jump in here.

I also want to *update* a table with status.

i.e. based on original problem; if a field is too long I insert a row into an warning notification table.

I also need to update a table to say that the file I'm currently working on had warnings issued.

I see two ways. 1) Update an SSIS variable by unlocking the variable and updating the value. and 2) wait until the processing is done and issue a query from the control flow page in my ssis package that just checks to see if any rows were added to my warning table.

Now that I'm typing this out, choice #2 seems to be the best. Why update a variable possibly multiple times to indicate that there are warnings issued, when I can just do a simple query to determine the same information? Furthermore you're not *supposed* to update ssis package variables except in the postExecute() funtion.

Thanks again, Russell.