SSIS Integration Services | Accessing file attributes exercise | Use a script task to find and read the latest file in a directory

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

You can learn how to do this exercise if you attend one of more of the courses listed below!

Software ==> SSIS Integration Services  (46 exercises)
Version ==> SSIS 2012 and later
Topic ==> Accessing file attributes  (2 exercises)
Level ==> Harder than average
Courses ==> Fast track SSIS  /  Advanced SSIS
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The folder above contains 6 files:

Six files

The permanent base file is there to ensure that SSIS always has something to find during the validation phase of your package.

 

Create a package called Open most recent file, and within this create this variable:

Variable for file path

Note that your folder path will be different.

Create a data flow task to read in data from your permanent base file:

Data flow task

Use a data viewer to show what you've loaded from the Excel workbook.

 

Tell your Excel connection to gets its path from the FilePath variable that you've created:

Connection property

Set the ExcelFilePath to be the value of your variable.

 

Create a script task to loop over all of the files in the permanent base file's folder, working out which one was last changed, and setting this file's path into the FilePath variable at the end of this script.

Even for an experienced programmer this isn't that easy.  The script you could use is shown below - it's up to you whether you refer to it!

Make a small change to one of the files in the folder at random.  When you run your package, it should load data from this workbook.

Here's a script that you could use: 

// get the current file path

string filePath = Dts.Variables["FilePath"].Value.ToString();

// get this file

var thisFile = new FileInfo(filePath);

// initially there is no file to use

FileInfo fileToUse = null;

// get a reference to this folder

var thisFolder = thisFile.Directory;

// loop over all of the files in this folder

foreach (var f in thisFolder.GetFiles())

{

// ignore base file, since this is accessed & updated during

// the validation stage, even though it's never used!

if (!f.FullName.ToLower().Contains("permanent base file"))

{

if (fileToUse == null)

{

// if this is the first file, set it to be the one

// to use (subsequent files may replace it)

fileToUse = f;

} else

{

// if this file was modified since the currently

// considered one, change the latter

if (f.LastWriteTime > fileToUse.LastWriteTime)

{

fileToUse = f;

} } } }

// update the file to be used

Dts.Variables["FilePath"].Value = fileToUse.FullName;

} }

When you've finished, don't forget to close down your package. 

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post