August 25, 2014 at 2:08 am
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.:-)
August 25, 2014 at 2:17 am
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.
August 25, 2014 at 3:06 am
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
August 25, 2014 at 11:54 pm
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.
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.
August 26, 2014 at 1:24 am
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.
August 26, 2014 at 1:45 am
Similar discussion on this thread
😎
August 27, 2014 at 7:18 am
Come on Eirikur, it is not that similar!:Whistling:
August 27, 2014 at 7:19 am
But I have already done that with interop dll.
August 27, 2014 at 7:32 am
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. 🙁
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[/url]
August 27, 2014 at 8:04 am
Mr.Sahand (8/27/2014)
Come on Eirikur, it is not that similar!:Whistling:
Ooops, wrong number:-D
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply