I'm doing some dynamic assignments of a datapumps SourceObject, which
happens to be a file.

Here is some background. My process uses activeX to look at the file
contents of a folder and assigns a file as the datasource of a text
connection. This connection feeds a datapump that has been setup with
its transformations in advance because they are the same for every
file.

So the process grabs the first file and assigns it as the source for
the 'file connection' which the datapump uses as a source.

When the process runs, the datapump moves the contents of the file into
a table, then the current file is moved into a separate directory.

The process repeats, selecting the next file. Here is where the
problem occurs. When the package loops back and sets the connection to
the new file, it gets to the datapump step and returns a 'cannot find
file' error.

What is truly odd is, if I open the datapump and look, the filename is
specified correctly, and once I close the datapump I can actually run
it manually!

Driving me crazy, anyone have *any* ideas?

Edward Hunter

Have you used this article?

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)


Make sure you set "Close Connection On Completion" in the workflow
properties of the DataPump task

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


<erhunter (AT) gmail (DOT) com> wrote