March 27, 2012 at 11:04 am
Scenario:
Invoice Pdf files resides in folder eg: E:\Filesourse
such as 1234.pdf and 2345.pdf
Meta data txt file also resides in same folder:
The values in the meta data file will populate a sql tabel and used by a web app to find a specific invoice pdf by searching fields in a sql server table with values such as Invoice Number, Customer Number etc.
Also in the metadata file and SQL Table, the DocumentFileName and the final destination path (DocumentFilePath) after pdf files have been moved from File source location (as in E:\Filesourse).
The meta data text file will populate a sql server table with values like:
DocumentTypeDocumentFilePath DocumentFileNameDocumentDateDocumentAmt
The goal is to use a file system task in a ForEachLoop Container that loops through the Filesourse folder and move a specific pdf file named in the the sql table
(caled DocumentIndex).
Example: Move
1234.pdf from E:\Filesourse to Destination Path such as \\Server\Share\Ort\Inv\2012\03\
3456.pdf from E:\Filesourse to Destination Path such as \\Server\Share\Ort\Inv\2012\04\
(DocumentFileName) into the folder designated by the DocumentFilePath (Destination Folder).
What I cannot figure out is how to set a FileToMove variable and a DestinationFolder variable to the DocumentFileName and DocumentFilePath from the DocumentIndex Sql table.
March 30, 2012 at 4:50 am
Have you tried using the foreach ado enumerator? That should solve your problem I think.
March 30, 2012 at 5:33 am
What I cannot figure out is how to set a FileToMove variable and a DestinationFolder variable to the DocumentFileName and DocumentFilePath from the DocumentIndex Sql table.
Create two new variables in your SSIS package, scoped at package level, to receive the values.
Use an Execute SQL task early in your package to select Col1, Col2 from DocumentIndex (assumes only one row of data is returned).
Configure the Execute SQL task to write the results of the query to the SSIS variables you have created.
Use Expressions within your FST to set the properties you want to be dynamic, based on the values contained in the variables.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply