Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS loop through file names defined in database table and apply File System task to each Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 10:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:07 PM
Points: 24, Visits: 123
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
Posted Monday, August 20, 2012 11:03 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Posted Monday, August 20, 2012 12:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:07 PM
Points: 24, Visits: 123
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
Posted Monday, August 20, 2012 12:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:07 PM
Points: 24, Visits: 123
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
Posted Tuesday, August 21, 2012 8:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:25 AM
Points: 461, Visits: 1,686
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
Posted Tuesday, August 21, 2012 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:07 PM
Points: 24, Visits: 123
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
Posted Tuesday, August 21, 2012 9:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:25 AM
Points: 461, Visits: 1,686
That sounds correct. Have you set up tasks in the container to enumerate through?
Post #1347855
Posted Tuesday, August 21, 2012 9:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Posted Tuesday, August 21, 2012 9:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:07 PM
Points: 24, Visits: 123
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
Posted Tuesday, August 21, 2012 9:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:25 AM
Points: 461, Visits: 1,686
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 »

Add to briefcase 12»»

Permissions Expand / Collapse