Solved

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

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

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 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

23 Experts available now in Live!

Get 1:1 Help Now