This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	SSIS Integration Services (28 exercises)
Topic ==>	Accessing file attributes (2 exercises)
Level ==>	Harder than average
Subject ==>	SSIS training

Get the most recently changed file using a script task to loop over files

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.

Before you can do this exercise, you'll need to download and unzip this file .

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 file 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;

} 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.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post

EXERCISE TOPIC

SSIS EXERCISES

SSIS INTEGRATION SERVICES EXERCISES

Get the most recently changed file using a script task to loop over files

Head office

London

Manchester