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.:-)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 24, 2013 at 1:58 pm

  #1661455

  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

  October 24, 2013 at 2:06 pm

  #1661459

  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

  October 24, 2013 at 2:14 pm

  #1661463

  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

  October 24, 2013 at 2:36 pm

  #1661468

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