I’ve recently been working on a large ETL project for a data migration, so I’ve been spending far more time working in SSIS than I would normally do so. As with any ETL project, there’s a substantial portion of outputting data to files, both Excel and text, and moving those around a filesystem to various locations. Worst still, to write to an Excel file, it must already exist, with all the correct column headers. Eurgh. This means I need to create a bunch of template files, and copy them in to place for me to then use as the target in my Excel Destination. I really hate Excel….

SSIS comes with file system tasks built in which would enable me to do this. However, I find them notoriously flaky and unflexible; for instance, if a file might exist, but it also might not exist, they don’t really seem to like it. Plus, sometimes, I just get random errors; I’ll run it again and it will work, without changing a thing. This is neither good for my sanity, or my sleep when I’m on call.

Fortunately, SSIS also has the solution: the Script Task!

Continue reading →