August 24, 2017 at 7:26 am
Hello Good Morning,
I am trying to build ssis package to solve
loading excel file to sql server table, here the issue is the excel file has data after some rows (not fixed every time in every file, sometimes data is after 10 rows sometime it is after 20 rows)
can you please advise how can I load this ?
also is there any possibility to do it without coding the reason I am asking is the prod server doesn't have Microsoft office installed so coding will work in dev but not in prod(I think)
so kindly help with this issue,
Thank you a ton in advance
dhani
August 24, 2017 at 7:32 am
Will there be any data above the rows, or will they simply be blank? If they are truly empty (no data has ever been put in them, and not formatting applied), then ACE should start at the correct cell position regardless of where your data is. If not, then I would suggest loading the entire sheet into a staging table, and then processing your transformations there (deleting blank rows, etc), rather than in SSIS. I would, however, personally ask your data supplier to try and "sort their data out", and supply it in a standardised format.
On your SQL Server, there is no need to have office installed to interact with Excel, you simply need to ACE drivers. you can download using the below links.
2010: https://www.microsoft.com/en-gb/download/details.aspx?id=13255
2016: https://www.microsoft.com/en-us/download/details.aspx?id=54920
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 24, 2017 at 9:58 am
Hi There thank you for your answer,
my file initial rows has data some titles and some summary information which i don't need to load to sql table,
I will try as you suggested, meanwhile do you have any links to code way or other way you suggested
Thank you much
August 24, 2017 at 10:36 am
How is your Excel Source configured? I've solved similar problems by setting the Data access mode to "SQL command" then writing a query such as:SELECT * FROM [sheet1$] WHERE yourcolumnname IS NOT NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply