Hi,

I have a requirement to find the file name based on some string values. A file contains all the strings in a column.

Eg: Column1  Column2

       abcd       aaa@a.com

       asdf        bbb@b.com

A folder contains files with the names as below.

123abcdjohn.pdf

23456asdfmike.pdf

I have load all the column1 and column2 values into record set and loop through it, and send the file as attachment to the corresponding email addresses by comparing the column1 value to be matched in file name (anywhere).. looks like I have to use wild cards to match the filename,.. I am not sure how to get the file name using a sub string of file name,.. Would be great if you can help me.

Thanks.

this is easy

1. Add a execute sql task with query to get above columns and then store it in a object variable by setting Resultset option as Full resultset

2. Use a ForEach loop with ADO recordset and map to object variable created above

3. Inside loop have a script task inside which check for existence of files with names having pattern of Column1  within them

you can use logic as below for this

http://www.bidn.com/blogs/DevinKnight/ssis/76/does-file-exist-check-in-ssis

for pattern matching you can use IndexOf function after passing column1 value through variable used inside the loop.

4. Use a conditional precedence constraint to link to a sent mail task and use email variable to set the To property to sent notification mail. Pass file path also as value for file attachment property

Hi Visakh,

Many thanks for your reply. As my source is File, I have load all data into Record Set and loop through for each ADO Enumerator and in the script task, I am trying to match the pattern using below code.

In the For each loop, variable mapping, varColumn1 - 0 and varColumn2 - 1

varDir - c:\

varPattern - "*"+@[User::varColumn1]+"*.csv"

Inseide Script task:

Dim arrFile_Infos() As FileInfo
            Dim strFileName As String
            Dim strFilePath As String
            Dim iCount As Integer = 0

            'Map path of directory to interrogate
            strFileName = Dts.Variables("varPattern").Value.ToString()
            strFilePath = Dts.Variables("varDir").Value.ToString()

           
            Dim Dir_Info As New DirectoryInfo(strFilePath)

            'Load the files info array
            arrFile_Infos = Dir_Info.GetFiles(strFilePath, strFileName)

'Returns the number of files that matches pattern
            iCount = arrFile_Infos.GetLength(0)

     if iCount > 0 then
       Dts.variables("varFileExists") = True
     else
       Dts.variables("varFileExists") = False
     End if

But I am getting exception with the error Error -13 Can not conversion from String "*abcd*.csv" to type 'integer' is not valid.

Please advise me.

Thanks,...