Hi, I have an SSIS variable of type object. I created a for each loop on a share drive and for each iteration i have a script task that is checking a file date and based on the day, i want to store the file name in the object variable. So if the directory has 100 files and 40 of them match the date criteria, i want to store the 40 file names in the object variable. Is this possible in a script task using vb? thanks scott

It is possible - but there may be a better solution. What are you going to do with the 40 file names? Talk to me now on

Todd, Thanks for the reply. Once i store the 40 or so filenames in an object variable, i will then need to check each file name with another filename from a table in a database to see if it matches. If it does, then i will be converting the file to pdf. So in short, i need to iterate through a directory of about 1000 or so files, get only the files that have a modified date of today, then bounce those file names against file names that are stored in a table in a database to see if they have a match, then convert them to PDF. Sounds fun huh? I was hoping SSIS would help. I know i can use for each loop to iterate through the files in the share directory. I was thinking of then having a script task to check each file to see if date modified is today. IF so, store it in object variable. Then have another step to iterate the object variable and check it against the table in the database to see if there are matches. Then have a final script step to convert files to pdf. any help would be appreciated. thanks scott

I'd be tempted to do most of this in a script task Use Match Files and an array list to get your list of 40 files: Sub Main() Dim Test_Date As date = Date.Today Dim Archive_Files() As String = System.IO.Directory.GetFiles("FolderPathHere") Dim New_Files As New ArrayList For x As Integer = 0 To Archive_Files.Length - 1 If File.GetLastWriteTime(Archive_Files(x)) < Test_Date Then New_Files.Add(Archive_Files(x)) End If 'Get Table records into recordset then into a string seperated by | 'Do this so we only have to loop through the recordset once Dim con As New OleDb.OleDbConnection Dim cmd As New OleDb.OleDbCommand Dim dr As OleDb.OleDbDataReader con.ConnectionString = dbConnection Try con.Open() Catch ex As Exception End Try cmd.Connection = con cmd.CommandText = "SQL TO GET LIST OF NAMES FROM DATABASE" dr = cmd.ExecuteReader() If dr.HasRows = True Then While dr.Read strNames = strNames & dr.Item(0).value.tostring & "|' End While Else strNames = "" End If dr.Close() Next For Each Found_File As String In New_Files If instr(Found_file,strNames) > 0 Then 'Create PDF End If Next End Sub Rgds Geoff

OK - that's what I thought. There's no need to store this stuff in an object variable - just do it all in one loop. Use the Foreach Loop you have to iterate over the files. Use an Execute SQL Task to do your lookup. You'll have to use property expressions to craft your SQL based on the filename variable the loop is using Then use a third-party File Properties Task or a script task like this, or similar to Geoff's to read the file's time into an SSIS DateTime variable. (You'll need to specify the Filename variable in the script's ReadOnlyVariables property, and the Modified variable in the script's ReadWriteVariables property.) Attach an Execute Process Task (or whatever kind of task does your conversion) to the file properties (script or custom) task with a Precedence Constraint. Double-click on the precedence constraint to change it to an "Expression and Constraint", and set the expression to "YEAR(@Modified) == YEAR(GETDATE()) && MONTH(@Modified) == MONTH(@GETDATE()) && DAY(@Modified) == DAY(GETDATE())". Geoff - your solution would probably work. Personally, I don't like doing that all inside a Script. First, it's opaque to someone who knows SSIS but doesn't know code. (Yes, I know that's a small percentage of people... today. But it will (should) be the norm!) Second, you're using an unmanaged connection in the script - not a best practice in my book. Third - there's no logging inside scripts unless you manually add it. Fourth, you'll never benefit from version upgrades/patches to SSIS within a script. Of course, those are my opinions only... Talk to me now on

Thanks to both of you for the response. i will try to see which option works best. I may use parts of each solution if need be. thanks again!

Hi Todd - I do generally try to stay away from code where possible (although I must admit I'm from a bit of a coding background so it's always tempting) The code is actually incorrect in that there should be the lines dim dbconnection as string dbConnection = Dts.Connections( "PackageConnectionName").ConnectionString so the connection used is one set up in the package already The reason I went with script for this was efficiency - getting the names of the files already created and appending into a long string to be able to use instr to find values (hitting the database 1 time) would generally be faster and more efficient than hitting up the database multiple times - especially if the no of new files grew from 40 to something rather larger Other than that - thanks for the info - I'm by no means an SSIS expert so any hints and tips I can get from experienced users such as yourself are appreciatedRgds Geoff

I do try to shy away from Foreach Loops where database operations are involved as well - using a Foreach Loop with an Execute SQL Task inside it always seems to ask the question "shouldn't you be doing this in a Data Flow?" Talk to me now on

Todd, In your explanation above, you state the following: "Use an Execute SQL Task to do your lookup. You'll have to use property expressions to craft your SQL based on the filename variable the loop is using" I assume this will be where i would bounce the report name from the for each loop against the report name in the table in the database to see if there is a match. My question is what exactly to i do if the report is found in the table? Do i store the value in a parameter? I would want to just somehow state that if the report is found in table, then use that report name in the next step, otherwise either do nothing or store nothing. Does that make sense? How would i go about doing that?

You can use parameters, or a resultset to communicate "back" from SQL to SSIS. I'll describe input parameters and output result sets. Assuming you have a variable called "FleName" that your Foreach Loop is populating with a file name, then you can use a statement like this on the General tab: SELECT COUNT(*) FROM table WHERE filename = ? You'd also say you're getting back a ResultSet type of SingleRow On the Parameters tab, you'd specify parameter zero to be filled by your FileName SSIS variable. On the Result Set tab, add a result name of 0 (zero), and associate it with an SSIS integer variable called FileExists. Back in the Control Flow, double-click on your precedence constraint from the Execute SQL Task to whatever's next to "do", and change the operation to "Expression and Constraint". Set the expression to "@FileExists == 0". Now the tasks connected to the arrowhead will only get run if the COUNT returns a zero... Talk to me now on