Tuesday, August 18, 2009

Using SSIS with SAP

I'm reading: Using SSIS with SAPTweet this !
I have been working since a few months on using SSIS with SAP. Generally while working with the functional consultants, it can be a very hard time getting the architecture in shape keeping in view their understanding of technology in alignment with the requirements. There are certain points which a SSIS developer or designer should keep in mind while working on a project where SSIS would be used for catering data to SAP environments from legacy systems, and to make it worse if the project is of data migration.

1) An SSIS package designed for IDocs would be much simpler to understand and design compared to the same for Screen recorded sessions.

2) Always sort the data after reading (or you can also use the IsSorted property setting) and sort the data before you write it down to the file output.

3) Audit the data to the necessary and sufficient level. More auditing would break a developers shoulder and less would break the functional / technical SAP consultant's shoulders.

4) Never let your main package read the data directly from file input. Stage the data into a relational storage like a table. Also never read directly from the table. Always keep an interface like a stored procedure or a view from which your package should read. This design would have the advantage of more resistance towards changes, which is the theme of any such project.

5) Always keep the main control in the database which designing the architecture, for eg. configuration settings, loop iterator collections, file paths etc.

6) Generally data objects like Materials, Customers, Vendors, Finance, Payroll, Employee, etc... are the kind of data objects that gets designed in the SAP system. The first three are the foundation stones to the best of my SAP knowledge till date.

7) Try to use less variety of data types, and the rule of best fit data type does not apply to data migration project. More the synchronization you have at your relational staging area, the less pain you would have while designing your package.

8) Be well versed with casting operators and functions, as generally you would require to use them a lot in such projects.

9) Import - Export wizard can be a nuisance if the text file from which you are importing the data have duplicate column names. If you are working with text files as input source files, make sure you do a basic minimum profiling of the source files.

There are many more points worth mentioning, but for now this is just a quick starter.

5 comments:

Thiru said...

Hey Siddhart,

I am working as a SSIS lead in one of the UAE based MNC.I am in the situvation of loading SAP CRM data into SQLServer 2005 using SSIS 2005/2008.

Can you please guide me to achive this.

Thanks
Thiru

Siddharth Mehta said...

Hi Thiru,

Please send me your exact issue and/or requirements, and then I can comment/advise on the same.


Regards,
Siddharth.

gvvinay said...

Hey Siddharth,

I have some data from different sources on SAP CRM which i am trying to integrate. Basically my choices are between MS SQL and SAP's Data Quality management. Can you please share any info which will help me in my decision.

Thanks and regards,
Vinay

ss said...

Hey Siddhart,

I am stuck in such type of data fetching data from SAP BW using SSIS by using Open Hub method.

we tried alot...but no use...

Is there any other possiblity to fetch the data from sap.....

regards,
Sai.

Siddharth Mehta said...

Generally organizations would not want any app to directly hook into SAP database. If you can get them to export data into text file formats (which can be done very easily is SAP), then reading data would be very easy for you. Do plan to agree of the export schema before you plan to export data out from SAP DB.

Related Posts with Thumbnails