Attempt execution of a package until File in Folder is found
-
Thanks and I agree that the For Each Loop Container is the best route after I iron out more core problems. I'm writing this program first assuming that the .csv file exists and is in the correct format. I'm reading from this file into SQL then moving the file into the archive folder.
My bigger problem is I can run the package perfectly in debug in Visual Studio. I can run the deployed package from the catalog, and it reads and writes perfectly to SQL. But with my Agent Job the package fails completely.
-
You still need to view the context of the errors to see if there is more information. But based on your description - the issue is most likely a permissions issue. Do you have a proxy account set up and assigned to the integration services subsystem in SQL Server agent? Is the job step set to run using that proxy account?
If not - I would recommend setting one up and making sure that windows account has the necessary permissions on the folders.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
How do I create a Proxy? i.e.: Proxy name, Credential name, Principals, Type of Principal, etc.
-
What code are you using in your 'Archive File' script task?
- DaveBriCam wrote:
How do I create a Proxy? i.e.: Proxy name, Credential name, Principals, Type of Principal, etc.
I searched the following: sql server proxy account ssis
Here are the top 3 items found:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Here is the code I use for the archive task:
bool fireAgain = true;
// Get the source and destination files
string fullFileName = Dts.Variables["User::FullFileName"].Value.ToString();
string archiveFileName = Dts.Variables["User::ArchiveFileName"].Value.ToString();
try
{
if (File.Exists(fullFileName))
{
File.Copy(fullFileName, archiveFileName, true);
File.Delete(fullFileName);
Dts.Events.FireInformation(0, "Archive File Task", fullFileName + " archived to " + archiveFileName, string.Empty, 0, ref fireAgain);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(0, "Archive File Task", ex.ToString(), string.Empty, 0);
}For this project - I don't need a date stamp of the archive file because the input file is already date stamped. So all I need is the archive directory where I am 'moving' the file.
Other code builds an archive directory if one doesn't exist - and then builds dated folders in the archive location so all files loaded in a month are archived to that monthly folder.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I like your code but for right now I'm testing the below to simply delete the file. My package running from SQL Agent is reading the file and writing successfully to SQL Server but the file won't delete and my code for writing to the archive folder failed too.
string sourcePath = @"\\10.1.30.29\ssis\";
foreach (string sourceFile in Directory.GetFiles(sourcePath, "*.csv"))
{
string fileName = Path.GetFileName(sourceFile);
File.Delete(sourceFile);
} -
Use the Try/Catch from my sample code - then check to see what the error is when it fails.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I put in the Try/Catch code and the two dts variables, but the C# has errors: "The name File does not exist in the current context.". I have these values in my variables, \\10.2.44.29\ssis\smslog.csv, and \\10.2.44.29\ssis\archive
-
Add
using System.IO;
to your 'using' block and it should fix that.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Thanks!... I'm always forgetting "using System.IO;"
-
Just to get the program running I used, reluctantly, a File System Task... and it worked but with limitations. The code I have in the script task is failing to move the file, but it may be the values of my variables (?). It is however writing to the database still with no problem so I know the problem is either my variables or the C#...
My goal is to be able to have the completed CSV file move into the Archive Folder without manually having to rename the existing file or deleting it... that is I need the code eventually to rename the file (Maybe with a timestamp as part of the name) before moving it to the Archive. Does this make sense?
I was able to get the File System Task to overwrite the existing file in my Archive folder, which is good enough for now.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply