sql server 2008 - Creating an Expression for an Object Variable? -

i creating ssis package , first step list of files stored in file , store information in object variable. pass variable task tries open list of files. however, open file task fails if there no files open because folder empty.

i relatively new using ssis , curious on how rewrite expression checks see if object variable empty , if empty not allow open file task run.

you don't need use object variable loop through files in folder. can achieve using foreach loop container. following example demonstrate how can done. example created in ssis 2008 r2.

step-by-step process:

let's assume need loop through files in path c:\temp shown in screenshot #1.
on ssis package, create 3 variables namely folderpath, filepattern , filepath. refer screenshot #2. set folderpath variable folder loop through, here in case have chosen c:\temp. set filepattern variable pattern files should match, here loop through files have used *.*. if loop through excel 2010 files, can use *.xlsx. can accept 1 pattern. don't set value variable filepath because assigned value when foreach loop container loops through each file in folder.
on package's control flow tab, place foreach loop container , place script task within foreach loop container. in example, going loop through each file , display names , not more. refer screenshot #3.
configure foreach loop container shown in screenshots #4 , #5. on collection section, have configured expressions use variables folderpath , filepattern. on variable mappings section, have told container store file path value filepath variable.
inside script task, replace main() method code code given under script task code section. there nothing fancy in code. displays file path in message box.
screenshots #6 - #11 shows sample package execution , how each file in folder looped through. please note screenshot #11, script task marked green color stating package executed task.
now, let's delete files in folder c:\temp shown in screenshot #12.
if execute package now, tasks within foreach loop container not executed because there no files loop through , folder empty. refer screenshot #13. please note script task marked white color stating package didn't execute script task , skipped section.
this simple example. can whole lot more display names of files. can have other tasks within foreach loop container , pass filepath variable process files.

hope helps.

script task code:

c# code can used in ssis 2008 , above.

public void main() {     variables varcollection = null;      dts.variabledispenser.lockforwrite("user::filepath");     dts.variabledispenser.getvariables(ref varcollection);      messagebox.show(varcollection["user::filepath"].value.tostring(), "file path");      dts.taskresult = (int)scriptresults.success; }

screenshot #1: