question

Noonies avatar image
Noonies 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?
ssisforeach-loop
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you see any errors in the Execution Results tab?
0 Likes 0 ·
I see a Warning stating The end of the data file was reached while reading header rows. Make sure the header row delimiter and the number of header rows to skip are correct. Other than that Validation is completed and Finished. No errors but a warning.
0 Likes 0 ·
Raj More avatar image
Raj More answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Kirman1 avatar image
Kirman1 answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.