September 16, 2002 at 4:30 am
Hello,
I've got a simple DTS package that reads an AS/400 and writes to SQL Server 2000. I need to add a WHERE clause (or something) to limit some data with a date stored on the SQL Server. I've found many DTS lookup examples that affect all rows, but none with a simple WHERE clause. Here's my query:
SELECT Data
from AS_400
WHERE Date = Data_from_SQL_Server
In the transformation I can add a lookup that returns the correct value. I can't figure out how to do the '?' substitution in the source query and tie it to the lookup.
SELECT Data
from AS_400
WHERE Date = ?
Any pointers would be appreciated.
TIA
Don
http://www.biadvantage.com
September 16, 2002 at 4:37 am
Hello,
You say the date is stored on the SQL Server. Is this the system date on the server? Or is the filter date stored within a table?
Either way, you can do this. If you wanted today's data only you could use GETDATE() to return the date. However, with the JDE system we run on an AS400 the dates are stored in Julian Date format, so you need a small script for the conversion.
If the Date is stored within a table, then you would need a subquery. For Example.
SELECT Data
FROM AS_400
WHERE Date = (SELECT FilterDate FROM FltDateTable)
This assumes you have a table with one column.
If these scenario's are not what you have in mind, pls reply with how/where the date is stored :o)
Clive Strong
September 16, 2002 at 5:03 am
Thanks, here's some more info:
The date is stored in a table on the SQL Server. That's the difficulty (I think). My data source is the AS/400. The select statement has to use two data sources - AS/400 for the data and SQL Server for the date. That's why I thought a lookup was appropriate.
Don
http://www.biadvantage.com
September 16, 2002 at 5:29 am
OK...One idea I have is this..
If you know the format of the AS400 tables, you can create an ActiveX script which connects to the AS400 and also your SQL Server and downloads the data that way. We do this a fair bit.
You can call a function to return the filter date from your SQL Server table and when you query the AS400, use the variable in the WHERE clause. This can be done quite simply using VBScript.
However, there are probably other solutions (that I can't think of right now!)
Clive Strong
September 16, 2002 at 6:16 am
That sounds like a good idea. I'm a little weak in VBScript, though. Can you give an example?
Don
Don
http://www.biadvantage.com
September 16, 2002 at 6:17 am
That sounds like a good idea. I'm a little weak in VBScript, though. Can you give an example?
Don
Don
http://www.biadvantage.com
September 16, 2002 at 7:08 am
Sure.
Create a DTS and drag an ActiveX object onto the DTS pane. Open it up and you could create something like this. It's pretty basic and has no error trapping or anything.
Hope it helps!
'Global *Script* variables
Dim JDEConn
Dim SQLConn
Fim FilterDate
Function Main()
'Create and Open JDE Connection
Set JDEConn = CreateObject("ADODB.Connection")
JDEConn.Provider = "msdasql"
JDEConn.open "uid=JDELogin;pwd=JDEPwd;initial catalog=CatalogName.F42119LA;dsn=jdetest"
'Create and Open SQL Connection
Set SQLConn = CreateObject("ADODB.Connection")
SQLConn.open "uid=;pwd=;Trusted_Connection=Yes;driver={SQL Server};server=MySQLServer;database=TestDB;dsn="
'Call Functions
GetFilterDate
ImpRecords
'Kill Connection Objects
Set SQLConn = Nothing
Set JDEConn = Nothing
Main = DTSTaskExecResult_Success
End Function
Function GetFilterDate()
Dim SQLRS
'Create SQL Recordset Object
Set SQLRS = CreateObject("ADODB.Recordset")
'Query SQL to get the filter date for your AS400 query
SQLRS.open "SELECT QryFilterDate FROM FilterDateTable", SQLConn
'Store Filter date in variable
FilterDate = SQLRS("QryFilterDate")
GetFilterDate = DTSTaskExecResult_Success
End Function
Function ImpRecords()
Dim JDERS
'Create Recordset Object for AS400 Connection
Set JDERS = CreateObject("ADODB.Recordset")
'Query the table on the AS400
JDERS.open "SELECT SDLITM, SDADDJ, SDUORG FROM CatalogName.F42119LA WHERE SDADDJ = " & FilterDate, JDEConn
If Not JDERS.BOF AND Not JDERS.EOFThen
WHILE Not JDERS.EOF
'Put the resultset into variables
DTSGlobalVariables("SDLITM").Value = JDERS("SDLITM")
DTSGlobalVariables("SDADDJ").Value = JDERS("SDADDJ")
DTSGlobalVariables("SDUORG").Value = JDERS("SDUORG")
'Execute INSERT query to pass variables into SQL Table
SQLConn.Execute "INSERT INTO MyDownloadTable VALUES (DTSGlobalVariables("SDLITM").Value, DTSGlobalVariables("SDADDJ").Value, DTSGlobalVariables("SDUORG").Value)
JDERS.MoveNext
WEND
Set JDERS = Nothing
ImpRecords = DTSTaskExecResult_Success
End Function
Clive Strong
September 16, 2002 at 8:34 am
Thanks Clive. I'll toy with this code for a while.
Don
Don
http://www.biadvantage.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply