Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
1
Guests Online:
64

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

Looping throgh Excel in SSIS

Please wait...

Looping throgh Excel in SSIS

Postby Andre on Wed Nov 21, 2012 8:28 pm

Hello!

I attempted to loop through Excel (.xlsx) files in a folder using two for each loops. The first loop uses a connection string variable which goes through all the excel files and the second loop uses an ADO connection which is suppose to loop through the variable name of the sheets.

My issue is that when I utilize a variable in the second loop for the connection which is suppose to determine the name of the sheet, it will not work and it tells me the columns in the metadata needs to be removed / added. I was wondering if anyone has had success in using a variable for the excel sheet.

For now I've settled with manually renaming the first sheet of every excel to Sheet1 with one loop which works fine.

tl dr: Would like to know if anyone has created an SSIS that loops through .xlsx files and only grabs the first sheet.
Andre
Apprentice
Apprentice
 
Posts: 11
Joined: Tue Apr 10, 2012 7:17 pm
Location: Chicago, IL
Unrated

Re: Looping throgh Excel in SSIS

Postby koenverbeeck on Wed Nov 21, 2012 9:07 pm

I'm not 100% sure what you are trying to achieve with the second loop.
Can you elaborate a bit more on that please?

Are you just trying to loop through the various sheets of the Excel file?

Here's a general article on how to loop through Excel files/sheets:
Loop through Excel Files and Tables by Using a Foreach Loop Container
User avatar
koenverbeeck
Newbie
Newbie
 
Posts: 4
Joined: Wed Nov 21, 2012 9:04 pm
Unrated

Re: Looping throgh Excel in SSIS

Postby Andre on Thu Nov 22, 2012 4:55 am

I want to go through the first sheet in every excel file, there should only be one sheet. I'll try the method in the link you provided, I'm not sure if my files being .xlsx will make a difference but I'll let you know my findings. I assumed I would need a second for each loop since the first sheet name in every file was different.
Andre
Apprentice
Apprentice
 
Posts: 11
Joined: Tue Apr 10, 2012 7:17 pm
Location: Chicago, IL
Unrated

Re: Looping throgh Excel in SSIS

Postby Andre on Fri Nov 30, 2012 6:54 pm

In the end it turned out one of my excel files was corrupt and had a hidden sheet that I couldn't delete. Instead of using two for each loops I'm now using one for each loop to go through the .xl files in a folder and using a script task to go through the sheets.
Andre
Apprentice
Apprentice
 
Posts: 11
Joined: Tue Apr 10, 2012 7:17 pm
Location: Chicago, IL
Unrated