Get files based on SQL results
-
I have a sql task which queries a database and returns a result set similar to: \\fileshare\folder\filename
I then need to loop through all of these results, find the file on the filesyatem and then copy it to another location.
I am not sure how to pass the sql results to the file system task in order to achieve this. I thought there may be a table variable I could use and point the task to that but I can't see one. Maybe I need to use a for each loop to do this but have not done this before in SSIS.
-
You can't pass the result set directly into the file system task. Instead, pass it into a For-loop container and then set another variable with the value of the current file path. You can use the file system task within the For-loop container.
-
You can configure your SQL Task to return a Full Result Set on the General tab, then assign the result to an object variable on the Result Set tab.
To loop through the object variable, use a Foreach Loop. On the Collection tab, set the enumerator to Foreach ADO Enumerator, and set ADO object source variable to the object that was populated by the SQL Task.
On the Variable Mappings tab, assign one package variable to contain each column in the object variable. Set Index to zero to assign the first column returned to a variable, set it to 1 to assign the next, and so on.
Then access those variable(s) in a File System task from within the Foreach loop.
-
Thanks, that' sort of where I'm heading I think but am getting lost in a sea of variables!
I have split the file path into a variable "theSource" and am getting the filenames from the SQL table. I am outputting these results into a resultset variable of type object.
I need to (within the for each loop) concatonate theSource + filename for each rown in the resultset, pass this to an FTP task and have it upload to the remote server. I have the FTP connection set up I'm just trying to work out how to re-populate the variable for each rown in the results and pass this to the FTP task
-
Is there a way for me to clear the value of a variable after each iteration?
Clear varible
Populate variable
FTP task using variable
Loop
I can't see a way to set it to "" and I have a feeling it might be appended a value rather than setting a new value each time.
As an aside I seem to have disabled progress reporting somehow! When I go to the progress tab it tells me enable it via the SSIS menu. I'm using visual studio 2019 and don't see an SSIS tab not can I find this command anywhere on the menus/tabs/windows
-
You can use an Expression Task in the loop to concatenate theSource and the filename from the recordset object into another variable, which would be accessed by the FTP task.
-
I've cut out the complication by doing the concatenation in the SQL so now I am dealing with a single variable for the FTP task.
My sql outputs to a resultset called Attachs. The for each loop using a collection User:Attachs. The variable mapping is set to User:tempSource, the FTP task local path variable uses the local variable User::tempSource.
However now when I run it it errors and says "Error: 0xC002917C at FTP Task, FTP Task: The variable "User::tempSource" doesn't contain file path(s)."
The SQL runs fine, it parses fine in the SSIS designer and the filepaths do not contain invalid characters so now I am stuck as to why it is empty (according to the variables window)
-
Sorry for the wild goose chase (and spam!) I have worked it out.
The error alluded to a path in the database but no corrosponding file on the network so it could not FTP it. As the variable was showing blank I assumed the problem was programmatical and not physical! I can only guess that the process checked the existence of the file before populating the variable.
Now I just need to work out how to handle this - if file does not exist then move on to the next row
-
I think if a Foreach loop uses a File Enumerator, it will only populate the variable for files in the target folder, but since you are enumerating a recordset in an object, it populates the variable for every row in the loop, and doesn't know or care that the variable is a file path.
One thing that tripped me up when I was getting started with SSIS is how to examine variable values at runtime. The SSIS | Variables menu just shows the window where you create variables and assign them an initial value. To see the value of tempSource when the FTP Task is executed, set a breakpoint at the FTP Task's Preexecute event. When it's hit, check it's value in the Debug Locals Window:
You can use a script task to check for file existence and set a variable, then use expression constraints out of the script task to FTP or not to FTP.
-
Thanks, I'm a little rusty on the script task not having used it since the DTS days
Ok I have dragged a script task into my foreach look and set it ahead of the FTP task.
I'm now in the main method and basically need to say if the file exists at the path stored in the user::tempSource variable then move on to the FTP task else skip the task and move onto the next iteration.
Any ideas please?
-
Here is what I have so far. Just need to say if File.Exists then go to FTP else skip FTP and loop
public void Main()
{
// TODO: Add your code here
string fileExists;
fileExists = (string) Dts.Variables["tempSource"].Value;
if (File.Exists(fileExists))
{
}
Dts.TaskResult = (int)ScriptResults.Success;
} -
This might get you started. I changed this snippet on the fly after copying from real script, so it might not be perfect, but should give you the idea.
// You need this namespace
using System.IO;
// strCurrentFile_FQN was constructed in the script and is tested for existence.
// You can use your file variable instead
if (File.Exists(strCurrentFile_FQN)) {
\\set a package variable true
} else {
\\set a package variable false
}You can use expression constraints coming out of the script task to control flow, e.g:
Connect the true constraint to the FTP task, then create another constraint with @[User::MY_BOOLEAN] == false, and connect it to some point after the FTP task.
-
99% there. But now when I try to run the package (in visual studio) I get errors in the sscript. When I re-open the script all references to Dts are underlined in red asking me if I am missing a reference.
I have googled it and see suggestions to browse to microsoft.sqlserver.managedDTS and add it as a project reference but I don't see that dll listed on my client. The only reference I added was system.IO, other than that it is just how it was when I first created the script task. Even the out of the box code shows errors e.g. Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
This was also pre-written in the namespaces area but claims not to exist. using Microsoft.SqlServer.Dts.Runtime;
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
namespace ST_620cf59084384434889745b7a25228d8
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// TODO: Add your code here
string filePath;
filePath = (string)Dts.Variables["tempSource"].Value;
if (File.Exists(filePath))
{
Dts.Variables["FileExists"].Value = true;
}
else
{
Dts.Variables["FileExists"].Value = false;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
} -
Sorry, ignore that. Despite me not doing anything it has decided it does not want to error anymore, doesn't need me to include any additional DLLs and is quite happy with the ones it has! I can see I'm going to "enjoy" SSIS as much as I did DTS 🙂
Only puzzler now is why (when I run the package as a SQL agent task) it runs, works as intended but claims to error.
I go to the all executions report and it says it succeeded and there are zero errors
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply