Looping and File names

2 files are delivered encrypted to delivery_folder.

I have created a for-each loop to pick up all files in the folder (*.pgp) and copy them to an archive folder.

I then need to decrypt each file and give each file a specific name.

files come into delivery_folder named file_name02012012.txt.pgg and file_name102012012.txt.pgp

after the copy to the archive folder, i want to decrypt the files in the delivery_folder to file_name.txt and file_name1.txt

I was attempting to do the move AND the decrypt step within one ForEachLoop container. Not working...please advise.

I do that ( though not in a LOOP ) with my present employer.  Can you provide a few more pieces of information?

• What version of SQL Server are you running?  Is is 32 or 64 bit SQL? 
• What is the operating system on which you're running this process?
• Are you using only stock components in the SSIS package or do you have some 3rd party pieces as well?
• What is the error message that you're getting when the package fails?

Hope to hear from you,

Keith

Right now I am upgrading DTS pkgs to SSIS, I am running SSIS against a SQL Server 2000 backend. Going to SQL Server 2008 64 BIT.

No 3rd party software.

I apologize for an unclear explanation - I am new to SSIS.

In my ForEachLoop container I populate a variable filename.

Files are copied to Archive directory - successful.

DECRYTPION STEP variables:

sourcefile (populated with @User::filname)

outfile (when I normally decrypt a single file - this is a hard coded path    C:\outfile location\outflename.txt)

----------------------------

Issues are- each file needs to be decrpted with a new file name

filename_02012012.txt.pgp --> filename_02012012.txt --> filename.txt

filename1_02012012.txt.pgp --> filename_02012012.txt -->filenam.txt

No apologies needed - MANY of us have been where you are.  I just wanted to get as much info as possible to try to help out.

Here's a blog I posted that helps with the step-by-step on copying / moving files.  What I put together is in a sequence container, but the same principles should apply within a loop container.  http://www.bidn.com/blogs/KeithHyer/bidn-blog/2465/copy-and-rename-a-file-in-ssis-using-the-file-system-task 

Take a look at that to see if it helps.  I'll see if I can put together a walk-through like that one this afternoon to help out.

Keith Hyer

Excellent Example.  I got it to work - maybe with some unecessary muscle - but still learning.

For the outfile (and this is where it is a little clugie) I appended the word "decrypted" to the filename 

I created a script task that has the ReadOnlyVariable set to the ForEachLoop variable filename.

Then I passed in the variable file name + decrypted and then used Rename - (using VB 2008).

Glad you got it working!

I published a blog entry with what I came up with to handle this.  Take a look and let me know what you think if you have time.

http://www.bidn.com/blogs/KeithHyer/bidn-blog/2511/a-file-manipulation-step-by-step-geared-toward-pgp-decryption

Keith

Awesome - Thank you.  I will def try that way..it seems more flexible.  In my instance...I am certain(to a point) of the file names.  Your way is much more elegant.

Thanks for the help. V

Glad you like it - if you run into anything - let me know, I'm happy to lend a hand ( as time allows ).

Keith

Hi Keith,

Its me again with a new issue, I'll need help in getting  10 .txtfiles from FTP to my local drive, the folder name is the date <03122012> this changes dynamically everyday, from your earlier post i was able to get the folder name figured out and the local drive and remote drive, but im not sure how do i get the <03122012> folder open and get all the txt files to my local machine. or if i can move the complete folder to my local shared drive that would be pretty awesome . Can you guide me thru with any screenshots please..

Thank you very much!!

Hi Keith,

Its me again with a new issue, I'll need help in getting  10 .txtfiles from FTP to my local drive, the folder name is the date <03122012> this changes dynamically everyday, from your earlier post i was able to get the folder name figured out and the local drive and remote drive, but im not sure how do i get the <03122012> folder open and get all the txt files to my local machine. or if i can move the complete folder to my local shared drive that would be pretty awesome . Can you guide me thru with any screenshots please..

Thank you very much!!