SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to apply same data manipulation codes to a group of SSIS components' inputs?


How to apply same data manipulation codes to a group of SSIS components' inputs?

Author
Message
Mr.Sahand
Mr.Sahand
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 34
I am new to SSIS.:-D

I 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.:-)
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18650 Visits: 20451
Mr.Sahand (8/25/2014)
I am new to SSIS.:-D

I 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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Mr.Sahand
Mr.Sahand
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 34
Thanks Phil for the answer.
The rules are not too complicated to be done through T-SQL or Access query or VB scripting.
Although they are many in number. They are mostly related to the cleansing of data.
For the Access date time format as you would probably know there are some mismatches between data/time formats in SQL and Access. That is part of our problem.
I can not do that in Access side on the live data.
If I want to do that in SQL side, I guess I first have to store all those date time data into varchar fields
in sql tables, then modify them using those rules in an sp and then change the data type of all those varchar fields
to date time. That is a workaround comes to my mind. Would it be possible to this(your second suggestion) in a better way?

Also, could you please refer me to an article about SSIS global functions?

Thanks
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18650 Visits: 20451
Mr.Sahand (8/25/2014)
Thanks Phil for the answer.
The rules are not too complicated to be done through T-SQL or Access query or VB scripting.
Although they are many in number. They are mostly related to the cleansing of data.
For the Access date time format as you would probably know there are some mismatches between data/time formats in SQL and Access. That is part of our problem.
I can not do that in Access side on the live data.
If I want to do that in SQL side, I guess I first have to store all those date time data into varchar fields
in sql tables, then modify them using those rules in an sp and then change the data type of all those varchar fields
to date time. That is a workaround comes to my mind. Would it be possible to this(your second suggestion) in a better way?

Also, could you please refer me to an article about SSIS global functions?

Thanks


I haven't worked much with getting data out of Access using SSIS. However, what I had in mind was the creation of clean-up functions in Access which can be used in Access views.

I was then hoping that these views could be used as data sources in SSIS. But it may be that the Access driver allows access only to the data itself - I just can't remember how it all fits together.

If we discount the above possibility, you can do it in SQL Server as you suggest - though I would not do it exactly as you describe.

Instead, I would create one or more staging tables to hold the imported data (with your dodgy dates as varchar(n) as you suggest).

Then, I would use T-SQL to get that data into your target table - possibly calling UDFs to take care of your 'global' code (taking care with performance - UDFs can be slow if implemented poorly).

Using 'global functions' in SSIS. Have a look here and you'll start to get the idea.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
twin.devil
twin.devil
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2381 Visits: 2674
VBA functions related queries are not accessible in SSIS because driver do not allow it.

if you use the custom assembly you will have to deploy these settings every time whenever you want to deploy package in any other environment.

if you are staging your data in SQL SERVER, which will be centralized (i hope so) do the data manipulation on sql server sider, you want need to rewrite the code over and over again. SQL SERVER also support CLR assemblies so you have the choice you can either write code in .Net (Vb.net/C#) or you can convert the logic in t-sql.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15254 Visits: 18607
Similar discussion on this thread
Cool
Mr.Sahand
Mr.Sahand
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 34
Come on Eirikur, it is not that similar!Whistling
Mr.Sahand
Mr.Sahand
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 34
But I have already done that with interop dll.
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4173 Visits: 11639
Phil Parkin (8/25/2014)


Unfortunately, the creation of 'global' functions in SSIS is not straightforward. Not a job for an SSIS newbie.



This is my biggest disappointment about SSIS. Sad



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15254 Visits: 18607
Mr.Sahand (8/27/2014)

Come on Eirikur, it is not that similar!Whistling


Ooops, wrong number:-D
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search