SSIS – Script Tasks to replace file system tasks

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!

After creating your script task, on double click you’ll be presented with the following screen; from where you can configure the language you want to use for your script task (I’m going with C#) and then also specify the variables you want to pass in to the script task (I haven’t done any yet):-

ScriptTaskOptions.jpg

I’m going to create three types of variables, just to demonstrate how they work in the script task:-

  • A local variable
  • A Package parameter
  • A Project parameter

Local variables can be access either via the button at the bottom left of the design window, or from the right click context menu.  I’m not going to go too much into the variables themselves in this post; suffice to say that I’ve got three variables – a filename that dynamically creates the date, a template filename including full path, and a target directory.  Their names within SSIS will be “user::filename”, “package::TemplateFile” and “Project::TargetDirectory” respectively.

The three variables get passed into the script task as read only variables, and we can then reference them within the script task itself.  To edit the script, we click on, unsurprisingly, the “Edit Script” button.  This will launch a new instance of Visual Studio on your machine, which, due to it’s rapid launch time on my work machine, usually prompts me to go and make a coffee…..

keep_calm_and_trust_your_database_administrator_ringer_coffee_mug-rdde968a405814f90802f05f41f3b359a_x76x5_8byvr_324

Once I’m back, with any luck the new instance of Visual Studio will have launched and I’ll be presented with the following default script (remember this is for C# in this case, VB will be slightly different but then I haven’t used VB for donkeys.)

So, the first thing I’m going to do is declare a string and then assign my SSIS variables to those strings; to do this we use the DTS.variables class.  We can specify the variable we want to use and select it’s value and convert that to a string with the following syntax, Dts.Variables[“Scope::Variablename”].Value.ToString().  So my three variables will be assigned with the following code:-

string FileName = Dts.Variables"User::Filename"].Value.ToString();
string TemplateFile = Dts.Variables["$Package::TemplateFile"].Value.ToString();
string TargetDirectory = Dts.Variables["$Project::TargetDirectory"].Value.ToString();

From here, all I need to do is concatenate my Target Directory and Filename, and then call the System.Io.File.Copy method, including the “overwrite” boolean and setting that to true (basically the bit that SSIS struggles with). Or, if I wanted, I could check to see if the file exists, and delete that file; and then move my new file in. Or, well, pretty much anything that you can do in C#, which is A LOT.

Anyway, the concatenation and copy code looks something like this:-

string TargetFile = TargetDirectory + FileName;

System.IO.File.Copy(TemplateFile, TargetFile, True);

If you want to move a file, rather than copy it, you simply need to do an existence check on the target location and delete any file found there with the same location, then move your file over:-

if(System.IO.File.Exists(TargetFile) == true)
{
	System.IO.File.Delete(TargetFile);
}
System.IO.File.Move(TemplateFile, TargetFile);

This, to me, is so simple, that it’s really not worth the messing about with those pesky file system tasks!

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s