Addition of row in Excel sheet(using SSIS)

Question

Addition of row in Excel sheet(using SSIS)

M_GREAT_4_SQL

Right there with Babe

Points: 752
More actions
February 11, 2010 at 8:15 am

#137141

Hi,
Using SSIS how can one add a row in excel sheet, for some requirement I have to perform row addition before pulling data from excel sheet.
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

M_GREAT_4_SQL Right there with Babe Points: 752 More actions · Answer 1

Let me explain the issue in detail.

This is related to excel data provider problem which I have mentioned in one of my earlier thread. IN excel sheet after 20th row I am getting some data which has more than 255 charecters, and provider is deciding column data type on the basis of first 8 rows. Whenever SSIS package starts pullin data, it gives error for those rows.

To resolve this if I wanted to add one sample row as a very first row, then column data type would be defined easily and all the entries would get pulled without any truncation error.

Let me know what would be the code to add one sample row in excel.

Thanks in Advance,

LutzM SSC Guru Points: 107049 More actions · Answer 2

Copied straight from this post:

SSIS samples rows in Excel. It looks like it examines the first 8 rows.
Search for this phrase to learn more. 'ssis typeguessrows excel'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

David Data SSCrazy Points: 2965 More actions · Answer 3

A better solution is to make the driver test more rows. E.g. use this connection string for your file, which I'm assuming is in a variable called File

ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & File & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=25"""

That should make it test the first 25 rows.

Be careful not to miss out any "s. 2 before Excel and 3 after the 25

IMEX=1 says to treat all columns as text; if some of them are dates or numbers or currency use IMEX=0 instead.

HDR=YES means the first row of your spreadsheet contains the column headers - leave out or set to NO if not.

Let us know how you get on.