Solved

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

Posted on 2008-10-31
9
1,738 Views
Last Modified: 2013-11-10
I am attempting to use a ForEachLoop Container to run through Various Excel Worksheet files and import them into SQL.
I had previously asked this question in a different thread. I thought I had it working but when re-opened the project I have not been able to get it to work. I have attached all the screenshots from the setup and the error message I get when I try and apply the variable to the Excel Source Data Flow.
If I manually enter the worksheet name into the variable value it works just fine. For some reason the ForEach loop editor is not populating the variable with the correct info. Any info on where I am going wrong would be greatly appreciated.

Collection-Tab.bmp
Error-message.bmp
Excel-Source-Editor.bmp
Excel-Source-Properties.bmp
Foreach-Connection-Manager-Setti.bmp
Main-Page.bmp
Variable-Mappings.bmp
0
Comment
Question by:CSTX_Analysts
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22855060
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?
0
 

Author Comment

by:CSTX_Analysts
ID: 22867213
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.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22867658
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.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:CSTX_Analysts
ID: 22868074
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
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22868552
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

Open in new window

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

Author Comment

by:CSTX_Analysts
ID: 22868567
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.
0
 

Author Comment

by:CSTX_Analysts
ID: 22868814
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?
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 125 total points
ID: 22869166
OK, I think you have it.
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. YES, when you are in DESIGN mode you have to give it something real to create against.
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.  There ya go, that is it and done correctly.
0
 

Author Closing Comment

by:CSTX_Analysts
ID: 31512221
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!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question