Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.