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

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

  • 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
  • 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.

  • 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

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

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

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