DTS Packages

[BKRIS]

I need some information to complete the following task.

Table A - Progress table from Merant ODBC
Table B - SQL database table equivalent

Current method:
Delete all data from Table B.
Create a Data Task to copy all Table A data to Table B.

Is there a way to add logic/workflow to step one to make sure a database connection is available before deleting all the Table B data. We use the SQL tables as dependents, so if there is a problem with the ODBC connection to the ERP system, the tables have no data at all.

[rmiao]

You can try copy table a to staging table on sql first then replcat table b with staging table. If copy table a failed, don't do anything.

[BKRIS]

The problem with setting up a staging table is that these tables are replicated every hour. We have 10-15 tables with up 50,000 records. Can't I use an Active X Script Task to read the table count before an on complete workflow to delete the existing records?

[andi_g69]

You can use an acticeX Script for this. But it also works without:

Create a SQL Query Task which connects using the ODBC connection as your first task. Do a simple query like SELECT Top 1 * FROM TableA

If this task succeeds continue with deleting TableB and copy the data accross as you do it already

Otherwise let the whole package fail and exit

[BKRIS]

If I use that as a first Task, the command returns regardless if the table is empty. I need a SQL command that will basically check to see if there are any records in the table and fail if the answer is no.

Thanks.

[andi_g69]

In this case do something like:

IF NOT EXISTS (SELECT * FROM YourTable) RAISERROR ('No data available', 16, 1)

This will cause the SQL Task to fail if there is no data in YourTable, otherwise it will return success and the packages continues running.

[BKRIS]

Thanks for the Code works great on SQL Tables. Is there a way to add logic to see if a database connection exists or not then complete a package? We connect to a Progress database that is unstable at times resulting in a shutdown. Our Merant ODBC Driver will not allow a SQL statement like the one suggested, maybe something like this below:

If NOT (Other Connection) Does Not Exist Raise Error

[andi_g69]

In this case an ActiveXTask is the better option. I assume you have a DSN created. In this case it should work like this:

Dim oConn

oConn.Open "DSN=yourDSN;Uid=yourUsername;Pwd=yourPassword "

oConn.Execute ("Select * From anytable")

oConn.Close

Set oConn = Nothing


If the connection fails the ActiveXTask will fail as well. If the connection was can estblished successfully your ActiveX will succeed and you can continue running your package.