Hello,

I have an SSIS package/job that spins through a directory of files (a
Foreach loop, which has new files daily) and for each file does a Data
Flow task (call it "Step A") that inserts data to a database. I am
trying to add a condition within the Foreach (daily file) loop that
bypasses that "Step A" Data Flow task -- when the daily file has
previously been processed -- but bypass only for that file in the loop.
This is necessary because some days the daily file directory is not
completely refreshed with all new files.

For my bypass checking, I have created a task to insert the name of the
file in an SQL database when it has been processed for the "Step A"
Data Flow task. But I have struggled with adding SQL tasks within the
Foreach loop as well as adding another Data Flow task - but cannot
figure out how to get the bypass to work, ie, checking for existence of
the filename within my bypass check database and either bypassing or
proceeding with Step A.

Any suggestions are much appreciated.

Thank you,

Greg

Hello artzoop (AT) hotmail (DOT) com,


So after the loop iterates you have the name of the file in a variable.
You can now add an ExecuteSQL task which checks whether you have previously
loaded the file. This will return a value indicating this. Using expressions
on workflow from the ExecuteSQL task you can then decide whether to execute
the import stream.

Have a quick look here

The ExecuteSQL Task
(http://www.sqlis.com/default.aspx?58)


and this

Introduction to Expressions on Workflow
(http://www.sqlis.com/default.aspx?306)



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thank you, Allan! I appreciate your help. The sqlis.com examples are
very helpful.

I got it working . . . . mostly, except to accommodate the database
entry not found, I have to increase the MaximumErrorCount on the
SQLtask for scenarios like this:
[Execute SQL Task] Error: An error occurred while assigning a value to
variable "filename_on_db": "Single Row result set is specified, but no
rows were returned.".

Any advice how to handle that better? ie, a no rows returned scenario
on an SQLtask? I'd rather not set an arbitrary high number of
MaximumErrorCount.

to sequence which bypasses processing the file); and the other
"@filename != @filename_on_db" (goes to sequence to process file). My
SQLtask uses this expression for its sql source:
"Select filename from DailyFeed_Filenames where filename = '" +
@[User:ailyFeedFilename] + "'"

Thank you,

Greg

I got it. I needed to modify my sql source statement to be sure of
always returning a result -- in either case of my daily filename
existing on the db or not. Here's the expression I used:


"if exists (select XMLfilename from DailyFeed_Filenames
where XMLfilename = '" + @[User:ailyFeedFilename] + "')
select 'true' as truefalse
else
select 'false' as truefalse"

Greg