Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to apply same data manipulation codes to a group of SSIS components' inputs? Expand / Collapse
Author
Message
Posted Monday, August 25, 2014 2:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:47 AM
Points: 12, Visits: 24
I am new to SSIS.

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.
Post #1606980
Posted Monday, August 25, 2014 2:17 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 5,107, Visits: 11,911
Mr.Sahand (8/25/2014)
I am new to SSIS.

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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1606981
Posted Monday, August 25, 2014 3:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:47 AM
Points: 12, Visits: 24
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
Post #1606987
Posted Monday, August 25, 2014 11:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 5,107, Visits: 11,911
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1607341
Posted Tuesday, August 26, 2014 1:24 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 752, Visits: 1,323
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.

Post #1607355
Posted Tuesday, August 26, 2014 1:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 2,017, Visits: 5,495
Similar discussion on this thread
Post #1607362
Posted Wednesday, August 27, 2014 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:47 AM
Points: 12, Visits: 24

Come on Eirikur, it is not that similar!
Post #1607821
Posted Wednesday, August 27, 2014 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:47 AM
Points: 12, Visits: 24
But I have already done that with interop dll.
Post #1607824
Posted Wednesday, August 27, 2014 7:32 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 8:35 AM
Points: 1,439, Visits: 8,173
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.




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.
Post #1607829
Posted Wednesday, August 27, 2014 8:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 2,017, Visits: 5,495
Mr.Sahand (8/27/2014)

Come on Eirikur, it is not that similar!


Ooops, wrong number
Post #1607843
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse