Issue regarding stored procedure

  • Hi Everyone,

    I am having a problem regarding insertion of excelsheet values to a table in SQL database through a stored procedure only.

    Here it goes,

    First, I have created an excelfile by the name of "importdata.xls".

    the values which i have put in it are as given below.

    bank_code bank_transaction money_trn check_num

    100 15000 12360 672249

    200 25000 4354 873435

    300 45000 9999 75466

    400 65000 333 8523

    Second, in SQL Server Business intelligence development studio, I have created a data flow task, which has an excel source and an OLE DB destination, in the excel source i have given the above excelsheet name and in OLE DB destination I have given the table name where the excelsheet data has to be inserted i.e. "dbo.import_data2". The name of the package is "Package.dtsx". When I am doing it through this method the data gets inserted into the "dbo.import_data2" properly.

    But the problem is when I am doing the same operation through a stored procedure it is not happening.

    See the given procedure below.

    ALTER procedure dbo.import_data

    as

    declare @sql nvarchar(50),

    @Bank_code numeric(35),

    @Bank_transaction numeric(35),

    @Money_trn numeric(35),

    @Check_num varchar(25)

    --as

    set @sql = ''

    set @sql = 'exec C:\Documents and Settings\varunj\My Documents\Visual Studio 2008\Projects\Integration Services Project2\Integration Services Project2\Package.dtsx'

    --exec (@sql)

    --select * from import_data1

    --insert into import_data1 values (400,65000,3256,8523)

    --truncate table import_data2

    --select * from import_data2

    --delete from import_data2 where bank_code = 400

    --insert new records

    insert into dbo.import_data2 (Bank_code,Bank_transaction, Money_trn, Check_num )

    select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num

    from dbo.import_data1 a left join

    dbo.import_data2 b

    on a.bank_code=b.bank_code

    where b.bank_code is null

    --select * from dbo.import_data2

    -- update existing records

    update dbo.import_data2

    set money_trn = x.Money_trn

    from

    (select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num

    from dbo.import_data1 a join

    dbo.import_data2 b

    on a.bank_code=b.bank_code) x

    where import_data2.bank_code = x.bank_code

    --clean up import_data1 (temp table)

    truncate table dbo.import_data1

    exec (@sql)

    GO

    The work that this procedure must do is, whenever an insertion and updation changes are made to the excelsheet, the changes must be reflected in the "dbo.import_data2" table using the "package.dtsx" file. (i need to execute "package.dtsx" through the stored procedure)

    Please provide me the solution on how to go about accomplishing this task or if there are any flaws in the code please provide me a proper code regarding this issue.

    Thanks and Regards

  • set @sql = ''

    set @sql = 'exec C:\Documents and Settings\varunj\My Documents\Visual Studio 2008\Projects\Integration Services Project2\Integration Services Project2\Package.dtsx'

    --exec (@sql)

    You can't run the SSIS package using exec command. You need to call dtexec and run it using xp_cmdshell command.

    The work that this procedure must do is, whenever an insertion and updation changes are made to the excelsheet, the changes must be reflected in the "dbo.import_data2" table using the "package.dtsx" file. (i need to execute "package.dtsx" through the stored procedure)

    First, how you are planning to execute the SP? Meaning how do you capture whether the excel sheet is modified or not and call the SP accordingly?

    If you don't want instant update, then there is no need for SP and you can execute the SSIS package using SQL Agent Job or so and run it on daily basis.

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Hi,

    In regards to running the SP, here's how i plan to do it.

    - I can update or insert any row in the excelsheet and then save it.

    - Next I will execute the SP by pressing f5.

    -then the changes should be reflected in the table.

  • river22_34 (8/12/2010)


    Hi,

    In regards to running the SP, here's how i plan to do it.

    - I can update or insert any row in the excelsheet and then save it.

    - Next I will execute the SP by pressing f5.

    -then the changes should be reflected in the table.

    Then, it is a manual process. There is no need to perform this in SP. You can just schedule the SSIS in SQL Agent Job or run it by right clicking on the package in the network folder whenever the excel sheet is updated.

    Also if you have some more sql which you need to execute after executing the package you can add those directly in the package using "execute sql" task after data flow task completes.

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Hi,

    Thanks for your prompt reply.

    Could you provide me the steps on how to do this in SQL Server agent ?

  • river22_34 (8/12/2010)


    Hi,

    Thanks for your prompt reply.

    Could you provide me the steps on how to do this in SQL Server agent ?

    1. Connect to the sql server database engine from where you want to execute the ssis.

    2. Expand Sql server agent.

    3. Create a job by right clicking on the Jobs.

    4. Create a step in the job by click the new button.

    5. Select type as "sql server integration services packages" and package source as "file system".

    6. Select other parameters as appropriate to your package.

    7. Save the job and run it by right clicking on it whenever u need.

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Hi,

    can you also provide me the code on how to call dtexec in a stored procedure and run it using xp_cmdshell command . The entire code ?

  • river22_34 (8/12/2010)


    Hi,

    can you also provide me the code on how to call dtexec in a stored procedure and run it using xp_cmdshell command . The entire code ?

    I found this in internet...

    http://geekswithblogs.net/stun/archive/2010/02/24/execute-ssis-package-from-stored-procedure-with-parameters-using-dtexec-utility.aspx

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Hi,

    I am getting this message when i executed this SP by using xp_cmdshell

    "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'xp_cmdshell'. The stored procedure will still be created."

  • river22_34 (8/12/2010)


    Hi,

    I am getting this message when i executed this SP by using xp_cmdshell

    "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'xp_cmdshell'. The stored procedure will still be created."

    This is more like a warning message. Are you calling xp_cmdshell like this? If not please modify like below...

    EXEC master..xp_cmdshell

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Hi,

    Will this command work if I am in another database for ex, If I am in "SQL_Training" database will it get executed in this database ? or do i need to copy the sp for xp_cmdshell from master database and run it on

    SQL_Training database.

    Thanks !!

  • river22_34 (8/13/2010)


    Hi,

    Will this command work if I am in another database for ex, If I am in "SQL_Training" database will it get executed in this database ? or do i need to copy the sp for xp_cmdshell from master database and run it on

    SQL_Training database.

    Thanks !!

    Yes. It would. The reason is entering the database name is to point out that the SP reside in that database.

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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