Dynamic SSIS package for importing Excel files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Dynamic SSIS package for importing Excel...

Dynamic SSIS package for importing Excel files

Rate Topic

Display Mode

Topic Options

Author

Message

jonesj-761762

Posted Tuesday, August 05, 2008 12:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2008 2:07 PM
Points: 4, Visits: 40

I wanted to be able to import Excel files to a generic working table, then use stored procedures to manipulate the data. I have a generic raw table with 60 columns, all varchar. I wanted to be able to have the SSIS package simply take the data from the Excel file and migrate into the raw table. I have a configuration file where I can pass in the directory of the file, along with the name of the worksheet to use.

I am using an Excel Source, Data Conversion, and OLE DB Destination object in the Data Flow. I have this embedded in a ForEachLoop as there can be several files in the directory.

Currently I get a series of errors when trying to automate, and what I find by trying a web search is not much help.

Thanks in advance

JJ

Post #547000

Jack Corbett

Posted Tuesday, August 05, 2008 12:27 PM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

It sounds like you could make this work. Without knowing what the errors are it is hard to suggest any solutions though.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #547005

jonesj-761762

Posted Tuesday, August 05, 2008 12:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2008 2:07 PM
Points: 4, Visits: 40

I do get some validation warnings. "Validation warning. XLS to Raw Table: Data Conversion: The component has inconsistent metadata"

The only thing I can think is that in my data conversion I mapped for up to 60 columns. None of the Excel files will have this many (so they tell me). Some of the columns also have NULL as the value in the first row. These NULL columns seem to be the numbered errors.

Did I miss something in my conversion step? When I look at the Excel Source, I am able to preview the data set correctly, but those NULL values are in there.

Thanks so much for the assistance!

Post #547010

« Prev Topic | Next Topic »

Permissions