SSIS Looping Through Files In Folder

A task I have had to do a number of times in SQL Server Integration Services (SSIS) is to cycle through all the files in a folder, and import each of those files. Here is a step by step guide to how I do that.

1) To a SSIS package add a For Each Loop Container

2) Inside the For Each Loop container, add a For Each Sequence container
3) Right-click on an empty space on the package file, and choose Variables
4) Create a string variable to hold your file-name
5) Right-click On the For Each Loop Container, choose Edit, choose Variable Mappings
6) From the drop-down list, select the variable you created to hold the file-name

7)

Additional Notes:

VALIDATION

When the package executes its first task is to validate connections and other elements. If certain files do not exist when the package first executes, this can cause validation errors and the package can fail.

To avoid this, on the connection or task properties set Delay Validation to True.

Another Problem

UNICODE

In a data-flow task, I got the error message "cannot convert between unicode and non-unicode string types."

The solution was to add a data conversion component to the middle of the data flow task.

To set the data conversion component up, select the columns that need transforming in the input columns list. Then in the list below, select Unicode String, or whatever Unicode type is appropriate. Also set an alias for the  column. The alias should be different from the original column name.

Finally, after closing the conversion component, redo the mappings going into the destination so that your new aliases are used, rather than the original unconverted column.