Solved

Create an SSIS package that executes c# code

Posted on 2008-10-22
9
827 Views
Last Modified: 2013-11-10
Greetings all

I am in the process of developing an SSIS package fro a client.  I have a table in the databse that needs to be validated before records are copied to various other tables. The validation is quite complex as is the error messaging that needs to accompany any errant records (stoed in another table).

My thoughts were too use c# for this - create a class, with a static method that is call from SSSIS. Is this possible? And if so, how does one go about this? If not, are there alternatives?

In advance, thanks!!
0
Comment
Question by:allanmark
  • 4
  • 4
9 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22775267
Inside SSIS you have several ways to add C# code.
You have some script components in controlflow and dataflow.

You need those validations row by row? or a dataset?
Regards!
 
0
 
LVL 9

Expert Comment

by:Sander Stad
ID: 22775289
0
 

Author Comment

by:allanmark
ID: 22775325
Hi PedroCGD

The validation needs to be row by row.

A brief summary --> check if the branchId exists in BranchMaster - if not 1st error message = "Invalid Id". Check if the year is valid (various conditions) and if not, then add to the error message list, etc for the rest of the fields. When all fields ahve been checked, write out either an error or a "scrubbed" record.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Accepted Solution

by:
PedroCGD earned 250 total points
ID: 22775367
you dont need c# code... you should use only the SSIS components.
Create a new SSIS project

Add a dataflow to you control flow
Open dataflow (by the tab section or double click in dataflow task)
Inside dataflow, add a OLE DB Source to get data from SQL Server
add a lookup transformation. Link source to this lookup  to check for existent branchId, if not exist you can redirect these rows to another transformation or destination
Add a condition split to make the conditions you need
add a destination do insert data in target.

helped?!
regards!

0
 

Author Comment

by:allanmark
ID: 22775438
Thanks!

I will most certainly give it a go this afternoon and will post back on the outcome.


allanmark
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22775454
ok... keep in touch!
regards
0
 

Author Comment

by:allanmark
ID: 22775617
This is really cool!

I see that the "Conditional Split" has various categories of functions. I can't find anything to validate a month -- is numeric, is 1-12.

Have I missed something? or is this somethign one must work around?



allanmark
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22775943
you have the MONTH() function and you have string functions to deal with that.
You also have the derived column transformation or if you have big complex transformation you can add a script to the dataflow as TRANSFORMATION.
You must play a little with SSIS... is simple and intuitive!
Regards
0
 

Author Closing Comment

by:allanmark
ID: 31508693
Apologies - forgot to close this one.

Answer was spot on; working like a charm!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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