Home Forums SQL Server 2005 SQL Server 2005 Integration Services How to load data from Excel file to sql server table using only script task in SSIS. RE: How to load data from Excel file to sql server table using only script task in SSIS.

  • 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