Jeff Moden (7/4/2016)
Phil Parkin (6/28/2016)
Jeff Moden (6/27/2016)
jeffe_verde (6/26/2016)
The files have data for the Asia-Pacific, EU, and the Americas financial markets (thus the AP, EU, and AM in the file names), and each file is created after close of business for that region. So the create TIME for the EU file is about 8 hours after the AP file, and the AM file is about 8 hours after the EU file.Right now, I check the FTP throughout the day, to catch each file as it's created.
It seems, then, that you need something that will list the CREATED DATE/TIME for each of the files. Are you allowed to use xp_CmShell or other "thing" that will allow the interrogation of that information at the OS level? I could be wrong but I don't believe that SSIS itself has anything that would return the date and time of creation of files. Being able to do such a thing would allow total automation.
On this occasion, you are wrong, Jeff 🙂
I have not had the time to create sample code, but this can be achieved using a Script Task containing just a few lines of code.
Looking forward to see that, Phil. 😉 I'm all in favor of learning something different.
As you asked so nicely.
As you may know, Script Tasks execute C#, so if you know the language, they can be a powerful tool.
I created a folder on my PC called c:\SSISFileTest and put some files in there. Several .txt files and one or two other file types.
If we assume that we wish to rename all .txt files in this folder by prefixing the file names with datetime of creation (in format YYYYMMDD MMss), here is the code to do it.
In order for it to work, you'll probably need to add using System.IO; to your 'Using' block.
string fld = @"c:\SSISFileTest";
string fileType = "*.txt";
FileInfo fi;
string newName;
foreach (string f in Directory.GetFiles(fld, fileType))
{
fi = new FileInfo(f);
newName = Path.Combine(fld, fi.CreationTime.ToString("yyyymmdd HHmm") + " " + Path.GetFileName(f));
//Do the rename
File.Move(f, newName);
{
Aside from the declarations, that's just three statements in a loop.
If this were 'properly' implemented in SSIS, I would expect the folder and file type to come in as SSIS parameters, but that is a trivial change.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.