May 10, 2016 at 9:16 am
Im having to export SQL Data to an xlsx worksheet
However one of the worksheets doesn't have the header on the top row.
A B
1 Input if there is a match Input if Yes
2 Ethnic Group Ref_ID
So the Headers are on Row two.First Row has column names cant be used for this instance because obviously its the second column.
How do you set this up? Ive done some research and I cant find anything that actually works for this scenario
Debbie
May 11, 2016 at 6:05 am
Will the columns always be the same amount, for example Columns A through to J? If so, you can set the sheet name to;
[Sheet1$A2:J] This will make SSIS select all data from Columns A to J, starting at row 2. If you have First Row Contains headers ticked, Row 2 (Row 1 from the dataset point of view) will be used as the headers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 11, 2016 at 6:33 am
Thom A (5/11/2016)
Will the columns always be the same amount, for example Columns A through to J? If so, you can set the sheet name to;[Sheet1$A2:J] This will make SSIS select all data from Columns A to J, starting at row 2. If you have First Row Contains headers ticked, Row 2 (Row 1 from the dataset point of view) will be used as the headers.
Note that it's an export to Excel, not from. Does your suggestion work for exports too?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 11, 2016 at 7:06 am
It will always be the same. I'm not quite sure where you would set that information though.
May 11, 2016 at 7:08 am
Phil Parkin (5/11/2016)
Thom A (5/11/2016)
Will the columns always be the same amount, for example Columns A through to J? If so, you can set the sheet name to;[Sheet1$A2:J] This will make SSIS select all data from Columns A to J, starting at row 2. If you have First Row Contains headers ticked, Row 2 (Row 1 from the dataset point of view) will be used as the headers.
Note that it's an export to Excel, not from. Does your suggestion work for exports too?
Sorry, I had misread. Not quite, but not far off. Instead use the Data access mode SQL Command and enter
Select *
from [Sheet1$A2:J]
Where Sheet1 is the name of your sheet, A2 is the starting cell, and J is the end column. This is in your Excel Destination Node.
That'll skip the first row instead.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2016 at 2:15 am
I will certainly have a look at that. I didn't know you could use the Sql command when you are using an excel destination source. I thought you could only choose your excel file and then choose the worksheet.
Debbie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply