SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57511 Visits: 9730
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
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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.
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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?
kl25
kl25
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1875
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.
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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:BigGrinest_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.
kl25
kl25
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1875
That sounds correct. Have you set up tasks in the container to enumerate through?
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57511 Visits: 9730
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
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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.
kl25
kl25
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1875
Not sure on this one. It may be the path. I've only had success with fully qualified paths in SSIS.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search