Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
corkyhawk
5 Posts |
 Posted - 2011-04-18 : 15:18:24
|
| I want to create a reuseable SSIS package to import data from several Excel files that I receive from a client. The Excel files contain account information and I receive one file per month based off the month the account is opened. The first five columns contain static account information. The next 1 to 12 columns contain monthly sales amounts. (We're trying to track the first 12 months activity.) So I might receive a "January 2011" file that will have values in columns 6 - 9 for January - April sales and I might receive an "April 2011" file with only data in column 6 for April sales since that's all the data we have for those accounts. I want to load the data into a staging table that contains the five static fields and then a month01 - month12. The spreadsheets have a header row, but I will ignore that because the headers display the relevant month/year, which will change for each file. So I basically just want to map column F6 to Month01, F7 to Month02... F17 to Month12. I have a template spreadsheet that is used for development, but when I run the program on real files, I get an error such as "[Excel Source [1]] Error: Column "F10" cannot be found at the datasource." if there isn't data in all of the columns. Any suggestions. I'm sure this is clear as mud, so please ask if you need more clarification.Thanks! |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-04-18 : 19:09:06
|
| it means one of the Excel files you are trying to ingest does not have an F10 column.what do you currently have in your SSIS package, can you show us a snapshot of how you are mapping columns from Excel to SQL staging table?Are you mapping it dynamically or programmatically using Excel object model in c# or vb.net? if you use a script to parse through the columns you will have more control otherwise you will get these type of errors you describe.If you don't have the passion to help people, you have no passion |
 |
|
|
corkyhawk
5 Posts |
Posted - 2011-04-19 : 09:26:09
|
| Correct, the Excel file doesn't have anything in column F10 by design. The spreadsheets have 1 to 12 columns of monthly sales data depending on how old the accounts are. All of the accounts in each spreadsheet are the same age, as far as which month they became active. So some spreadsheets will have 1 column of sales data, some 2, some 3, up to 12. I'm fine with getting NULLs for those columns with no data, I just don't want the package to fail because it doesn't think the column exists. I can make the package work if I edit the files and put a header in each of the columns that aren't populated in that particular spreadsheet, but that would become very time consuming. (I don't have any control over how the spreadsheets are created.) Is there a way to tell SSIS to include the extra columns, even if there isn't any data in them. Thanks! |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
|
|
|
|
|
|
|
Importing Excel referencing columns without header