Transfer AS400-->SQL Server with dynamic table names

Question

Post reply

Transfer AS400-->SQL Server with dynamic table names

Philippe H

Valued Member

Points: 72
More actions
April 19, 2005 at 5:33 am

#74912

Hi everyone,
I couldn't find any solution to this simple problem... I think I'm missing something trivial. Any help would be most appreciated !
The problem is basically:
Using a DTS that runs every night, how can I transfer files from an AS400 to a SQL Server, knowing that the name of the files are changing every night and one table on the AS400 contains the new names ?
For example:
On April 16th, the files AD001, CM008 and ER004 were loaded in three new tables AD001, CM008 and ER004 on the AS400.
The log table will therefore contains the values (FileName, Date):
AD001 16/04/05
CM008 16/04/05
ER004 16/04/05
Later during the night, I launch a DTS on SQL Server that reads the log table and retrieves these three file names (AD001,CM008,ER004).
The problem is, how can I transfer these files from the AS400 to SQL Server ?
I tried the "Transform Data Task", but it doesn't accept dynamic table names (changing every night).
Same problem if I try the "Execute SQL Task", because SQL-DB2 doesn't accept dynamic either.
I didn't try the dynamic properties yet... but I guess the problem would remain the same. And I don't know if Active X scripts could solve that.
Anybody has an idea ?
Thanks
Philippe

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

esserm Old Hand Points: 340 More actions · Answer 1

Hi,

you can run an Active X script prior to the execution of the data pump to change the table name.

Example:

'**********************************************************************

' Visual Basic ActiveX Script

'

' Replace the Placeholder ##Supplier## in the "WHERE" clause in the data pump task

'************************************************************************

Function Main()

' Get the root parent reference to the DTS package

Set oPkg = DTSGlobalVariables.Parent

' Get reference to data pump task1

Set oExecSQL =oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

'Retrieve current SQL statement (with placeholders ##Supplier##) and save to lokal Working Variable

mySQLStatement = oExecSQL.SourceSQLStatement

' Copy the local working variable to Global for later restore use

DTSGlobalVariables("original_my_SQL_DPT_1").Value= mySQLStatement

' Start the find and replace operations to substitue the "placeholder" in the SQL statement

mySQLStatement = Replace( mySQLStatement, "'##Supplier##" , DTSGlobalVariables("Supplier_Number").Value & ")" , 1 , -1, 1 )

' Assign SQL Statement to Exec SQL Task

oExecSQL.SourceSQLStatement = mySQLStatement

' Clean Up

Set oPkg = Nothing

Set oExecSQL = Nothing

Set mySQLStatement = Nothing

Main = DTSTaskExecResult_Success

End Function

The global variable "Supplier_Number" has to be created e.g. when you read from the log table.

You also can use the stored procedure sp_executesql to modify an SQL statement prior to its execution.

Hope this helps.

Matthias

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

It would be much easier not using DTS at all. First setup the AS/400 as a linked server. Then write a stored procedure that reads your log able and, in a For loop, retrieves the tables and loads them into your tables. Then create a job to run the stored procedure.

Mark

Philippe H Valued Member Points: 72 More actions · Answer 3

Hi,

I finally used your method Mark.

I just created the linked server and made a stored procedure which basically contains:

declare @ToLoad table (FileName varchar(10))

insert into @ToLoad

select

FILEID -- FileName

from

openQuery(LINKEDSERVER, 'SELECT FILEID FROM "LIBRARY"."LOGTABLE")

Therefore I can use dynamic SQL on an AS400. Brilliant !!

Thanks a lot

Philippe

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

Mark Harr

SSCrazy Eights

Points: 9921

April 22, 2005 at 6:08 am

#554319

Glad I could help.

Mark