DTS - 2 newbie questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS - 2 newbie questions

Author

Topic

H_E_Boy
Starting Member

2 Posts

Posted - 2002-09-24 : 12:13:27

Hi list,

I'm new to the list and fairly new to SQL Server 7. I've done the MS SQL Server 7 Admin course so that may give you an idea of the level I'm at.

Question 1: with DTS can I use a declared variable in an SQL statement? e.g. I have a table which requires daily update from an IBM AS/400 DB2 database. I want to refresh the table by getting the latest date from the SQL Server table (e.g. select max(date) from table1) and using it in the SQL statement I use to select the required data from the AS/400. Any clues on how to set up the DTS package for this? I want to do something equivalent to (I know the syntax is not right):

declare maxdate as string
select max(date) from SQLServertable
select col1, col2, col5 from AS400table where date > maxdate

Question 2: I have created some DTS packages as local. Can I now convert them to .DTS files?

TIA,

H.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-24 : 12:24:09

this will call an SP from a dts activex script task
You can find out from here how to exec sql code but I would advise SPs instead.

strConnectionString = "Provider=SQLOLEDB.1;Integrated Security =SSPI;Persist Security Info=False;Initial Catalog=" & DTSGlobalVariables("DatabaseName").Value & ";Packet Size=4096;Data Source=" & DTSGlobalVariables("ServerName").Value
set cn = CreateObject("ADODB.Connection")
set cmd = CreateObject("ADODB.Command")
cn.open strConnectionString
cmd.ActiveConnection = cn
cmd.CommandText = "spMySP"
cmd.CommandType = 4 'adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("RETVAL",3,2)
Set rs = cmd.Execute
set rs = nothing
DTSGlobalVariables("myval").Value = cmd.Parameters("RETVAL")

For the AS400 bit I take it you want to copy some data from a table.
Create a transformation with a query to the AS400 as the source then in the startup activex script change the source to a query including the parameter received.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

H_E_Boy
Starting Member

2 Posts

Posted - 2002-09-25 : 06:38:05

Thanks nr, appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources