Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic SSIS package for importing Excel files Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2008 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 5, 2008 12:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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
Posted Tuesday, August 5, 2008 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse