DTS_E_FLATFILESOURCEADAPTERSTATIC_CANTCONVERTVALUE

Question

I'm running an SSIS package that I made a few months ago, and ran into an odd error.

The package loads data from a tab-delimited file that's exported from an excel worksheet. Errors are redirected to an error table, which is later serialized to an output file.

With my most recent attempts to load these files, every row is rejected with the DTS_E_FLATFILESOURCEADAPTERSTATIC_CANTCONVERTVALUE error code and a column number that doesn't exist in the input file (there are 13 rows on the input, the error column is 187.

I figure that there's something not exported to csv properly, but I'm at at a loss to explain what it is. I've looked at the file, and it has the proper encoding. In addition the SSIS package builder generates the preview correctly.

When have you run into this error before, and what solutions/workarounds did you find that worked?

Some details about the execution environment: package run via dtexec, 2 parameters set on the command line. One is the working folder for the package, the other is the file name. The data is loaded into a SQL Server 2005 database.

Thanks for the help :)

Answer 1

Zach,
Good question, when I first started with SSIS this would happen to me all the time and there is hardly any information on why this happens. What I found is that if you delete the Flat-File/Excel Import component and the actual file from the datasources list on the bottom and then re-add it you can often correct this issue.

As I mentioned before, I am not entirely sure what causes the preview to get out of whack with what is happening but I suspect it may have something to do with the ID keys assigned to different components (just pure conjecture though).

Answer 2

Figured out what the error was: I was passing parameters on the command line improperly.

I was running DTEXEC as follows:

> dtexec /f "C:\Path\to\Package.dtsx" 
   /set \package.Variables[User::InputFileName].Value;"filename" 
   /set \package.Variables[User::WorkingDir].Value;"C:\working\dir\"

Either DOS or SSIS was parsing the User:WorkingDir variable incorrectly... it interpreted the backslashes within the path as escape sequences, not as path components. Updating the dtexec line to escape each backslash fixed the issue:

> dtexec /f "C:\Path\to\Package.dtsx" 
   /set \package.Variables[User::InputFileName].Value;"filename" 
   /set \package.Variables[User::WorkingDir].Value;"C:\\working\\dir\\"

(line breaks added for clarity)

It pains me when I miss the blatantly obvious ;)