SSIS Foreach From Variable Enumerator Multiple Variables
-
Currently I have a working script task which reads a directory to find filename(s) that match a certain regex. The ArrayList object is passed to a Foreach Loop which uses a Foreach From Variable Enumerator and a single variable index = 0 to BULK INSERT into a table.
I am trying to add additional functionality to the script task which also include the file creation time in the object and then the foreach loop will have two variables one for the filename and another for the file creation time. I've tried two different versions below (a lot of code is omitted) to no avail.
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
private List<FileInfo> REQUEST_FILES;
private FileInfo MATCHINGFILES;
private List<Tuple<string,string>> SOURCES;
public void Main()
{
string archiveFilePath = (string)Dts.Variables["User::FilePathArchive"].Value + "\\";
SOURCES = new List<Tuple<string, string>>();
NewestFiles(archiveFilePath);
Dts.Variables["User::SourceObject"].Value = SOURCES;
Dts.TaskResult = (int)ScriptResults.Success;
return;
}
private void NewestFiles(string archivePath)//Archive files other than the Newest
{
MATCHINGFILES = REQUEST_FILES.OrderByDescending(file => file.CreationTimeUtc).First();//CreationTimeUtc was chosen on purpose instead of LastWriteTimeUtc
foreach (FileInfo x in REQUEST_FILES)
{
if (x.FullName != MATCHINGFILES.FullName)
{
MoveFile(x.FullName, archivePath + x.Name);//MoveFile was chosen because it preserves the CreationTimeUtc
}
else
{
SOURCES.Add(Tuple.Create(MATCHINGFILES.Name, MATCHINGFILES.CreationTime.ToString("yyyy-MM-dd HH:mm:ss")));
}
}
}
}
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
private List<FileInfo> REQUEST_FILES;
private FileInfo MATCHINGFILES;
private ArrayList SOURCES;
public void Main()
{
string archiveFilePath = (string)Dts.Variables["User::FilePathArchive"].Value + "\\";
SOURCES = new ArrayList();
NewestFiles(archiveFilePath);
Dts.Variables["User::SourceObject"].Value = SOURCES;
Dts.TaskResult = (int)ScriptResults.Success;
return;
}
private void NewestFiles(string archivePath)//Archive files other than the Newest
{
MATCHINGFILES = REQUEST_FILES.OrderByDescending(file => file.CreationTimeUtc).First();//CreationTimeUtc was chosen on purpose instead of LastWriteTimeUtc
foreach (FileInfo x in REQUEST_FILES)
{
if (x.FullName != MATCHINGFILES.FullName)
{
MoveFile(x.FullName, archivePath + x.Name);//MoveFile was chosen because it preserves the CreationTimeUtc
}
else
{
SOURCES.Add(Tuple.Create(MATCHINGFILES.Name, MATCHINGFILES.CreationTime.ToString("yyyy-MM-dd HH:mm:ss")));
}
}
}
}
When debugging the script task is finding the file and creationdatetime, but failing in the variable assignment to two string variables
I asked co-pilot, which game me broken code and can't seem to find an example online of multi-variable assignment.
-
I figured it out using a datatable with an ado.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply