Using SSIS with SAP

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.