SSIS Dynamic File Name

Question

SSIS Dynamic File Name

niffer76

SSC-Addicted

Points: 452
More actions
July 21, 2010 at 11:42 am

#146307

Hello,
I am trying to set up a SSIS package that will automate a weekly transfer of files from one path to another. I have the File Connection Strings dynamically programmed, but part of the path is variable. For example, I may have the following files this week:
File 1: File1Type_123.txt
File 2: File2Type__456.txt
File 3: File3Type__789.txt
I will always have 3 files (3 different file types), but the numbering on them is different every week.
For example, next week the files could be:
File 1: File1Type_321.txt
File 2: File2Type__654.txt
File 3: File3Type__987.txt
I can get the package to work if I program the number (ex '123') as a variable and actually put '123' stored as the variable, but I am stumped as to how to get the variable to dynamically change and include the actual value of the path.

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

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

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 1

use configuration file, which will read dynamic values, you can modify any time.

or use one table to store these two values and retrieve into variables using execute sql task code, if you are not aware of configuration files.

After reading values from above write expression, in the connectionString property of the flat files.

depends on requirement....enjoy

niffer76 SSC-Addicted Points: 452 More actions · Answer 2

Thank you for your quick response. I think I am still stumped, though. The 3 digit number will vary every week and is completely random so I will not be able to store it anywhere. Does the configuration file work for this?

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 3

No, in such cases you cant use config files,

Let us know, how you are getting random number, who generates for you? or you are the one who generates it?

you can write some SQL / .net code to generate that number and store into variable, use this varible in exressions...

niffer76 SSC-Addicted Points: 452 More actions · Answer 4

The file comes from a third party. They use this "trailer" for some unknown filing purpose. Aside from storing every possible combination of 3 digit numbers in the database, is there a way to just assign the variable on the fly when the package is run? What I am looking to do it to have the package just grab the name of the file and use it whenever needed throughout the package. Is this possible?

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 5

yes, you can use script task to read the file name / ( for each task), and take last 3 digits of the file name (not extension 🙂 ), then Drive in....

Cheers

philip.coe SSC Journeyman Points: 79 More actions · Answer 6

Have you tried wrapping it inside a ForEach Loop?

You can just map the filename to a variable.

niffer76 SSC-Addicted Points: 452 More actions · Answer 7

Actually, I found another blog that suggests that and I am trying to implement it. (http://www.sqlservercentral.com/Forums/Topic906117-148-1.aspx) Thank you very much for the suggestion. I think I am actually moving in the right direction now.

niffer76 SSC-Addicted Points: 452 More actions · Answer 8

I am still struggling with this a bit. I have set up the ForEach loop container to pick up the files based on the file name using "*" for the random 3 digit number. That part seems to be ok. What I can't quite figure out is once I pick up the file, I need to PGP encrypt it and then send it out via FTP. I have gotten this to work in the past for files without this random string of digits using CozyRoc's OpenPGP task and a connection string expression for the files. However, the connection string expression doesn't seem to work for a variable 3 digit random number. Or at least I haven't figured that out yet. Any ideas?

dg227 SSCrazy Points: 2017 More actions · Answer 9

I'm not familiar with using Cozy sw specifically, but in your ForEachLoop (FEL) container, you end up assigning your "wildcarded" file name into some variable (like, CurrentFlie, for example), with which you use in the FEL for your processing. You should be able to then use that assigned variable (CurrentFile) while in the FEL in your Cozy task(s) for encrypting and/or FTP'ing.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 10

niffer76 (7/23/2010)
I am still struggling with this a bit. I have set up the ForEach loop container to pick up the files based on the file name using "*" for the random 3 digit number. That part seems to be ok. What I can't quite figure out is once I pick up the file, I need to PGP encrypt it and then send it out via FTP. I have gotten this to work in the past for files without this random string of digits using CozyRoc's OpenPGP task and a connection string expression for the files. However, the connection string expression doesn't seem to work for a variable 3 digit random number. Or at least I haven't figured that out yet. Any ideas?

What expression did you try and what error message did you receive?

Presumably, you are mapping the file name/path to a variable as part of the FEL and then you are using that?

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

philip.coe SSC Journeyman Points: 79 More actions · Answer 11

CozyRoc PGPtask has a few options.

Once you have mapped the filename variable, create a new variable that adds the .gpg/.pgp file extention using expressions.

If you are using a pgp key, put this file path of the keyring into a variable (name it something like PGP_Key)

On the PGP task set the settings (is filename variable from memory) to true, then select the variable.

Same for the key file (then select the specific key from the keyring) and destination filename.

jayraj0071 SSC Journeyman Points: 86 More actions · Answer 12

Can you explain the requirement clearly. I am little confused. You want to move files as they come in...or what exactly you are looking for!!!

niffer76 SSC-Addicted Points: 452 More actions · Answer 13

I was finally able to get this working!!!! :w00t:

What I needed to do was to be able to was to unzip a folder, pick up a files in it (variable file names) and then transfer them other locations. What I had trouble getting figured out was that I needed to set a variable and add that in to the connection strings for the files.

Also, one of the files also needed to be PGP encrypted. I actually ended up removing the CozyRoc OpenPGP task from my package because you have to purchase a license from them in order to have it execute the job via SQL Server Agent. I ended up using Gnu GPG to PGP the file.

Thanks everyone for your help!