Attempt execution of a package until File in Folder is found
-
I have a package that is running fine manually. The package loads a .csv file to a SQL Server table. The package then runs 4 simple SQL Tasks. I have now successfully deployed the package to an IS Catalog on SQL Server. Also I created a SQL Agent Job to run the package:
I have been tasked to find a way to have the job start running at 8am (whether a .csv file is in the holding folder or not) and run until either the file is found and can be processed or 10am arrives, whichever comes first.
I found this article ( https://www.mssqltips.com/sqlservertip/5625/how-to-retry-sql-server-integration-services-ssis-control-flow-tasks/ ) that appears to be just what I need to add to my package but I'm lost beyond creating the variables and putting in place what I have below. I'm having trouble completing the flow and setting the expressions:
-
Rather than going through all that, an alternative might be to run the package every 15 minutes (or whatever) between 8am and 10am.
If no file is found, the package completes gracefully and does nothing.
If a file is found, the tasks are performed.
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 -
What about if that file is not removed from the folder at the end of the day and another is not put in its place... will it not reprocess that file the next morning?
- DaveBriCam wrote:
What about if that file is not removed from the folder at the end of the day and another is not put in its place... will it not reprocess that file the next morning?
Yes it would. But I made the assumption that your process would archive the file elsewhere after successful completion.
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 - DaveBriCam wrote:
What about if that file is not removed from the folder at the end of the day and another is not put in its place... will it not reprocess that file the next morning?
If that's the case, though, how do you know the file is new or not. Your initial post implies that the file won't exist, so if it does but it's just yesterday's, how to you (or more importantly SSIS) know it's not today's file?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Yes that's it... I can create a File System Task to move it to an Archive Folder using some C# script:
using System.IO;
namespace ST_adc7961e6d564b959438804fe590a5a4
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string sourcePath = @"C:\Users\wally\desktop\convoso\";
string destinationPath = @"C:\Users\wally\desktop\archive\";
foreach (string sourceFile in Directory.GetFiles(sourcePath, "*.csv"))
{
string fileName = Path.GetFileName(sourceFile);
string destinationFile = Path.Combine(destinationPath, fileName);
File.Move(sourceFile, destinationFile);
}
} -
I can create a File System Task to move it to an Archive Folder using some C# script:
This is known to the rest of the SSIS world as a Script Task 🙂
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 -
Why create a c# task to move the file, and not just use a File System Task?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
Why create a c# task to move the file, and not just use a File System Task?
There was a time when I had some reliability issues with the FST – it sometime errored out and the error message was rather unhelpful.
So now I always write file-handling tasks in C#. I find them simpler to configure and easier to maintain, for all but the most trivial requirement.
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 -
I'm having unexpected trouble getting the SQL Agent Job to run. I first set it to run about 45 minutes ago and to run every 15 minutes. There are no errors in the log. I tried to use also the retry method within the step. No luck with either, but I can manually launch the job.
-
That's not enough information to enable us to help.
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 -
Let me know what info would help. I can run the package from the ISC with no problem, but when running from the Agent Job it says it can't read the .csv file and I've tried different versions of the file. I tried recreating the job also. No luck. Also the C# script task is not moving the csv to the archive folder even when I run the package from the ISC.
The agent error tells me to look at the Execution of the package which says basically it can't open the csv:
- Thom A wrote:
Why create a c# task to move the file, and not just use a File System Task?
If you move a file in Windows - it retains the properties and if you copy a file it inherits the properties from the destination. If you are archiving files and the archive location is compressed, then moving the file will overwrite the compression and you end up with files in a compressed folder that are not compressed.
For this reason - I always use a script task to archive files with a copy/delete operation instead of moving the file.
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 -
There's no compression on the source or destination folder and I used this just guessing, but no change in the results:
string sourcePath = @"\\10.1.30.29\ssis\";
string destinationPath = @"\\10.1.30.29\ssis\Archive\";
foreach (string sourceFile in Directory.GetFiles(sourcePath, "*.csv"))
{
string fileName = Path.GetFileName(sourceFile);
string destinationFile = Path.Combine(destinationPath, fileName);
File.Copy(sourceFile, destinationFile);
File.Delete(sourceFile);
} -
I wasn't responding with that as the issue - just responding to Thom on why use a script task. I would not expect anything in the script task to affect an error on the flat file source. You need to dig in further to determine - try viewing the context to see what further information is available.
Is this creating a file - or reading a file? I thought the idea was to load the csv file into the database if it exists.
The general method for accomplishing that task is to use a foreach loop container. Specify the location and the type of file to process in that component and put everything else inside the container. When the package runs and there are no files found - the tasks inside the foreach loop container will not be executed and the package will end. When there are files - the tasks inside will be executed.
Here is a general outline of an import process I use:
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply