Mr.Sahand (8/25/2014)
I am new to SSIS.:-DI have a number of MS access tables to transform to SQL. Some of these tables have datetime fields needed to go under some rather same complicated rules before sitting in the respected SQL tables. I think I need to use Script component that gets the job done. Since all of these fields need same modification rules, I want to apply the generic code base to all of them thus avoiding the code duplication. What would be the best option for this scenario?
I know I can't use the same Script Component and direct all of those datasets outputs to it because unfortunately it doesn't support multi-inputs . So the question is is it possible to apply a set of generic data manipulation rules
on a group of different datasets' fields without repeating the rules. I can use a Script component for each ole db input and apply the same rule on them each. But it would not be an efficient way of doing that.
Any help would be highly appreciated.:-)
Unfortunately, the creation of 'global' functions in SSIS is not straightforward. Not a job for an SSIS newbie.
Instead, I would suggest either
1) Writing your function in Access and using that.
2) Staging your data in SQL Server and then using a UDF or stored proc to unscramble it.
Perhaps neither of the ideas will work, as I can only guess what you mean by 'complicated rules' - if that is the case, perhaps you would expand a little more on what these rules comprise?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.