Moving files with SSIS.

Question

Moving files with SSIS.

William Gary Wright

Old Hand

Points: 328
More actions
July 29, 2010 at 1:48 pm

#148279

Need some help.
We have an application that displays images (.jpeg). The images are stored in a file share. The application builds the path to the images by concatenating the server name, share name and the image name. The image name is built by concatenating entries from 3 fields in the images table in the database. Example: \\Servername\sharename\123.12 Breaking down the image name 123 is the user name. .1 is the set of pictures taken and w is the view (side, front etc.). There are close to 90,000 pictures and I need to copy about 4,000 to our web server.
I have a SQL query that will do this for the 4,000 images I need. I have used SSIS a number of times but not for this. I have created a package that uses my query and exports the UNC path names to a text file. I assume I need some method (such as a for each loop) to extract each path name and copies the image to the web server. Here I am lost.
Thanks,
Bill
One more thing, the images will be displayed in a web application that is separate from the Windows application.

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

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

keith.gerritsen SSC Eights! Points: 973 More actions · Answer 1

It sounds like your destination directory is a constant?

You could use a data flow task to get the values in the text file into the package, but I'd find it easier myself to query SQL for the selected source file names (if you can get them in a view or a table on the server) in an Execute SQL task that maps the ("Full Result Set") result to an object variable.

Then, in a for loop container, as a For Each ADO enumerator, use that object variable as the source (and "rows in the first table" mode parameter), and a different, string variable mapped to index 0.

Use that string variable as the parameter in a File System Task. Use an expression to parse out the file name from the variable and combine it with the constant to get the destination.

William Gary Wright Old Hand Points: 328 More actions · Answer 2

I beleive I have the Execute SQL task configured properly (I don't get any errors when I run it separately) but I am not sure about the for eack loop and file task. I guess the first question is, which task should be inside the for each loop container SQL task or file system.

If I got this right, I need to use two variables one to get the file names from the SQL task and pass the values to a second varialbe to be used by the file system task. I get an error from the file system task telling the varibble has no value. If we can get this part working I will move onto the file system task questions.

William Gary Wright Old Hand Points: 328 More actions · Answer 3

I should have proof read that last post before I sent it. EACKKKKKKK

Phil Parkin SSC Guru Points: 246993 More actions · Answer 4

William Gary Wright (8/4/2010)
I should have proof read that last post before I sent it. EACKKKKKKK

Also 'beleive' and 'varibble' 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel