SSIS File System Task--specifying files to move
-
I am completely comfortable with using the SSIS For Each Loop container with the File System Task tool to move all the files in one directory to an Archive directory. I am even OK on renaming the files and appending the date to them, etc... However, I'm trying to do something a little different and would love some advice.
I want to move only specific files from one directory to another. The file names are in the format 000000000.jpg and represent student photos where 000000000 is the student ID. I have a database table that contains CURRENT students with their 000000000 ID. I want to only move current students' pictures. I know I can create a user-defined variable and set it to the file name of the .jpg, but how do I then check to see if that variable is a current student? I can write a function to read the variable and return a true or false, but how do I connect that to the file system task? Any thoughts? Thank you!
-
Let me see if I understood the problem right.
You are trying to set a variable to true or false which will allow you to use in the file system task to conditionally move the files to archive folder. Assuming this as the problem statement.
I've used same kind of approach to set a global variable with the file name and then using the variable inside the file system task to move the file to archive folder. The variable needs to be passed to the file system task as a parameter (either readonly or as a readwrite param).
Let me know if this is what you are looking at.
Thanks, Srinivas.
-
I've had a similar situation. You'll need two variables. One for the file name and the other save the value when you compare to the database. I wound up putting the file name in a variable and used a SQL task passing in the variable which contained the file name and comparing that value to what I had in the database. On the SQL task, make sure you set the resultset property to single row. Under resultset assign the value to the variable.
The SP below is what I used.
create proc p_checkFile
@fileName
as
select count(*) as recordCount
from myTableThatContainsTheInfoINeed
where fileName = @fileName
You can then set the precedence contraint to expression and constraint and set the expression to @myCounterVariableName > 0
The last step is to use the file system task to make it do what you want. In my case, I moved the file from it's production location to an archive.
Hope this helps.
-
Yes, that is exactly what I want to do. I think I'm close, but not totally sure how to proceed. I have it set up to read the file name & use that as an input variable to a SQL function that determines if the ID is current. I set up an output (output or returnvalue???) variable @IsTrue and I'm setting that to the return value. Now I'm stuck. If it's a 1, I want to move it. If it's a 0, I want to leave it alone. How do I connect my @IsTrue value to the File System Task?
-
Now I'm stuck. If it's a 1, I want to move it. If it's a 0, I want to leave it alone. How do I connect my @IsTrue value to the File System Task?
You set that at the precedence constraint level. My default, if you connect the SQL task to the file system task, it gets set as success. If you double click on the green arrow connecting the two, we can change it from Constraint to Expression and Constraint. The expression would then look like @IsTrue == 0.
-
J.D.--Thank you so much. I'm so close now!!! Ok, I understand how to do the precedent constraint, but it doesn't like the way I'm trying to set the @IsTrue from the SQL task. So I have a ForEachLoop container and it sets my @User::PhotoName variable. Then, inside the ForEachLoop, I've got a SQL Task to call a function select dbo.TNU_CurrentFSA_check @people_Id=?. On the Parameter Mapping section, I have variable name @User::PhotoName as Direction:Input, Data Type: Varchar and Parameter @People_ID. I think I may be messing up with setting the output parameter. I have a variable @User::IsCurrent, Direction: Output, Data Type: Numeric (is that ok for Int??) and Parameter @IsCurrent. Then, my precedence constraint says @IsCurrent>0. When I try it, it doesn't like the SQL task.
It says: Executing the query "select dbo.TNU_CurrentFSA_check @people_Id=?" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
-
I got it to work! Thank you so much!
I didn't read your post closely enough. I was trying to set an output variable rather than using the "Results Set" area. This if fantastic and exactly what I needed! 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply