I am trying to make a decision on the approach to use for making some daily
updates from a SQL Server database to an Oracle database. I currently have
a DTS package pumping some data over to staging tables in the Oracle
database. I have also built a script to move the data from the staging
tables into the application tables.

I could certainly put the steps in the script below in one or more SQL
Tasks, but that doesn't seem like the best approach. It doesn't tell me how
many rows were inserted, deleted, or updated. It doesn't give me any
performance statistics on how long each step took.

We have a pretty nice UNIX scripting package. The down side is that I would
need to link my DTS loads of the staging tables to the UNIX script to
process those loads. Can anyone provide any advice? Yes, link them through
the scheduler -or- No, do them in DTS. Are there some examples out there to
show me how to do this in DTS? I have the WROX SS 2000 DTS book which has
been helpful, but I need more direct, specific examples of how to do this.
(A nice way to say I don't know what I am doing in DTS!)

The SQL script looks something like:

/* Step 1: delete products whose IPIS Record_ID is no longer present in the
staging table */
DELETE FROM PM_PRODUCT_RELATIONSHIPS
WHERE ...;

DELETE FROM PM_PRODUCTS
WHERE ...;

/* Step 2: delete contributors no longer associated with existing
IPIS-related products */
DELETE FROM PM_CONTRIBUTORS
WHERE contributor_id ...;

/* Step 3: update PM_PRODUCTS data for existing IPIS-related products */
UPDATE PM_PRODUCTS
SET ...;

/* Step 4: add new IPIS-related products (including relationship records) */
INSERT INTO PM_PRODUCTS (product_cd, product_date, name, last_change_uid,
last_change_date)
SELECT ...;

/* Step 5: add contributors. This list includes updates to preexisting
IPIS-related products as well as those added in Step 3 */
INSERT INTO PM_CONTRIBUTORS (
....;

Hi, Michael,

As my understanding of you question, you want to run some different batches
of SQL script in a DTS package, right?

Based on my knowledge, you could use the ActiveX Script Task to run the SQL
statements instead of using the Execute SQL task. In the DTS package
designer, you could add one ActiveX Script Task, it could use the ADO to
connect to the target database and execute your SQL script. Then code in
the ActiveX Script task is as follows ( you could also see this sample code
in the articles I recommended at the end of my reply) , which will connect
to your local database with sa and its password, then run T-SQL statments.
If the later steps are depends on the results of the former ActiveX Script
task, you could add workflow between different ActiveX Script tasks as 'On
Success'. You could get the information of the duration of each step and
other informations in VBscript programming scope.


'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* *********************

Function Main()

dim myConn
dim myRecordset
dim iRowCount

MsgBox "Start DTS Time: " & Now()

' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

' set the connection properties to point to the Northwind database,
' using the Customers table
myConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial
Catalog=Northwind;user id = 'sa';password='MyPassword'"
mySQLCmdText = "Select 'rowcount' = Count(*) from Customers"
myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowCount = Flds("rowcount")

MsgBox "End DTS Time: " & Now()

If iRowCount.Value = 0 then
Main = DTSTaskExecResult_Failure
Else
MsgBox "The number of customers is: " & iRowCount.Value
Main = DTSTaskExecResult_Success
End If

End Function

Please refer the following articles for more detailed informations:
http://msdn.microsoft.com/library/de...us/dtssql/dts_
elemtsk1_91yr.asp
Using ActiveX Scripts in DTS
http://msdn.microsoft.com/library/en...df_ax_06er.asp
DTS Package Workflow
http://msdn.microsoft.com/library/de...us/dtssql/dts_
elemwkflow_659z.asp


Hope this helps.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

If I want to use an ExecuteSQL task and count the number of rows that were
affected then in SQL Server I do this

SET NOCOUNT ON
DECLARE @i int
DELETE FROM TESTER WHERE Col1 = 1
SET @i = @@RowCount
SELECT @i as RtnVal

I can then map the "RtnVal" to a parameter.

I am not sure this works in Oracle though.


Another way would be to take the count of rows from the table before and a
count of the rows after. (Clunky)

How long did it take?

If you log to SQL Server for the package then DTS will log this for you

sysdtssteplog (elapsedtime)

perf stats you can then say "How many rows did I move/manipulate v time
taken". For ther types of perf info you will need to look at things like
Perfmon and Profiler.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Michael" <michael.kanyid (AT) pnl (DOT) gov> wrote

Hi, Michael,

I am reviewing you post now. Since we have not heard from you for some
time. I want to know if you still have question about this issue. For any
questions, please feel free to post message here and we are gald to help.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.