FileSpec Expression Dynamic

Question

FileSpec Expression Dynamic

Mark787

SSC Veteran

Points: 281
More actions
June 18, 2018 at 9:39 am

#363202

Hi all,
I have files in a folder that I need to process together.
For example
340154.6000339193.180518.d.A234.dfr_resp.pgp
340154.6000c.180518.d.B410.dfr_resp.pgp
340158.6000339201.180518.d.A234.dfr_resp.pgp
340158.6000339201.180518.d.B410.dfr_resp.pgp
Substring of files italicized are to be processed together.
My Variables
Package.
Company ID File Loop gets two file names from ADO.recordset returns 339201 and 339201
I want to use these values to specify which files to process within directory folder.
Expression in Foreach File in Folder
When I run this I get file not found.
Any idea's appreciated. I'm very new to SSIS...

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

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

When you use a ForEach container to loop through files in a folder, that container is expecting you to provide a "filespec". A filespec is typically looks like this:
C:\Chase\Files\Daily\*.pgp

I'm not sure what you are doing by using a substring of a package variable that looks for the next 6 characters starting at position 33. That would end up looking for files with exactly that name and no other characters, which doesn't appear to be what you are looking to accomplish. Can you explain how the character string 339201 has any relationship to the displayed list of filenames you have? I can't quite make sense of the following portion of your post:

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mark787 SSC Veteran Points: 281 More actions · Answer 2

The files contained in the directory I'm searching are names as follows.
four files
340154.6000339193.180518.d.A234.dfr_resp.pgp
340154.6000339193.180578.d.A104.dfr_resp.pgp
340158.6000339201.180518.d.A234.dfr_resp.pgp
340158.6000339201.180518.d.B410.dfr_resp.pgp

The numbers 333193 and 339201 are contained in the file names.
I need to process the files with same #'s together.

For each loop 3331939(AD).recordset)
Use 333193 string to get files in folder
process files

sgmunson SSC Guru Points: 110639 More actions · Answer 3

Mark787 - Monday, June 18, 2018 11:07 AM

The files contained in the directory I'm searching are names as follows.
four files
340154.6000339193.180518.d.A234.dfr_resp.pgp
340154.6000339193.180578.d.A104.dfr_resp.pgp
340158.6000339201.180518.d.A234.dfr_resp.pgp
340158.6000339201.180518.d.B410.dfr_resp.pgp

The numbers 333193 and 339201 are contained in the file names.
I need to process the files with same #'s together.

For each loop 3331939(AD).recordset)
Use 333193 string to get files in folder
process files

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mark787 SSC Veteran Points: 281 More actions · Answer 4

Steve thank for your help. Still not sure how to get this working.
"*" & SUBSTRING(@[User::varFileName], 33, 6) & ".*.pgp"

The & operators were not compiling.
( The data type "DT_WSTR" cannot be used with binary operator "&". The type of one or both of the operands is not supported for the operation.)
Had to use + instead.

Also, the variable varCompanyFileID holds the values of numbers 333193 and 339201
These values are in outer loop
How do I pass that into the expression to search for that string with in the folder path and file name?

Folder:
C:\Chase\Files\Daily
File
340154.6000339193.180518.d.A234.dfr_resp.pgp

Pseudo code search through all files in folder and process those that have varCompanyFileID in the name of file.

Mark787 SSC Veteran Points: 281 More actions · Answer 5

Steve, do I need to use an Expression Task to evaluate files as they are found.
I would have to search the file Folder twice.

1st pass 339193
   loop through folder
     evaluate current file name
         340154.6000339193.180518.d.A234.dfr_resp.pgp
         339193 match
               process file...
         340158.6000339201.180518.d.A234.dfr_resp.pgp
          no match skip

2nd pass 339201

Not sure how to set this up though

sgmunson SSC Guru Points: 110639 More actions · Answer 6

Mark787 - Tuesday, June 19, 2018 4:43 AM
Steve thank for your help. Still not sure how to get this working.
"*" & SUBSTRING(@[User::varFileName], 33, 6) & ".*.pgp"
The & operators were not compiling.
( The data type "DT_WSTR" cannot be used with binary operator "&". The type of one or both of the operands is not supported for the operation.)
Had to use + instead.
Also, the variable varCompanyFileID holds the values of numbers 333193 and 339201
These values are in outer loop
How do I pass that into the expression to search for that string with in the folder path and file name?
Folder:
C:\Chase\Files\Daily
File
340154.6000339193.180518.d.A234.dfr_resp.pgp
Pseudo code search through all files in folder and process those that have varCompanyFileID in the name of file.

Is your source of the user variable an ADO recordset ? That's a scenario where you would need a ForEach container within a ForEach container. The outer one would have the ADO recordset as the enumerator, and each row would have to update that user variable, and then you have a chance at it working.

Also, I suspect I have the wrong string concatenation operator. Must have had Excel on the brain the other day. Check the operator list in SSIS to see what it uses for String concatenation. Maybe it's +, or maybe there's a function for it. I can't recall off the top of my head.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)