Regarding SSIS Implementation Difficulties In Migration

We would like to Share the BatchJobs functionality before going to the difficulties what we are facing in SSIS package Design and it's implementation.

BatchFile Process Steps:

1.Flat File is coming from the upstream server(DB2 main Frame Server) to the linux box in which the jobs are scheduled using crons.

2.The Flat file is validated whether having header and trailer information.The flat file name itself (eg. APPLWIC00077.dat ) will have it's sequence number ie 00077.This number will be checked against Flat file Header information Which also gives the sequence number information .If both number matches,The Loading will be initiated.Same way checking happens with Trailer.If anything goes wrong,mail will be sent and Loading will not happen.

3.The Job will process the coming flat file and Loading it into SQL Server Staging Table.

4.If loading is successful,then Some Sql server Procedure are called to validate the Staging Table Data,Filters,then move the Data from Staging table to Target table.

5.All Process or Steps are tracked,If anything goes wrong,Mail will be sent to Concerned Person.

Utilities Involved:

1. SQL Loader to Load the FlatFile Data into the SQL Server 2005 Database.

2. SQLPLUS to execute the PL/SQL procedures.

3. SENDMAIL or MAILX to send mails to externals.

Additional Information:

The SQL Loader Control File Splits the flatfile data by Position wise in order to load it into the particular column of the Staging Table.No column delimiters.Data are taken position wise (like column1 -> Sequence No , 1-3 -> column2 , 4-8 -> column2 )and checked against a condition By which the particular Staging Table is selected.





Difficulties We face in migration in regard to SSIS Package Design and it's Implementation:

1. We could Receive the flat file using FTP Task and we don't know how to open and validate the flat file data Like What we mentioned in STEP 2.

2. We could load the sample flat file which is properly delimited like {CR}{LF} ,{CR},{LF},semi -colon,comma,colon,tab,vertical bar .

If flatfile is messed up and data need to be processed by position wise (Scenario mentioned in Additional Information) ,We don't know how to implement this in SSIS package with the Limited delimiter option given.

FYI : Flat File columns are not fixed and ragged type.

3. We need logging information like howmany rows inserted, discarded,something in detail..How can we log these information using SSIS.This will help to send detailed mail to Application handler.It should be automated using SSIS.

4. How can we execute Procedures Using available SSIS tasks ( Executing many Procedures which is residing in Sql server 2005 using Single sql file as input to SSIS package)



Thanks in Advance!
Your input is priceless.

Regards,
Prabhu.M