Processing XLS Files

Question

Processing XLS Files

James Tech

Right there with Babe

Points: 762
More actions
February 24, 2011 at 7:33 am

#234268

Hi everyone.
How do I create an SSIS package that will process the following items?
- Scan all the excel files from a given folder (*.xls).
- Get the latest excel file from the scanned folder and process the its content.
Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

James Tech (2/24/2011)
Hi everyone.
How do I create an SSIS package that will process the following items?
- Scan all the excel files from a given folder (*.xls).
- Get the latest excel file from the scanned folder and process the its content.
Thanks!

You have to implement SSIS script where for a specified folder:

1. You will get all files with .xls extension.

2. Sort the returned list of files by modified date/time in descending order.

3. Get the file from the top of the list, which is the latest Excel file.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

James Tech Right there with Babe Points: 762 More actions · Answer 2

CozyRoc (2/24/2011)
James Tech (2/24/2011)
Hi everyone.
How do I create an SSIS package that will process the following items?
- Scan all the excel files from a given folder (*.xls).
- Get the latest excel file from the scanned folder and process the its content.
Thanks!
You have to implement SSIS script where for a specified folder:
1. You will get all files with .xls extension.
2. Sort the returned list of files by modified date/time in descending order.
3. Get the file from the top of the list, which is the latest Excel file.

Do you have any sample reference where I can see how to implement the items you suggested?

What I have right now is an SSIS package which contains "Foreach Loop Container" and within it is the Data Flow Task that contains excel source.

From the "Foreach Loop Editor", I set the collection Enumerator Config to look for files with extension *.xls from a directory. Can you enlighten me how to implement what you suggested based on my current implementation, sorry but I am new to SSIS.

Thank you for the help.

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Do you have any sample reference where I can see how to implement the items you suggested?
What I have right now is an SSIS package which contains "Foreach Loop Container" and within it is the Data Flow Task that contains excel source.
From the "Foreach Loop Editor", I set the collection Enumerator Config to look for files with extension *.xls from a directory. Can you enlighten me how to implement what you suggested based on my current implementation, sorry but I am new to SSIS.
Thank you for the help.

Sorting is not very trivial in VB.NET. You can use the following code to get the latest file:

Public Sub Main()

Dim fiList As FileInfo() = New DirectoryInfo("<your target folder>").GetFiles("*.xls")

Dim latestDate As DateTime

Dim latestFile As String

For Each fi As FileInfo In fiList

If fi.LastWriteTime > latestDate Then

latestDate = fi.LastWriteTime

latestFile = fi.FullName

End If

Next

Dts.Variables("LatestFile").Value = latestFile

Dts.TaskResult = Dts.Results.Success

End Sub

You have to specify ReadWriteVariable LatestFile, which will contain full path to the latest .xls file.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

James Tech Right there with Babe Points: 762 More actions · Answer 4

Sorting is not very trivial in VB.NET. You can use the following code to get the latest file:
Public Sub Main()
Dim fiList As FileInfo() = New DirectoryInfo("<your target folder>").GetFiles("*.xls")
Dim latestDate As DateTime
Dim latestFile As String
For Each fi As FileInfo In fiList
If fi.LastWriteTime > latestDate Then
latestDate = fi.LastWriteTime
latestFile = fi.FullName
End If
Next
Dts.Variables("LatestFile").Value = latestFile
Dts.TaskResult = Dts.Results.Success
End Sub
You have to specify ReadWriteVariable LatestFile, which will contain full path to the latest .xls file.

Thank you for the detailed response CozyRoc. But I'm confused where to insert the code based from what I have in my SSIS package?

I am using C# .NET by the way.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

You have to use the script in the standard Script Task. I would recommend you find good book about SSIS.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

James Tech (2/24/2011)
I am using C# .NET by the way.

You can change the language in the script task properties to VB.NET.

However, once you chose a language and clicked OK, you cannot change it back.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

James Tech Right there with Babe Points: 762 More actions · Answer 7

CozyRoc (2/24/2011)
You have to use the script in the standard Script Task. I would recommend you find good book about SSIS.

Okay, I already got it. My only problem is how to check the output from the script if it is getting the correct file.

Thanks for the help CozyRoc.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

James Tech (2/24/2011)
CozyRoc (2/24/2011)
You have to use the script in the standard Script Task. I would recommend you find good book about SSIS.
Okay, I already got it. My only problem is how to check the output from the script if it is getting the correct file.
Thanks for the help CozyRoc.

If you are using SSIS 2008, you can debug the script task like you would debug a .NET project in Visual Studio.

Just place a breakpoint before the end of the script and just examine the locals and watch windows.

Or if you want to go oldschool, put a message box in there 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

James Tech (2/24/2011)
CozyRoc (2/24/2011)
You have to use the script in the standard Script Task. I would recommend you find good book about SSIS.
Okay, I already got it. My only problem is how to check the output from the script if it is getting the correct file.
Thanks for the help CozyRoc.

You can write the latest file in the SSIS event log. Include the following line of code:

Dim fireAgain As Boolean = False

Call Dts.Events.FireInformation(1001, "Latest File", String.Format("Latest file: {0}", latestFile), String.Empty, 0, fireAgain)

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

James Tech Right there with Babe Points: 762 More actions · Answer 10

I tried debugging it and saw the correct file output.

Thank you so much for the help guys.

Phil Parkin SSC Guru Points: 246950 More actions · Answer 11

I may be coming into this a bit late, but sorting can be done a bit more elegantly than that in .NET. Here's an example:

{

string Path = "C:\\Temp";

FileInfo[] files = new DirectoryInfo(Path).GetFiles();

Array.Sort(files, (FileInfo f1, FileInfo f2) => f2.CreationTime.CompareTo(f1.CreationTime));

MessageBox.Show("The most recently created file is: " + files[0].FullName);

}

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

Phil Parkin (2/24/2011)
I may be coming into this a bit late, but sorting can be done a bit more elegantly than that in .NET. Here's an example:
{
string Path = "C:\\Temp";
FileInfo[] files = new DirectoryInfo(Path).GetFiles();
Array.Sort(files, (FileInfo f1, FileInfo f2) => f2.CreationTime.CompareTo(f1.CreationTime));
MessageBox.Show("The most recently created file is: " + files[0].FullName);
}

The cavalry is always late 😀

Anyway, nice piece of code. I will try to remember it.

(You bet I will have forgotten it tomorrow :-))

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP