July 17, 2016 at 11:02 am
I'm new to SSIS so this may seem like a very elementary question.
I have a real simple process that imports a .txt file into a SQL table. I created a Data Flow task with 2 components: source and destination. If the source file isn't there I want to trigger an email notification.
I created an On-Error event handler that calls a stored procedure (that I created) that does the eMail distribution. The problem is that when I tested it, it sent out 2 eMails intead of 1. In looking at the debug message, there were 2 errors thrown (see below). I assume the Event Handler was triggered twice and that's why 2 eMails were sent out? How do I ensure that only 1 is sent?
Warning: 0x80070002 at Data Flow Task, SCMData [2]: The system cannot find the file specified.
Error: 0xC020200E at Data Flow Task, SCMData [2]: Cannot open the datafile "\\SCMISC\testdata.txt".
Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: SCMData failed the pre-execute phase and returned error code 0xC020200E.
Information: 0x402090DD at Data Flow Task, SCMData [2]: The processing of file "\\SCMISC\testdata.txt" has ended.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: “ImportData" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "X:\SSIS_Pkgs\SCM_Import_SSIS2015\Package.dtsx" finished: Failure.
2nd question:
If the .txt file is present and the job completes, how can I move the .txt file to another folder from within the SSIS package?
July 17, 2016 at 5:04 pm
1. You could add a separate task to check whether the file exists, or use the OnTaskFailed event, instead of OnError, which only fires once.
Question 2. File System Task. Operation : Move file.
July 17, 2016 at 5:33 pm
OK, I didn't see an option to check for the presence of the file, so I added a task to rename the file. If the file isn't there, the task fails and triggers the sproc, so it serves my purpose quite well. Thanx.
Regarding the move, there's a little more to it. I have to move the file to a different server and also rename with today's date. For example,
Q:\ImportData.txt --> N:\Archive\ImportData_20160717.txt
Not sure how to do that.
July 19, 2016 at 7:23 am
I always use a script task to check for the file and set a file name variable. I then add a condition on the connection to the next task that checks to make sure it has a value. You could add a second connection to your email task that has an expression that checks for the variable not having a value.
If you don't like using script tasks, you could use a ForEach loop, and configure it to look for files in your drop folder location. Set up the ForEach loop to put the file name in a variable. Add the email step after the ForEach loop, and make it conditional on the file name variable not being set.
If you want to use the error handler though, I believe you can use a script task in your event handler to check the error number, then make your email conditional on a specific error number that indicates the file is missing.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply