DTS Advice/Help
-
I have a DTS package that copies data from a SQL view to a staging table in an Oracle db.
I need to confirm the delivery of data to the Oracle db. So I created some simple code to take the count of the SQL view and the count of the Oracle staging table and take the difference between them and store this all in a SQL table. That code is as follows:
declare @sct int, @oct int, @dct int
set @sct = (select count(*)from Table1)
set @oct = (select count(*) from OracleDB..USR1.STAGINGTABLE)
set @dct = @sct-@oct
insert into feedstatus
values(@sct,@oct,'Table1',@dct,getdate())
At this point, I'm sort of lost. First off, the above code works fine in Query Analyzer, but I'm not sure how to wrap it as a step in the workflow in the DTS package.
Also, I'd like for the DTS package to then check the difference value in the FeedStatus table and see if it's 0. If it's 0, then I'd like for the DTS package to copy the data that's in the staging table and put it into a load table in the Oracle db. If it's not 0, then I'd like for the DTS package to not copy the data from staging table to the load table. I'm not sure on the code for the "if it's 0, do this; if it's <> 0, do this". And I don't know how to wrap that as a step in the workflow of the DTS package.
Also, is it possible to do what I want to do and put it all in one DTS package? And is there a better way of doing what I'm trying to do? I'm open to any and all suggestions. Apologies in advance if the above was too crazy/confusing.
-
You can put your sql into an ExecuteSQL task, using the SQL Server connection as your connection.
To deal with your other issue, you will need to use global variables and output parameters. First create a global variable (integer type)
You add one more line of code:
select @dct as Results
Then set the output parameter (click parameters, go to Output tab). http://www.sqldts.com/234.aspx describes the process.
On the transformation (or executesql) that transfers the data from staging to the main table, I would use an element of workflow that is hidden away. Right click the task that you want to execute or not based on the results from @dct. Click on Workflow, Workflow properties. On the second tab (Options) check the "Use ActiveX Script" checkbox and click the properties button.
Replace the existing code with the following:
if DTSGlobalVariables("Now").Value = 0 then
Main = DTSStepScriptResult_DontExecuteTask
else
Main = DTSStepScriptResult_ExecuteTask
end if
Russel Loski, MCSE Business Intelligence, Data Platform
-
Hi Russel - Just wanted to report back that your suggestions worked! After much travail, I got the DTS package to work as you described. Thanks again for your input.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply