Re: Dynamic Properties and DTS Source Text File
- From: "Jinx1966" <Jinx1966@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 6 May 2005 02:28:19 -0700
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
>> pass >> text file souce >> trans (col copy) >> SQL DB >> pass run SQL
stored proc to take data from import table and move to required locations.
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:
> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:3C434CFD-0EE0-4688-A0AB-73DDD6028F42@xxxxxxxxxxxxxxxx
> > 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 File:DTSFFile.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.
> >
> >
>
>
>
.
- References:
- Dynamic Properties and DTS Source Text File
- From: Jinx1966
- Re: Dynamic Properties and DTS Source Text File
- From: Darren Green
- Dynamic Properties and DTS Source Text File
- Prev by Date: Re: Problem in Exporting table data to a flat file using DTS Packa
- Next by Date: RE: DTS in SQLServer 7
- Previous by thread: Re: Dynamic Properties and DTS Source Text File
- Next by thread: DTS package not picking up changes to UDL file
- Index(es):
Relevant Pages
|
