How to load data from Excel file to sql server table using only script task in SSIS.

Question

How to load data from Excel file to sql server table using only script task in SSIS.

p.shabbir

SSCrazy

Points: 2404
More actions
October 24, 2013 at 1:55 pm

#300455

Hi All,
Hope doing all well.
How to load data from Excel file to sql server table using only script task in SSIS?
I don't know coding. Please help me on this.
Thanks
Sibi.:-)

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

If you don't know coding, why would you want to do it with a script task if a data flow is the appropriate task for your situation?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

Thanks for quick response Luis.

I am just practicing.

I know we can do that by tasks but, i want to learn how to do using script task only. I googled it but, not clearly mentioned.

Please let me know how to do it?

Thanks.

rohit85m SSC Rookie Points: 42 More actions · Answer 3

HI

In order to load from Excel into SQL Server, you should have Bulk_admin database role rights on your ID with Insert permission on database.

you can use the below script to load data into sql server after that,

USE <DB_NAME>

GO

/** STEP 1 - CREATE TABLE **/ -- You need to create the table before inserting data with the columns mentioned in your excel

CREATE TABLE Load_Table(cust_no integer, cust_name varchar(20) )

GO

/** STEP 2 - BULK INSERT **/

BULK INSERT Load_table FROM 'FIle_location\file_name.xls' WITH (FIRSTROW = 2, FIELDTERMINATOR =',', ROWTERMINATOR ='')

GO

p.shabbir SSCrazy Points: 2404 More actions · Answer 4

Thanks for info rohit.But i want it in script task.