Import all files in a directory based on Date

Question

Import all files in a directory based on Date

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 10, 2010 at 3:12 pm

#137953

I was able to write a simple package the uses a for each loop container task and data flow task & import all files into a table but they must be loaded in date order based on the FileName which is on the following file format.
I tried using a script task to sort the files and load into an array but unfortunately it did not work out.
:doze:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 4 posts - 1 through 3 (of 3 total)

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

kbatta SSCrazy Points: 2274 More actions · Answer 1

What about just using the script task to save the file names to a file. Then a data flow task could read each file name and assign its date. These could then be sorted by date and written to a new file.

This sorted file list could be used with a for loop to load the files in the correct order.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I used a scrupt task inside a For Each Loop Task with the following code:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim path As String = Dts.Variables("BaseFolder").Value.ToString

Dim filenames() As String

Try

filenames = Directory.GetFiles(path, "Data.*")

Array.Sort(filenames)

Dts.Variables("FileNames").Value = filenames

Dts.Variables("Execute").Value = True

Catch ex As Exception

Dts.Variables("Execute").Value = False

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

BaseFolder was defin3ed as a read only variable

FileNames & Execute were defined as a ReadWrite Variable.

In the For Each Loop Containor:

The enumerator was defined as: Foreach From Variable Enumerator

The Variable used was User::FileNames

Upon execution I get the following errors:

Error: The object in the variable "User::FileNames" does not contain an enumerator.

Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80131509 "(null)". This occurs when the ForEach Enumerator cannot enumerate.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Here's an example I got from MSDN which may help:

Dim arr As New ArrayList

arr.Add("D:\Tests\a.txt")

arr.Add("D:\Tests\b.txt")

Dts.Variables("User::Collection").Value = arr

Can you also confirm that your User::FileNames variable is of type 'Object'?

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