asked
SSIS Foreach Loop Conatiner and Import showing Successful when File does not align with Data Flow Task
I must be overlooking something pretty simple here in maybe the order of precedence I have my SSIS utilizing ForEach Loop Conatainer. I created a Foreach Loop Container which has the folder location defined and Files set to *.csv. Variable Mappings is looking at the user::SourcePath and User::CSVFile. I have the Data Flow Task in the Foreach Loop Container which is picking up a Flat File (CSV) and loads to a specified SQL Table. I did add two File System Tasks within the container. 1) move files into Archive Folder and 2) move files to Error folder. Based on the upload outcome email notification would be sent after files have been moved based on Success or Failure. When running some testing, I moved a CSV file which doesnt have any data columns to align with the data flow task and the data flow task was showing successful. The data, didnt actually load into the SQL table because the columns for upload do not align with the CSV file I was using for test. I was thinking this file test would create a Data Flow Task as failed, move the file to the Error location and send an Email Task stating the upload failed. The File System Task which is run when the Data Flow Task shows successful, does move the file to Archive as configured and sends email notification. But the Data Flow Task is always showing successful. Can someone please let me know what I overlooked? I compared another package which utilizes the Foreach Loop Container and does error handling and I cannot see where I have an added configuration when reviewing that handles this. Any ideas as to what I may have overlooked on my end? Or maybe configured incorrectly?
It seems to me like the new file you put in is either empty, or only has one row in it (which, SSIS thinks, is the header row). Since SSIS 2012 works with Jagged Edges, it will allow you to have fewer columns. Since there is no actual data (starting row 2), it doesn't have to load anything.
The file I placed in the location shows loaded but I would think since the file doesnt reference any columns for the Data Flow Task, that the import would should as failed vs. successful. I was using a file that has no association with the table import to make sure the file fails and I can test the Error notification I have in place.
Can you disable your For Each Loop container and then try to replicate the same problem outside of it with the same Data Flow Task which is NOT alingned with your CSV.
@Kirman1 - If I modify the Source Connection to the specified flat file the task fails that is using the non-aligned CSV file. I have now modified the ForEachLoop container to look for files like SUDTS*.csv only and I added a column to this file and the Data Task Flow does Fail now. But I cannot get the Precedence Connector from the Data Task Flow to the File Task to work when Precedence is set to Constraint - Failure. I tried taking the Error File Task and Send Mail out of the ForEach and putting on an Event Handler for ForEach Loop Container but that created errors stating the file was in use but I couldn't find where it was in use if the package failed. Any ideas? If I'm being too vague or if you need more information, please let me know. I really appreciate everyones help!
16 People are following this question.
