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

Examples of a Script Tasks using external dlls Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 7:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 9, 2014 8:51 AM
Points: 59, Visits: 49
I'm working with an ERP accounting package and exploring the possibility of using SSIS as the primary tool for data conversion (Customers, Vendors, Items, etc.). The ERP package is written in .NET (C#) with a client , business and data layer.
The business layer contains the classes for creating a new object (a Customer for example) along with the methods for validating the object.

I am hoping to use SSIS to read in the data from another ERP system (Excel or delimited files), map those fields to the properties of the Customer class, validate the customer, then use the data layer to write the object to the SQL Server tables.

Is this possible to do in SSIS? Using the dlls that come with the Accounting package or would I need to develop my own C# scripts based on the existing dlls?

If anyone can point me to some good examples, I would appreciate the help!
Post #1566720
Posted Thursday, May 1, 2014 11:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
You can use external assemblies in SSIS Script Tasks and Components but they have to be strong-named and in the GAC. Here are a couple of older blog posts about it, but I don't think anything has changed:

http://consultingblogs.emc.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-VSA-requires-DLLs-to-be-in-the-Microsoft.Net-folder-_2800_but-not-all-the-time_2900_.aspx
http://microsoft-ssis.blogspot.com/2011/05/referencing-custom-assembly-inside.html




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566801
Posted Friday, May 9, 2014 9:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 9, 2014 8:51 AM
Points: 59, Visits: 49
I think I understand the big picture of using external dlls in the package. I've added a Script component to my data flow task, added the references and using statements to it, and can create new instances of my objects (a Customer, for example). And since I will probably never 'deploy' this package , I don't need to worry aboutthe GAC.

My question is more to do with the mechanics of saving the data once the Customer is created. Do I let the business/Data layer in the dlls handle that by creating a connection provider within the Script component, then saving that object to the provider and let it handle the table updates? Or do I need to set up a destination connection in the package and somehow take the Customer and break it out and insert records into the appropriate tables?

I haven't been able to find an example of a Script Component that does this. The examples I've found all take data into the S.C., transform it, then send it back to the packageas output, then the package proceeds on.

Ideally, my package would read the data from an input source, then send that data to the Script Task. The Script Task would create the object (Customer) using the input data, then persist that object into the database using the Data layer dlls. Is something like that possible?
Post #1569329
Posted Monday, May 12, 2014 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
Carlton,

I'm sure you could have the Script Task (Component) do it. You would need to to be a destination and not a transformation and then make sure you handle errors and send out the appropriate return of success or error. I have not used a s Script Component as a destination in the data flow I have always used it a as a transform.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1569815
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse