I have been given the wonderous task of working out how to import multiple
data files into a SQL DB (appx 250 per day)

The Files are of different sizes, number of rows, columns and formats but
have the same header row which defines what is in the file. I've sussed all
the import stuff but have an issue with the file source

I have made a DTS pakage that uses dynamic a property to determine the path
for the Text file source.

When I run the package from within designer, the first time it fails :
------------------------------------------------------------
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset
Provider
Step Error Description:Error opening datafile: The system cannot find the
file specified.
Step Error code: 80004005
Step Error Help FileTSFFile.hlp
Step Error Help Context ID:0
------------------------------------------------------------

When I run it the second time, without changing anything,it works just fine.
This is a continuois process . Run1 fail - run 2 ok, run 3 fail , run 4 ok
etc...

The package determines the file to be imported each time it runs, and if
successfull in importing, deletes the source file. I have a sepaerate process
that populates a SQL table with all the deatails of the files to be imported
this is used to populate the Dynamic Property using a stored proceedure call
that returns a single full file path.

Permissions are not an issue (I believe) as it is all set up and running
under administartor (I know thats bad but I'll tie down security later)

The odd thing is that the package claims it can't find the file on the first
run, but finds it fine on the second.

Any suggestions greatfully welcome

Many thanks.

The Dynamic Properties task is set to run before the data pump? Use workflow
constraints to ensure this happens. If any other tasks use that file
connection, make sure they have close connection on completion set otherwise
a change to the connection will not register until is closed, then reopened
to read the new info.


Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Jinx1966" <Jinx1966 (AT) discussions (DOT) microsoft.com> wrote

Thanks for the info Darren.

I already have success and fail precedences in place and close connection
property set.

Dynamic properties >> pass goes to SQL tsk to truncate destination SQL table

Any fails send a corresponding e-mail alert.


I get this same problem even if I run the package 1 step at a time. fails on
the first run through but is ok on the second !

Anyway I've given up trying to do it the easy way using MS helpful tasks and
will revert to Active X as inspired by http://www.sqldts.com/default.aspx?246
as recommended on other discussions

Again thanks for the advice.

Graham



"Darren Green" wrote: