Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SSIS package for importing Excel files


Dynamic SSIS package for importing Excel files

Author
Message
jonesj-761762
jonesj-761762
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
jonesj-761762
jonesj-761762
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search