Hi, I have the following requirement: I want to capture all the file names which were changed within 24 hours in a directory and its sub directories. once i have this data i want to insert this data into a table. i do not have rights to create any files i.e. i cant create any .txt or .xls or any file for that matter on the server. will this be possible usig SSIS 2005. Thanks Rohit

If your question is: can I create files on the system with SSIS ist the answer "possible" If your question is: can I fetch all names of modified files and put it into a table on SQL server, the answer is "yes". You can use a script task to detect the modified files, put them into the output and direct it to an oledb destination. You need the right to create a table on the server or ask someone to that for you.

you need a script task to fetch all file which modified in past 24 hours, and this is script: Note that you should define an OBJECT data type variable with name FileNameArray, and set it as ReadWriteVariables in the script task. public void Main() { System.Collections.ArrayList NewList = new System.Collections.ArrayList(); string[] files = System.IO.Directory.GetFiles(@"C:\SSIS\Files"); System.IO.FileInfo finf; DateTime lastDate = new DateTime(); string lastFile = string.Empty; foreach (string f in files) { finf = new System.IO.FileInfo(f); if (finf.LastWriteTime > DateTime.Now.AddHours(-24)) { NewList.Add(f); } } Dts.Variables["User::FileNameArray"].Value = NewList; Dts.TaskResult = (int)ScriptResults.Success; } after script task an array of changed files stored in the FileNameArray . now add a foreach loop with set enumerator as ado enumerator and set FileNameArray variable as source there. you need a string variable to fetch file names into it, so create new string data type variable, name it as FileName then in foreach loop editor, in variable mapping tab, set variable name as User::FileName and set index as 0 now add data flow task in the foreach loop container. set flat file source and any destination you need in the data flow task and then right click on flat file connection manager, go to properties, select expression, set filename property with the User::FileName variable Note that you should set default valid value for FileName variable. That's all.http://www.rad.pasfu.com

Hi Reza Raad, Thanks for the quick response. Looking at the script it seems its a C# script. As per my knowledge we can use Vb.Net script in the script task available in SSIS. I checked the Script language property in the script task and it only gives me a option of VB.Net script. can u please let me know the VB.Net sript for the same. I know i am asking too much but i am not too much aware of .Net coding :) Also looking at the script i feel only the Files will be scanned to check if there are any modified files. considering that there is few more sub folders inside files(C:\SSIS\Files\Temp\) folder will those be scanned too? Thanks, Rohit

this is the code in VB.NET: Public Sub Main() Dim NewList As New System.Collections.ArrayList Dim files As String() = System.IO.Directory.GetFiles("C:\SSIS\Files", "*.*", IO.SearchOption.AllDirectories) Dim fint As System.IO.FileInfo For Each f As String In files fint = New System.IO.FileInfo(f) If (fint.LastWriteTime > DateTime.Now.AddHours(-24)) Then NewList.Add(f) End If Next Dts.Variables("User::FileNameArray").Value = NewList Dts.TaskResult = ScriptResults.Success End Sub and yes, it will loop through all sub directories. http://www.rad.pasfu.com

Hi Reza Raad, thanks for the quick response. I am currently getting an error on "Dts.Variables("User::FileNameArray").Value = NewList" "property access must assign to the property or use its value" any reason why i am getting thi error. Am I missing anything. Thanks again, Rohit

First thing: You have to make the variable filenamearray as readonly variable in script task editor and to access it you need to use: Dts.Variables("filenamearray").ValueNitesh Rai- Please mark the post as answered if it answers your question

set FileNameArray variable as ReadWriteVariables in script task editorhttp://www.rad.pasfu.com

hi Reza Raad, Thanks i got through that. but then when i am connecting it to the For Each loop and executing the package i am getting a error "Error: Variable "User::FileNameArray" does not contain a valid data object" you have any possible reason for this Thanks, Rohit

Is it runtime error ? or design time error?http://www.rad.pasfu.com

Runtime when i execute the package. The Script task gets executed sucessfully. but the DFt gives me this error.

you are right, that was my fault,I should set object as data table, change script task code as below: I tested it and works fine: Public Sub Main() Dim NewList As New DataTable Dim col As New DataColumn NewList.Columns.Add(col) Dim files As String() = System.IO.Directory.GetFiles("C:\SSIS\Files", "*.*", IO.SearchOption.AllDirectories) Dim fint As System.IO.FileInfo For Each f As String In files fint = New System.IO.FileInfo(f) If (fint.LastWriteTime > DateTime.Now.AddHours(-24)) Then NewList.Rows.Add(f) End If Next Dts.Variables("User::FileNameArray").Value = NewList Dts.TaskResult = ScriptResults.Success End Sub http://www.rad.pasfu.com