Create an SSIS package that executes c# code

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!!
allanmarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PedroCGDCommented:
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
Sander StadSysteemontwikkelaar, Database AdministratorCommented:
0
allanmarkAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PedroCGDCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
allanmarkAuthor Commented:
Thanks!

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


allanmark
0
PedroCGDCommented:
ok... keep in touch!
regards
0
allanmarkAuthor Commented:
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
PedroCGDCommented:
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
allanmarkAuthor Commented:
Apologies - forgot to close this one.

Answer was spot on; working like a charm!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.