Solved

How to use scripting component  as transformation in SSIS

Posted on 2016-10-17
2
23 Views
Last Modified: 2016-10-27
Hi Guys,
I want to perform some operations for each record from the source and then do some operations in scripting component. I also want to catch the error and save the error in a log table and if no error then insert record in destination table.

Is there any tutorial on this?  I need a starting point though, I assume there would be some sort of variables i would need to use?

regards
0
Comment
Question by:shah36
2 Comments
 
LVL 13

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41859616
It sounds like you want to use implement a transformation where the data is processed row by row but which can, in effect, provide an error output if internal processing fails.

You can implement more than one output on the transformation, and send "good" rows to one output and "bad" rows to the other. This assumes that your code can trap any execution errors internally, which should be straightforward to do using ordinary error handling.

It has been a few years since I have written one of these but I believe you can use a synchronous transformation with multiple outputs and exclusion groups to do what you want to do.

The DirectToRow method, called within your script, lets you select the output to use for each input row, and you would use it to send to one output on success and another on failure. The two types of insert can then be performed in downstream components, one on each output. Exclusion groups are the key to, in effect, "branching" within the data flow following execution of the script component.

Have a look at the above link (select the version of SSIS that you are using) and see if that is what you are looking for, given the approach that I suggested above.
0
 

Author Closing Comment

by:shah36
ID: 41861870
Thank you so much for your help. Yes that's what i needed.

Regards,

Ali
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Expressions are in essence, code. If you had an expression 2 + 2 it would evaluate to 4. A string expression of “Hello” + “ “ + “World” would evaluate to “Hello World”. Expressions are an excellent choice for working with dynamic variables, espec…
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now