Solved

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

Posted on 2008-10-31
9
1,716 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now