How to check the existence of multiple files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

How to check the existence of multiple files

Author

Topic

freefreefree
Starting Member

5 Posts

Posted - 2010-10-19 : 14:03:48

Hi,

I am using SSIS 2008 and want to build a package which performs the following task. It should check for the existence of all the .cms files in the destination folder and if any .cms file exists it should send an email to the DBA and if not it should proceed with the next task in the packages.

Any suggestions please

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:20:27

you need to use for each loop with file enumerator and inside that add a script task which keeps on incrementing a variable added to package. then after exiting loop check for value of variable in constraint and expression option and then add a send mail task if count > 0.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freefreefree
Starting Member

5 Posts

Posted - 2010-10-19 : 14:48:24

Hi,

I followed what you said but getting the following error. I might have made a mistake in declaring variables.

Error: ForEach Variable Mapping number 1 to variable "User::FileExists" cannot be applied.

Error: The type of the value being assigned to variable "User::FileExists" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Any suggestions?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:56:47

what is the type you selected for variable? it should be int.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freefreefree
Starting Member

5 Posts

Posted - 2010-10-19 : 15:11:13

Datatype of my Variable is Byte

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 15:12:25

why? you want to store file count isnt it? so it should be int

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freefreefree
Starting Member

5 Posts

Posted - 2010-10-19 : 15:24:33

I tried execute the package by changing the Datatype to Int32. Still the same error.

May be I will post the step by step procedure I followed in my next reply. I guess you can then assist me with the mistake I am making here

Thanks,

freefreefree
Starting Member

5 Posts

Posted - 2010-10-19 : 15:32:42

1. Dragged Foreach Loop Container in the control flow
2. in Foreach loop editor, under collection, I chose the destination folder path
3. Under files: I types in *.cms -- as I want to check all the .cms file extension files exists or not
4. Under retreive file name: Fully Qualified
5. Variable Mappings: I declared a variable called "FileExists" with Index value set to 0
6.dragged a script task in to the Foreach loop container
7.under the script task editor, I chose "FileExists" variable for ReadOnlyVariables
8. Connected Foreach Loop Container with the Send Mail Task and configured the task

I guess, I messed up in steps 5,6 & 7

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 13:01:52

quote:
Originally posted by freefreefree

1. Dragged Foreach Loop Container in the control flow
2. in Foreach loop editor, under collection, I chose the destination folder path
3. Under files: I types in *.cms -- as I want to check all the .cms file extension files exists or not
4. Under retreive file name: Fully Qualified
5. Variable Mappings: I declared a variable called "FileExists" with Index value set to 0
6.dragged a script task in to the Foreach loop container
7.under the script task editor, I chose "FileExists" variable for ReadOnlyVariables
8. Connected Foreach Loop Container with the Send Mail Task and configured the task

I guess, I messed up in steps 5,6 & 7

Thanks,

in 7 you should add the variable as ReadWrite
and in code for script task you should include statement like

Dts.Variables("yourvariable").Value=Dts.Variables("yourvariable").Value + 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources