August 12, 2010 at 9:18 am
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
August 12, 2010 at 9:44 am
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]
August 12, 2010 at 11:45 am
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.
August 12, 2010 at 11:52 am
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]
August 12, 2010 at 12:03 pm
Hi,
Thanks for your prompt reply.
Could you provide me the steps on how to do this in SQL Server agent ?
August 12, 2010 at 12:24 pm
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]
August 12, 2010 at 12:29 pm
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 ?
August 12, 2010 at 12:34 pm
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...
[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]
August 12, 2010 at 11:33 pm
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."
August 13, 2010 at 8:46 am
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]
August 13, 2010 at 8:57 am
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 !!
August 13, 2010 at 9:12 am
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]
August 13, 2010 at 9:16 am
thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply