October 18, 2010 at 12:04 pm
Hi ,
I have a stored procedure which sends results to oracle table on daily basis using linked server.since it is not giving us the best results because of the network problems we have decided to send FTP the results using ssis package.but the stored procedure i have created is using many parameters could any one help me how should i proceed by seeing the stored procedure below:
CREATE procedure [dbo].[TransferToOracle]
(
@PlanID int
)
as
Declare
@ID int,
@RowCount int,
@TotalUnits int,
@PlanCode varchar(10)
select @PlanCode=PlanCode from dbo.tableA where PlanID=@PlanID
--Determine the PlanCode using the planid parameter.this tableA is in different database compared to other tables
create table #temp
(
ID int
)
Insert into #temp exec dbo.tableB @planID = @PlanID --The sequence number returned from the stored procedure will be inserted into temp table.
select @ID=ID from #temp -- Capture the ID in a variable @ID
-- determine the number of rows for each batch by ID.
select @RowCount=count(*) from dbo.tableC
where ID=@ID
group by ID
order by ID desc
-- determine the number of units for each batch by ID.
select @TotalUnits=sum(cast((Quantity * perc / 100.0) as int))from dbo.tableC where ID =@ID
--Insert a row into the OracleTable when the transfer started by ID.
Insert into OracleTable
(SEQ_NUMBER,NAME,STATUS,START_TIME,RECORD_COUNT,TOTAL_UNITS,SOURCE_TYPE)
values(@ID,' Data Transfer from SQLServer','Transfer Started',getdate(),@RowCount,@TotalUnits,@PlanCode)
exec Procedure @ID = @ID
-- this procedure will send the data to oracle using linked server .In this case i need to just select the results ex:select * from dbo.tableABC
instead of executing the stored procedure which will send the results to oracle table using linked table.
Finally i need to update the OracleTable Status again to Transfer Completed for that @ID .
Could anyone please help me how should i proceed with all these steps in a package (its urgent).I have tried by creating variables in a package but it is giving me errors saying:
Parameters cannot be extracted from SQL command .the provider mayn't help to parse the parameter information from the command.In this case use the "SQL Command from variable" access mode.
Thanks in advance.
October 19, 2010 at 11:43 am
I have tried using execute task in SSIS and succeeded till half .
I did this :
i have dragged execute task and in the sql statement i wrote this :
Declare
@ID int,
@RowCount int,
@TotalUnits int
drop table dbo.temp
create table dbo.temp
(
ID int
)
Insert into dbo.temp exec procedure name @planID =39
select @ID=ID from dbo.temp
select @RowCount=count(*) from table A where ID=@ID
group by ID
order by ID desc
select @TotalUnits=sum(cast((Quantity * perc / 100.0) as int))from table B where ID =@ID
Insert into "OracleTable"
(ID,NAME,STATUS,START_TIME,RECORD_COUNT,TOTAL_UNITS,SOURCE_TYPE)
values(@ID,' Data Transfer To Oracle','Transfer Started',getdate(),@RowCount,@TotalUnits,'snop')
followed by a data flow task which does this in Oledb Source :
select * from
Table ABC
where ID in(select top 1 ID from dbo.temp order by ID desc) -- this is the table i have created in execute task
followed by a another execute task :
Declare @ID int
select @ID=ID from dbo.temp --this is also the same table i have created in first execute task.
Update "OracleTable"
set
STATUS='Transfer Completed',
COMPLETION_TIME=getdate()
where ID=@ID
Now how can i use the ID column in dbo.temp table created in execute task as variable and use it in dataflow task(oledb source) and the in the next execute task?i can do this by creating temporary table in the database just like i did the problem is i can not drop the table since i use it in oledb source,so i want to create and use a variable ..
my other question is i have flat file as destination in the data flow task .i want the flat file to be cleared whenever i want to run the package .
if the package runs today i will have today's data and again if i run the package tomorrow i should only see tomorrow's data (not both today and tomorrow).
October 19, 2010 at 12:09 pm
There's a checkbox in the flat file destination that determines whether the data is overwritten or not.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 19, 2010 at 12:44 pm
Yes ,you are right i found it .thank you very much.
Could anyone also please tell me how to use variable like i asked in my previous post..
October 20, 2010 at 4:24 pm
You mentioned using linked servers, I think that really was the first problem and really FTP is an answer but not the best. Did you consider using SSIS to pump the data directly to Oracle, linked servers are great for discrete queries but pumping a lot of data into Oracle it isn't..
CEWII
October 25, 2010 at 11:39 am
No ,using SSIS i am only sending FTP not the data directly to oracle tables.
Could you explain me how to do that?i could try that too
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply