Determining Excel spreadsheet format before Data Flow Task

Question

I'm working on an SSIS package which uses a for each loop to iterate through excel files in a directory and a data flow task to import them.

The issue I'm having is that the project manager I'm working with doesn't think the users will always follow the structure. So if a file is in the folder and the package tries to import it but the spreadsheet is missing columns or has extra columns it generates and error of course. Even though I have the task set to not fail the package; the package does indeed fail and then the other files aren't imported.

So, I'm wondering what is the easiest way to either determine the spreadsheet is incorrectly formatted, or stop the error from failing the package execution? After taking said step I would just use a file copy task to move the file to a "Failure" folder. Then continue on processing the spreadsheets.

Unknown Unknown · Accepted Answer · 2012-12-12T23:00:39

I have a SSIS package set up similar to yours, except it is iterating through a list of Microsoft Project files and importing the records into a database. Within the Foreach Loop Container it is first performing a check to see if the file exists. I am passing to the script the string variable of FileName and the boolean variable bolFileExists. I know you are seeking to also verify if the file is correctly formatted and am guessing that you may be able to apply something similar to the Script Task code below:

    public void Main()
    {
        if (File.Exists(Dts.Variables["FileName"].Value.ToString()) )
        {
            Dts.Variables["bolFileExists"].Value = true;
        }
        else
        {
            Dts.Variables["bolFileExists"].Value = false;
        }
    }

Leading from this would be two paths. The first would apply the expression "@bolFileExists==True" and then perform the import process. The other would apply the expression "@bolFileExists==False" and skip that file and allow the following files to be processed. In my SSIS package that is table driven, for both options, the import status is defined in a table listing the files being imported. I have nearly 40 files that are imported and this allows me to run a query to determine if any files were missed and apply that query in an automated email sent out after the process is complete.

As for the incorrect formatting, my first recommendation is to provide the users with a template to follow with perhaps instructions in one of the worksheets. After that you could try applying a staging table or SQL that gathers the list or count of columns included in the worksheet of interest. The approach you take here depends on the complexity of the information being pulled. Again in my SSIS package, the data can still be off after it is imported. In this case, I am first importing into a temporary staging table in which checks are performed and only that which meets the criteria of interest is then normalized into the final detination tables using a MERGE.

Let me know if you have any questions. Hope this helps with part of the issue.

Determining Excel spreadsheet format before Data Flow Task

3 Answers