Rachel Reese's blog

One Rachii's take on .NET, F#, Community, and some unrelated, potentially completely random things.

DTS Packages & Text Files don't play nicely. At least, not when *I* ask them to.

Hi Folks,

After just lurking for so long, and rabidly reading what everyone else has to say, I figured it was time for me to step in. 

And I have a problem that Google can't seem to solve. 

I created a DTS package that copies a text file into a database using the wizard, saved as VB, and upgraded to VB.NET (using http://www.sqldts.com/default.aspx?6,104,264,0,1.)  If I run the package as soon as I create it, from within SQL Server, it works perfectly. 

However, I'd like to run the DTS package from a windows service upon discovering a new file in a directory. I have the windows service all set up and working (side note: must talk more about the ease, practicality and general amazingness of windows services!) calling the .bas file as needed... but it fails every time, with the following:
-------------------
Package Steps execution information:

Step 'Copy Data from TheTextFile to MyDatabase Step' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Incomplete file format information - file cannot be opened.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0

Step Execution Started: 4/22/2003 12:20:46 PM
Step Execution Completed: 4/22/2003 12:20:49 PM
Total Step Execution Time: 2.937 seconds
Progress count in Step: 0
-------------------

I found several posts to the microsoft.public.sqlserver.dts group, but none that solved the problem:  I'm already using SQL Server 2k; and, I need to pass the DTS package the name of the file to import, so I can't run the package from SQL Server itself.  (I did check and the name of the file is being successfully passed.)

Anyone have any ideas?
Posted: Apr 22 2003, 12:58 PM by rachelreese | with 8 comment(s)
Filed under:

Comments

John Cavnar-Johnson said:

Are you sure the file is really there? If you're monitoring the directory for the creation of the file, you'll get the file notification before the file is actually finished writing.
# April 22, 2003 4:27 PM

Rachel Reese said:

Good idea!
I'm copying the file in there myself & I've limited my test file to two rows of data, so it should finish copying before it tries to run it, right? (I've tried it in debug mode, so there is an actual pause between the notification and the dts package start-off)...
# April 22, 2003 5:28 PM

Chad Osgood said:

I've had to deal with this very issue in the past, and since there's no notify filter for after the file has been completely written, I ended up polling the file for a specified interval attempting to allocate an exclusive lock. Once I acquired the exclusive lock it was safe to continue processing the file.
# April 22, 2003 5:38 PM

Rachel Reese said:

yeah, Chad -- that's along the lines of the solution I was beginning to concoct. I was thinking about checking the LastWriteTime every 30 seconds or so. If they matched, I'd be good to go. But your solution is much safer. Thanks!
# April 22, 2003 5:45 PM

prashanth. said:

Hi Please let me know if there is any solution to this...
even I am trying to run a dts package from .net.. the dts package picks up a flatfile.. I have given UNC path but.. whatever I do it shows a logon fail message

pacchy_star@yahoo.com
# September 25, 2003 5:54 PM

YVD said:

Here is explanation for the problem when DTS package that imports text file. It works from Enterprise Manager but generates weird error when exported as VB class and executed.

I have discovered that Microsoft has a BUG when exporting DTS package to VB class. Depending on what delimiter is specified for row and columns, it does not put necessary code or put wrong code.

I had file with row delimiter CR and “tab” as column delimiter. VB code was missing setting for row delimiter AND had incorrect code for row delimiter ( see my notes in the code).

Below is the code that must be in place in Sub Main for importing text file:

oConnection.ConnectionProperties("Mode") = 1
oConnection.ConnectionProperties("Row Delimiter") = vbCr ‘ THIS LINE WAS MISSING
oConnection.ConnectionProperties("File Format") = 1
oConnection.ConnectionProperties("Column Delimiter") = vbTab ‘ THIS LINE WAS INCORRECT
oConnection.ConnectionProperties("File Type") = 1
oConnection.ConnectionProperties("Skip Rows") = 0
oConnection.ConnectionProperties("Text Qualifier") = """"
oConnection.ConnectionProperties("First Row Column Name") = True
oConnection.ConnectionProperties("Max characters per delimited column") = 8000

# December 17, 2003 10:21 AM

sugithkumar said:

I'm getting the same error when I schedule the DTS thru SQL Server. When I manually run using SQL Enterprise Manager, everything is fine.. This is the error I'm getting...

=================

Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 ... Process Exit Code 1. The step failed.

============

Please help me if anyone knows the answer..

Thanks
Sujith
# December 18, 2003 7:37 AM

Chris said:

HMM im trying the same thing.. kinda.. my code works fine when executing in VB MDE, but i want it to work from ASP..

When i try this, i get:
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'oConnection.ConnectionProperties'

/act_Analyzer_Import.asp, line 141

Please email me if you have any clue
chriss@tentmakers.net
# March 22, 2004 7:19 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)