November 18, 2005 at 8:20 am
I have DTS packages that import data from Progress databases into SQL databases. I want the pack to stop if the connection to the Progress db is not available. What makes it complex is I use global variables to define the database. Here is how the package flows:
1. DynamicProperitiesTask: Defines global vairable: ProgressDatabase
<insert database connection test here>
2. SQL Task: Truncate SQL table in corresponding SQL database
3. TransformDataTask: Import data from Progress to SQL tables
I want to insert a step before #2 that checks the connection to the database defined in the global variable ProgressDatabase. Could someone please advise on the ActiveX code or technique to use? Thanks.
smv929
November 18, 2005 at 9:15 am
Ah, a fellow Progress/SQL Server guy....I thought I was the only one! At my last job, I was a DBA for Progress, SQL Server, and Oracle. I have not had any experience moving Progress data into SQL Server, but it sounds to me like you could simply do a select from any of your Progress tables (or even one of the Progress _system tables). I assume that you are connecting to Progress via ODBC? ODBC should return an error if it cannot complete the select due to a lack of connection. If your first step is making the connection, your second step, the select, would verify that the connection was open.
November 18, 2005 at 9:57 am
This example ActiveX task VBscript does a check of the database using OPENQUERY. Note that it requires an ODBC DSN defined having the same name for each Progress database. This was already set up, so it works for me. So if the global variable (db) for the Progress database = corp, then
Select * from openquery(db,'select...')
will try to connect via the ODBC DSN named corp which has been predefined to point to the Progress database named corp.
If anyone can suggest a better ActiveX script, please do so. Thanks!
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim ADOcon
Dim strSQL1
Dim db
db = DTSGlobalVariables("ProgressDB").Value
Set ADOcon = CreateObject("ADODB.Connection")
ADOcon.Provider = "sqloledb"
ADOcon.Properties("Data Source").Value = "server"
ADOcon.Properties("Initial Catalog").Value ="db"
ADOcon.Properties("User ID").Value ="sa"
ADOcon.Properties("Password").Value ="password"
ADOcon.connectiontimeout=0
ADOcon.commandtimeout=0
ADOcon.Open strConnection
strSQL1 = " SELECT count(*) from openquery(" & db & ",'select * from SmallTable')"
ADOCon.Execute strSQL1
ADOcon.Close
Main = DTSTaskExecResult_Success
End function
smv929
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply