May 1, 2014 at 7:53 am
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!
May 1, 2014 at 11:22 am
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://microsoft-ssis.blogspot.com/2011/05/referencing-custom-assembly-inside.html
Jack Corbett
Consultant - Straight Path Solutions
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
May 9, 2014 at 9:04 am
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?
May 12, 2014 at 6:35 am
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
Consultant - Straight Path Solutions
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply