SSIS INTEGRATION SERVICES EXERCISES▼
- Data flow tasks (4)
- Basic data transforms (1)
- Data conversion transforms (2)
- Conditional split transforms (5)
- Lookup transforms (4)
- Looping over files (3)
- Looping over rows (2)
- Merge joins (1)
- Previous versions (18)
- Script tasks (1)
- Variables in script (1)
- Script components (2)
- Accessing file attributes (2)
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!
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:
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:
Note that your folder path will be different.
Create a data flow task to read in data from your permanent base file:
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:
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;
// 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.