SSIS script to create folders

Question

SSIS script to create folders

Newbie36037

SSChasing Mays

Points: 654
More actions
September 18, 2015 at 7:08 am

#321376

I need to create a package to do the following
the package will have 3 input parameters
1) Startdate
2) Enddate
3) RecordCount
I'm executing a sql task which reads an 'Orders' table and extracts as many records as meets the Start - End date selection, part of the data extracted is a URL to a document location. Using the URL I then need to copy the document to a folder called 'Orders' which will potentially have subfolders. These subfolders will be created if the number of records retrieved is greater than the RecordCount parameter, so if the parameter is set to 1000, then a new subfolder should be created for every 1000 records, these subfolders will be incrementally named so I'll end up with a folder structure something like
Orders
|___|--> 1
|--> 2
|--> 3
|--> 4
How can I script the folder creation, I assume I'll need a couple of variables to hold the current record count and the folder number, but I've no idea how to script this. Any help would gratefully appreciated

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

rather than an arbitrary count of 1000 or some other static value, would it make more sense to put files in folders based on the dates?

you could group by month, for example, and create any folders with the month name,and logically stick orders in the proper month.

for me, it would be a lot more intuitive. if i new there was an invoice from June, i'd have to poke and hunt through folders, but with an intuitive date name, i could drill in quicker.

code wise, it's just two lines a script task:

//determine the right assignment to the variable via code?

string dir ="2015_01"

if (!System.IO.Directory.Exists(dir))

{System.IO.Directory.CreateDirectory(dir);

--copy files too this directory?

}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Newbie36037 SSChasing Mays Points: 654 More actions · Answer 2

The documents are being migrated to a new application, so at the moment the date of the order is unimportant as all documents will eventually be moved

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Lowell (9/18/2015)
rather than an arbitrary count of 1000 or some other static value, would it make more sense to put files in folders based on the dates?
you could group by month, for example, and create any folders with the month name,and logically stick orders in the proper month.
for me, it would be a lot more intuitive. if i new there was an invoice from June, i'd have to poke and hunt through folders, but with an intuitive date name, i could drill in quicker.
code wise, it's just two lines a script task:
//determine the right assignment to the variable via code?
string dir ="2015_01"
if (!System.IO.Directory.Exists(dir))
{System.IO.Directory.CreateDirectory(dir);
--copy files too this directory?
}

+1

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Newbie36037 SSChasing Mays Points: 654 More actions · Answer 4

Do I just create a variable to hold the record count, in the same way as you created the directory variable, if so how do I reset to 0 once the folder size limit (1000) is reached

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Newbie36037 (9/18/2015)
The documents are being migrated to a new application, so at the moment the date of the order is unimportant as all documents will eventually be moved

These types of applications normally skyrocket with files. I recommend you make a plan to figure out a meaningful directory structure now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Newbie36037 SSChasing Mays Points: 654 More actions · Answer 6

The files will only be extracted once, I'm putting them in subdirectories as I don't know the process that the 3rd party software will use to reload them

Lowell SSC Guru Points: 323518 More actions · Answer 7

the sub folders would server no purpose then,except make the processing a little more difficult. processing a single folder vs lots of subfolders just adds to teh complexity of later processing.

i'd just stick everything in a single directory.

'if you just HAVE to have them in sub folders, i would make the process stick them all in a single folder, and then use a script task to move files into sub folders based on a counter after the fact.

this is my untested guess at the code:

public void processFolder()

{

string TargetDirectory = "C:\\Data";

string FileExtensionsToProcess = "*.xls";

int FileCounter = 1;

int FolderName = 1;

foreach (string FileName in System.IO.Directory.GetFiles(TargetDirectory, FileExtensionsToProcess))

{

string FileOnly = System.IO.Path.GetFileName(FileName);

try

{

if (!Directory.Exists(Path.Combine(TargetDirectory, FolderName.ToString())))

{

Directory.CreateDirectory(Path.Combine(TargetDirectory, FolderName.ToString()));

}

File.Move(FileName, Path.Combine(TargetDirectory, Path.Combine(FolderName.ToString(), FileOnly)));

}

catch

{

}

FileCounter += 1;

if ((FileCounter % 1000) == 0)

{

FolderName += 1;

}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Newbie36037 SSChasing Mays Points: 654 More actions · Answer 8

Cheers Lowell,

With a bit of tweaking that gave me what I needed, and saved me hours of frustration

Lowell SSC Guru Points: 323518 More actions · Answer 9

Newbie36037 (9/21/2015)
Cheers Lowell,
With a bit of tweaking that gave me what I needed, and saved me hours of frustration

what did you have to tweak? your answer might save someone else in the future.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Newbie36037 SSChasing Mays Points: 654 More actions · Answer 10

Made a couple of changes to the directory locations, added a source & target directory and converted to VB code as I'm using 2005 and it doesn't like c#

Dim SourceDirectory As String = "C:\Data"

Dim TargetDirectory As String = "E:\CN"

Dim FileExtensionsToProcess As String = "*.pdf"

Dim FileCounter As Integer = 1

Dim FolderName As Integer = 1

For Each FileName As String In System.IO.Directory.GetFiles(SourceDirectory, FileExtensionsToProcess)

Dim FileOnly As String = System.IO.Path.GetFileName(FileName)

Try

If Not IO.Directory.Exists(IO.Path.Combine(TargetDirectory, FolderName.ToString())) Then

IO.Directory.CreateDirectory(IO.Path.Combine(TargetDirectory, FolderName.ToString()))

End If

IO.File.Copy(FileName, IO.Path.Combine(TargetDirectory, IO.Path.Combine(FolderName.ToString(), FileOnly)))

Catch

End Try

FileCounter += 1

If (FileCounter Mod 1000) = 0 Then

FolderName += 1

End If

Newbie36037 SSChasing Mays Points: 654 More actions · Answer 11

Newbie36037

SSChasing Mays

Points: 654

September 23, 2015 at 1:05 am

#1828666

xc