SSIS load text file name into table for ETL auditing Unicode error

The old saying still rings true, you get what you pay for, pay nothing, expect nothing, this excellent post details how to read the file name but this post will cause a unicode error because the its missing details at this part

"Be sure to change the DATA TYPE from UNICODE to STRING.  Select OK to confirm changes."  This is impossible unless you do this

Click on Show Advanced Editor

to change the data type to String (see screen shot below)

Then your data type will be string (see below)

And then you will not have unicode errors.

Integrate Perl scripts into SSIS

Perl is such a powerful open source language with many modules for handling XML requests, whois lookups and database interfaces for all major open and closed commercial databases.

Being able to determine if a script has successfully run in SSIS is very important.

Using Perls exit value command allows the value of a successful execution to be passed to the SSIS process task.

Then set the value in SSIS;

Useful sources:

SSIS & SQL how to articles

Perl how to

To pass a variable from the perl script use the print command, it will be captured as a string in the SSIS 'StandardOutputVariable.  A useful how to guide here.