August 20, 2012 at 10:57 am
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
August 20, 2012 at 11:03 am
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
August 20, 2012 at 12:01 pm
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.
August 20, 2012 at 12:06 pm
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?
August 21, 2012 at 8:42 am
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.
August 21, 2012 at 9:04 am
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.
August 21, 2012 at 9:07 am
That sounds correct. Have you set up tasks in the container to enumerate through?
August 21, 2012 at 9:07 am
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
August 21, 2012 at 9:21 am
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.
August 21, 2012 at 9:45 am
Not sure on this one. It may be the path. I've only had success with fully qualified paths in SSIS.
August 21, 2012 at 12:49 pm
The process is now working. It turns out that I was applying the wrong variable for the destination in the File System Task nested in the Foreach Loop container.
The UNC paths are also working. Should anybody have a problem applying UNC paths, the following steps will resolve this issue:
1) Open Internet Explorer.
2) Select Tools > Internet Options.
3) Select the Security tab.
4) Select the Local Intranet icon at the top and then the Sites button.
5) Check the box next to "Include all network paths (UNCs)"
GSquared and kl25, thank you very much for your patience in stepping me through the process and resolving the issue.
August 21, 2012 at 12:50 pm
🙂
August 21, 2012 at 12:52 pm
Glad to hear that you got it working. Thanks for posting the end results and the UNC information.
August 22, 2012 at 7:28 am
You're very welcome. Glad we could help.
- 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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply