I'm uploading data from staging tables on an as400 to staging tables on
MSSQL. I want to set a status field in the source record to show whether the
upload was successful or not.

I think the multiphase data pump is the way to go, but I don't want to keep
opening connections to the as400 with each record. Is there a way to use one
connection for the whole tramsformation task ?

Any advice greatfully received.

Thanks.

But then are you not asking to do Row * Row processing? On large datasets
this will be a killer.

Alternatives are

Issue an update against the AS400 (ExecuteSQL task) after the DataPump task
that updates the same rows that your datapump chose

Use a linked server to do the updates matching on key values.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Duncan Allen" <DuncanAllen (AT) discussions (DOT) microsoft.com> wrote