Looping through Excel WORKSHEETS using SSIS-Cannot set variable to Excel Source

I notice a couple things. If UPD is your ADO.net, Microsoft Jet connection to and Excel spreadsheet, where is your Connectoin Manager for the SQL Database you are loading? You also get an error message that it cant find the SQL table, related?

I created a second connection for the Data Flow task. The initial data connection I made for the "ForEach Loop" does not show up as a connection I can use for the Data Flow task.

When I manually enter a value in the "Value" field for the "WorkSheetName" variable the package runs just fine. When I leave this blank is when I get the above error message. For some reason the value that is being entered into the variable is incorrect. I would like to be able to see what is actually being passed to the variable but I have not figured out how to pipe that to anything I can view.

To see the value being passed into the WorkSheetName variable, put only a Script Task in the Foreach Loop Container. Pass the ReadOnlyVariable WorkSheetName into the script. Write a single line of code:
MsgBox(DTS.Variables("WorkSheetName").Value.ToString)
This will show you a Message Box with the value for each occurrence in the Foreach Loop. Do this and report back so we can debug together. Thanks.

Ok this makes NO sense.

1. I ran the script task and It returned a value of "UPD FILE". I had previously entered this manually and it worked just fine.

2. I re-enabled the Excel Source task. It once again gave me the table cannot be found error.

3. Just on a hunch I shortened the Excel worksheet name to "UPD"

4. I also set Validate External MetaData to false. I have done this several other times with no success, but I was just trying things here so....

5. Ran the Package and it worked.  WTF??

6. Went back and changed the name to a two part name. Package still worked.

7. Turned Validate External MetaData to "True" The package failed.

This makes NO sense as I have set the Validation to False several times before but with no success.

Ok this is what I am going to do. I will create a package from scratch and see if I can make it work. Will let you know the results in 15

Here is an example I have to retrieve the worksheet names.
WorkSheetName string variable
Script Task is below:

Public Sub Main()
		'
        ' Add your code here
        Dim Index2 As String
 
        Index2 = Dts.Variables("WorkSheetName").Value.ToString
 
        MsgBox("WorkSheet Name: " & Index2)
 
		'
		Dts.TaskResult = Dts.Results.Success
	End Sub

Select allOpen in new window

foreach-loop-container.bmp
ADO-NET-Connection.bmp
foreach-loop-editor.bmp
foreach-loop-variable.bmp
msgbox.bmp

Ok created another package. Followed the same steps as above. The script task runs just fine. The Data Flow task fails.  I have turned Validation on and off. No go.

OMG!!! I think I have it!

This worked and I was able to recreate it twice.

1. Create ForEach Loop as described in Books Online
2. Create a Excel Source Data Flow source. Map this to an EXISTING Workbook and NOT the variable you created in step one.
3. Create your Data destination and then set up your column mappings.
4. Now go into the "Properties" of the Excel Source. Change the Access Mode to "OpenRowset From Variable" and the OpenRowset Variable to the variable you created in step one.

From here on it appears that the variable will populate correctly and the package runs correctly.

I do not know if this is the standard way to make this process work but I will take it as a win. Hogzilla what are your thoughts?

Thanks very much for your help, especially with the Script Task. I had tried doing that before but my setup was just a hair off. This has been a thorn in my side for a LONG time so it is great to finally figure it out!