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.
| Author |
Topic |
|
scabral7
Yak Posting Veteran
57 Posts |
 Posted - 2011-05-24 : 16:34:39
|
| 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?thanksscott |
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-05-25 : 17:46:48
|
| i think instead of using the for each loop container take script task using c# or Vb you can able to find out the file name Matching inside the script itself when match found write the filename to a Table or excel file so in the end you will get all the file names in that Table or excel file.Is it simpler i think so. |
 |
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-05-26 : 10:45:12
|
| Create a variable "dir" of string type which points to your directory location(folder with files).Add using System.DataClient Try these script in the script task in main function: string dir_path= null; Variables var = null; Dts.VariableDispenser.LockForRead("User::dir"); Dts.VariableDispenser.GetVariables(ref var); dir_Path= (string)var["User::dir"].Value; string[] files= System.IO.Directory.GetFiles (dir_Path); foreach(string filename in files ) { if (condition) { string connection = "Server=ServerNAme;Database=DatabaseName;Integrated Security=SSPI"; SqlConnection conn = new SqlConnection(connection); string sqlcmd = "INSERT INTO [DatabaseName].[dbo].[TableNames]([ColumnName])"; sqlcmd += "VALUES('" + filename + "' )"; SqlCommand command = new SqlCommand(sqlcmd, conn); conn.Open(); command.ExecuteNonQuery(); conn.Close(); } } Dts.TaskResult = (int)ScriptResults.Success; }Then the table will have all the Matching file names now using a execute sql task read the files and do whatever you like to do the variable now must be of type "Object".Thanks |
|
|
|
|
|
|
SSIS populate object Variable