Copy and Rename Files based on Query Results

Question

Copy and Rename Files based on Query Results

waxb18

Ten Centuries

Points: 1060
More actions
May 29, 2014 at 3:48 am

#293827

Afternoon Everyone,
Im caught in a bit of a sticky SSIS problem.
So the background.
We have a set of contracts set in a single location...say C:\Source Documents
and we want to move an rename them to another location...say C:\Destination Documents
Thing is...i only need to move those files based on a set of query result, and then rename them based on the job code the query returns.
E.G.
Job J12345 is with customer 7001
So move file where name contains '7001' from the source to the destination folder
then rename to have the Job number as the prefix of the document name...
E.G. 7001 Contract --> J12345 - 7001 Contract
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
So now the SSIS bits.
What i have been experimenting with is using an EXECUTE SQL TASK to feed the information required to 2 File System Tasks (1 for the copy and 1 for the rename)
so the issues i am having are 2...
1. I cannot seem to get the SQL task to produce outputs which i can then use in the File System Tasks
2. I cannot iterate through the query to repeat the process until there are no more results in the query. I have read briefly about Loop For Containers but i will need some further research before decieding to go down this route.
Your input is much appreciated

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

How is your scripting?

If you put your results into an object variable, you can use a single script task to iterate around the results and do all the file manipulation you need.

Otherwise, you may be able to use the built-in components, but the solution will be a little cumbersome, IMO.

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

aaron.reese SSChampion Points: 13498 More actions · Answer 2

Run your SQL query in an SQL task and store the results to an object variable.

Now create a ForEach loop that takes the object variable as its enumerator and map the fields of each record to variables.

Within the ForEach loop you can run your copy routine and have the source and destination file names (and locations) derived from an expression that contains the variables you have set as part of the For Each loop.

waxb18 Ten Centuries Points: 1060 More actions · Answer 3

Thanks Aaron, great piece of advice

So what i have done now is to have a SQL task Feeding into a ForEach Loop which will then move the file from source to destination and then rename the file.

I have also created 5 variables. 2 of the FilePathSource and FilepathDestination are expression based and will inculde the Project Code and OPCO (Customer) in the expression result to distinguish the file to be moved and the name to rename.

What i am having issues with at this point is extracting the project code and customer from the query (now stored as a dataset variable).

waxb18 Ten Centuries Points: 1060 More actions · Answer 4

Still no luck with this gents.

I have changed the query so that the file path is returned in the SQL statement rather than using the variables in SSIS and then attempt to return these variables as file source and destination. What i am left with is an error stating

"Validation error. Move File File System Task: Variable "Source_String" is used as a source or destination and is empty"

So... i did a quick test using the send mail task, putting the 2 variables in the message body. and hey presto...it sends me an email with the full filepath as i want.

Im struggling to understand why the File System Task fails to recognise the variables within the loop but the Send Mail Task has no such issues.

Can some please advise me on where to go from here?

Many Thanks

waxb18 Ten Centuries Points: 1060 More actions · Answer 5

So ladies and gents....

I found the cause of the validation error :w00t:

Within the File System Task Properties there is a section named "Execution" under which there is an option to "DelayValidation".

I set this to True

Debugged the package

and BINGO...COME THRU YOUR window...STAR LIKE RINGO!!!

thanks for all the guidance guys again you were awesome...

i can go home a happy guy...