ForEachloop SSIS

Question

Task: Loop thru these excel files and insert data into SQL table but in the process i get an error and i don't know which it errored on.

My understanding is SSIS doesn't loop thru file in an random order but i get an error about CANNOT ACQUIRE CONNECTION FROM CONNECTIONMANAGER. Excel Source failed validation and returned error code.. I did set 64bitruntime to False. This happened on VS 2008/SQL Server 2008 R2 on Windows 7 OS. Initially i was able to run the whole process successfully on Windows XP- VS2008 /SQL Server 2008 R2.

Problem: How do i know which file system is going to iterate next if i have 70 odd files in a folder. The thing i get an error and i'm not sure which file SSIS is working on. However i do see files are executed and data is in SQL. Let me know how to find which file SSIS is currently working or the next one it will work on.

You can't know what the loop is going to pick next. You can only know what it has currently selected. Unless you choose to roll your own foreach looping mechanism. I know somewhere in my history, I've got answers for how to process files based on newest-oldest or oldest-newest. — billinkc, Jul 11 '14 at 21:57
thanks for your response Billinkc, How would i trouble shoot this error. I can go back to sql table to check which's file is in and wipe those files out but that would be tedious process. Is there any better to do this.. — user1810575, Jul 11 '14 at 22:08
Add a step that logs the file in the loop to a table _before_ it gets imported, in a table with an identity field so you know which was the last to be logged. In fact this is probably worth building in permanently anyway as an audit mechanism. You could also add an event handler that msgbox's the variable value if an error occurs. — Nick.Mc, Jul 12 '14 at 01:03

score 0 · Answer 1 · answered Jul 12 '14 at 21:42

Add a script task inside your ForEach container, immediately before you do the Excel processing. In the script task, add the variable you configured in your ForEach loop to hold the filename to the Read Only Variables. In the script itself, call the FireInformation event, which will add an informational message to the progress log in SSIS. In the FireInformation call, pass the value of your filename variable as the message argument.

This will let you see each file being processed, and which one it was processing when it failed.

FireInformation help: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.idtscomponentevents.fireinformation.aspx

score 0 · Accepted Answer · answered Jul 12 '14 at 21:54

You could add a Script Task and log the variable used in the foreach loop. enter image description here

Add the variable as readonly variable in the script task editor and then add something like this in the main method (C#):

public void Main()
{
    bool fireAgain = true;

Dts.Events.FireInformation(0, "Logging FELC variable", "File: " + Dts.Variables["User::FilePath"].Value.ToString(), string.Empty, 0, ref fireAgain);

ForEachloop SSIS

2 Answers2