I am very new to SSIS, as in two days ago my boss said do it this way. Currently we use Access VBA to parse files and populate the tables. As we get more data as in millions of records this is way to slow. The user selects a file from a directory and it populates tables. I am trying to convert this process to SSIS. I have created package that does the load of the main table but before I can add records to the Mail table I need to have a job record (see layout below). The jobs table contains summary details on jobs in the imported file. Cycle Date, is the first 4 characters of file name, Job number is a field in the file. Basic is a count of Job records with a certain values in ST_ID, Full Service is the count of Job records with a different value in ST_ID, Total Pieces  is the total number of records for each job. In addition Mail Date needs to be input by the user. I am not sure how to do this I guess I could create a script that would summarize the file and write records to the Jobs Table. But how do I get the Mail date. Alternatively I have been looking at for transform loops and the possibility of using that to summarize the file. Can anyone point me in the right direction as to the best way to get this done. 

Hi Salmmer,

Where manual import is involved SSIS is not the best tool for the job, I think your app needs to consist of two parts:

1) UI (user input), and

2) Background server side processing (by SSIS if you insist).

So the #1 can give you the file name, dates, etc.

To sum up the file we need to see its example, to me it looks like e.g. if there is a header record.

I still believe this is the fastest most reliable way to load large files to a SQL Server is SSIS. To stress this point MS access, on a good day, takes 4 hours to parse a 3.5 million record file and load it to a local SQL Server, SSIS transform finished in minutes. I have heard that this is because of Access buffering before SQL. I have made progress, I have an Aggregate function that outputs File Name, Job Number,  ST_ID, and Counts. I just need to get it into one row per job, Then insert that row into a table.

JobNum, File Name(1,4), 311 count, 271 count, Total count

With that said I will still need a Front end. It would be nice to ask the user to select the file and input the Mail Date then pass that to the SSIS. After the load is complete I need to produce a report and send an email that the load is completed letting the user know it completed without errors.