allanmark
asked on
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!!
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!!
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
I will most certainly give it a go this afternoon and will post back on the outcome.
allanmark
I will most certainly give it a go this afternoon and will post back on the outcome.
allanmark
ok... keep in touch!
regards
regards
ASKER
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
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
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
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
ASKER
Apologies - forgot to close this one.
Answer was spot on; working like a charm!
Answer was spot on; working like a charm!
You have some script components in controlflow and dataflow.
You need those validations row by row? or a dataset?
Regards!