File System Task with variables

Question

Post reply

File System Task with variables

MartinIsti

Mr or Mrs. 500

Points: 540
More actions
November 15, 2007 at 9:28 am

#183862

Dear SSIS-fellows!
I have looked through 3 ebooks and dozens of webpages but still can't do the following (probably due to my unexperiency):
There are more than one zip files in a folder (e.g. E:\DATA)
I want them to be renamed to default.zip, then unzip and afterwards move them into E:\DATA\Archive with the name of Archivexx (xx should be an index - or the date + an index).
At a time only 1 default.zip would exist.
As far as I know it would need a ForEach container, and within some FileSystemTasks and an ExecuteProcessTask (for unzip). I thought of using variables in the FileSystemTasks to avoid many-many connections.
But for some reason I cannot do that :crying:
Please if anyone has an idea for it share it with me! I would think that this should not be that difficult as it seems for me now.
Thanks in advance,
MartinIsti

Viewing 8 posts - 1 through 7 (of 7 total)

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

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 1

What specifically is preventing your package from executing?

Dr. John Tunnicliffe Right there with Babe Points: 782 More actions · Answer 2

I am not sure why you need to rename the zip to default.zip. Personally I would use the ForEach container to enumerate the list of files in a directory and have the ForEach container set some variables containing the file paths.

Then drop your next component in the ForEach container which will use the content of the variables to process the files.

MartinIsti Mr or Mrs. 500 Points: 540 More actions · Answer 3

Thanks Matt and John,

I wasn't too clever when I tried this task last time. Now I've reached a little further. It was really no use of renaming the zip to default.zip so I can do it with a foreach container.

Now I am stuck when I want to move the original zip to an archive folder. The file system task is also in the container but it doesn't use the variable from the container (the *.zip) as source variable.

The execute process task (with an unzip application) can do it without problem.

Where is the error? How can it be solved?

Dr. John Tunnicliffe Right there with Babe Points: 782 More actions · Answer 4

The file system task will use the variables from the container if you set the IsSourcePathVariable=True

Also, try using Expressions in your Varibles if you need to create a file path.

MartinIsti Mr or Mrs. 500 Points: 540 More actions · Answer 5

Thanks, John!

Since I wrote the reply I've found out that I used filenames where folders were required.

Now I am stuck again 🙂 but it's not that surprising

I have a foreach loop container with an unzip ExecuteProcessTask which works great (I get the argument for it from a variable from the container).

Then I want to rename the unzipped anything.mdb to default.mdb. I can't use joker characters (like *) in the variable expression so I inserted another foreach container to go through all the mdb files in the folder (although there is only one).

I took it out into a separate package (that 2nd container) because I cannot make it work: I configured it in the same way I did for the zip files but it doesn't rename anything.

My variables (I just name the ones from the separated task):

MI::MDB_files - variable mappings window in the foreachloop container (the collection is *.mdb)

in the rename task:

sourcevariable = SourceRename (which is an expression with value: path+ @[MI::mdb_files]

destinationvariable = def_mdb (MI::default_mdb, value: default.mdb)

Maybe I make it more difficult than it should be 🙁

I would be grateful even if you just put my case away and send or upload me a package that renames every mdb file in a directory into default.mdb. (it might sound stupid but if there is only 1 mdb that's okay and since this container is inside another the other default mdb files can be overwritten after using them in another package)

My email is martinisti@gmail.com

Thanks in advance!

MartinIsti

Dr. John Tunnicliffe Right there with Babe Points: 782 More actions · Answer 6

I think you are making it more complex than it needs to be. :crying:

I would create five variables in your foreach container. One takes the

SourceFolder (for your source folder path)

TargetFolder (for your archive folder path)

SourceZipFileName (your source zip file)

SourceUnZippedFileName (your MDB file name after unzipping)

TargetMDBFileName (the target MDB file name i.e. where you want to move it to).

So expressions would be

[User::SourceFolder] + [User::SourceZipFileName]

for the full path to the source file, and

[User::SourceFolder] + [User::SourceUnZippedFileName]

for the unzipped file, and

[User::TargetFolder] + [User::TargetMDBFileName]

for the target file.

This assumes you know or can predict/control the name of the unzipped file.

If you do not know the name of your SourceUnZippedFileName then I suggest you use a Script Task and System.IO as you have more control over the exact coding.

Remember that you need SourceFolder and TargetFolder to have a trailing \ otherwise you will cause an error!!

MartinIsti Mr or Mrs. 500 Points: 540 More actions · Answer 7

This assumes you know or can predict/control the name of the unzipped file.

That was the source of the whole misery 🙂

But I managed to do it with some scripts. It took a lot of time for me but now at least I know much more of how to write VB-scripts in SSIS and about variable-handling in script tasks.

Thanks for your efforts anyway!

MartinIsti