SSIS loop through file names defined in database table and apply File System task to each

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SSIS loop through file names defined in...

14 posts, Page 1 of 2

1 2 »»

SSIS loop through file names defined in database table and apply File System task to each

Rate Topic

Display Mode

Topic Options

Author

Message

grantbanjo

Posted Monday, August 20, 2012 10:57 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:50 PM
Points: 18, Visits: 75

Hello,
My objective using SSIS is to loop through a SQL Server 2008 database table I created called Files in a field called Filepath to capture the list of files and then loop through and copy each to a destination directory. The source files all exist in different folders and are defined in the "Files" table according to their UNC path (i.e. "\\xxxx.com\WWWRoot\z\projects\u\Shared Documents\test.mpp").

I have already set up an Execute SQL Task using the following SQL Statement to capture the list of filepaths.

SELECT FILEPATH
FROM dbo.Files
WHERE Active = 1

Can somebody provide the step-by-step process from this point for apply the result from the above SQL to the means of copying each file over to a destination?

Thanks

Post #1347340

GSquared

Posted Monday, August 20, 2012 11:03 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Are you storing the results of the query in a variable as a recordset?

If so, then create a For Each Next loop, set the source for it as the recordset variable, and use that to assign the path value to a string variable. Then use the string variable as the source connection string for your File Action step.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1347347

grantbanjo

Posted Monday, August 20, 2012 12:01 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:50 PM
Points: 18, Visits: 75

Yes and this is where I probably do not have things set correctly from the beginning.

I have created an Object Data Type variable for the package calleld vFileName with a value of "System.Object".

Under Parameter Mapping in the Execute SQL Task Editor I have added User::vFileName with a direction of Input, Data Type of VARCHAR Parameter Name of 0 and Parameter size of 1000.

I do not know if this is set up correctly and how to apply it next in the Foreach loop container.

Post #1347374

grantbanjo

Posted Monday, August 20, 2012 12:06 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:50 PM
Points: 18, Visits: 75

I neglected to mention that I am using an OLEDB connector for the SQL. Should I use ADO.NET instead in order to apply the source container in the Foreach loop?

Post #1347380

kl25

Posted Tuesday, August 21, 2012 8:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:17 PM
Points: 240, Visits: 1,095

I have created an Object Data Type variable for the package calleld vFileName with a value of "System.Object".

Correct first step. I'm assuming you're accomplishing this for a SQL Task.

Under Parameter Mapping in the Execute SQL Task Editor I have added User::vFileName with a direction of Input, Data Type of VARCHAR Parameter Name of 0 and Parameter size of 1000.

You're looping through the result set. You'll want to place the results in an object. Go to the 'Result Set' section. Map the 'Result Name' of 0 to the 'Variable Name' of User::vFileName.

I do not know if this is set up correctly and how to apply it next in the Foreach loop container.

Now that you have the result set in an object, you can set up the For/Each loop. For the 'Collection' select the 'Foreach ADO Enumerator'. Set the 'ADO object source variable' to your variable object. For your 'Variable Mapping' use whatever variable will be holding the value for each loop and set the index to 0.

Post #1347825

grantbanjo

Posted Tuesday, August 21, 2012 9:04 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:50 PM
Points: 18, Visits: 75

Okay, I applied those steps and within the Foreach Loop container set the Enumerator to 'Foreach ADO Enumerator', the ADO object source variable to 'user::vFileName' (the variable object being populated in the preceeding SQL Task by the SQL query), and then selected the radio button for 'Rows in all teh tables (ADO.NET dataaset only). Variable mappings under the Foreach Loop lists the string variable 'User::FileName' with an index of 0.

When I run just the SQL Task and the Foreach loop container (not containing any tasks), they both turn green.

I next tried adding the File System Task Editor with the following settings:
IsDestinationPathVariable = True
DestinationVariable = User::Dest_Dir (which contains the value 'H:\Working')
OverwriteDestination = True
Name = File System Task
Description = File System Task
Operation = Copy file
IsSourcePathVariable = True
SourceVariable = User::FileName

Now when I run the package it fails with the following error: "The network path was not found."

As I mentioned previously, the path of each file is applying a UNC format. I am aware that there is an issue with UNC path formats in SSIS, but I have copied these files individually via SSIS using this format in the past. The only difference is that, instead of hardcoding the paths directly in a File System task, I am using them dynamically stored in a database table. These files are coming from a SharePoint site to which I have full permission.

Post #1347852

kl25

Posted Tuesday, August 21, 2012 9:07 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:17 PM
Points: 240, Visits: 1,095

That sounds correct. Have you set up tasks in the container to enumerate through?

Post #1347855

GSquared

Posted Tuesday, August 21, 2012 9:07 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

You're on the right path. Now you just need to put your file system task (and any related tasks) inside the loop, and set the source connection to use your vFileName variable, and your destination to use whatever is appropriate for that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1347857

grantbanjo

Posted Tuesday, August 21, 2012 9:21 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:50 PM
Points: 18, Visits: 75

Yes, I set up the File System Task within the Foreach loop container as discussed in my previous message. I edited my response with this additional detail while you were responding. Please verify that I am applying the correct settings, as I am now receiving, "The netowrk path was not found." error message.

Post #1347869

kl25

Posted Tuesday, August 21, 2012 9:45 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:17 PM
Points: 240, Visits: 1,095

Not sure on this one. It may be the path. I've only had success with fully qualified paths in SSIS.

Post #1347889

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions