February 24, 2015 at 8:10 am
I've got an ssis package that will retrieve a bunch (45) of zipped text files from an ftp server, unzip them, and put them in a drop folder. Then with a series of dataflow tasks having a Flat File Connection and an OLE DB source, import them to a staging table.
Problem:
I set the datatypes on the flat file connection but they are reset to other values with fresh import of text files. The values are set based on the first 200 row sampling ssis does, and sometimes are set insufficiently eg: single byte signed integer when it needs 4 byte signed integer. Consequence; overflow errors.
is there a way to *freeze* the setting I make for each column's datatype? If the answer is Data Type Conversion Task, then I've tried that.
The flat files themselves will offer up varying datatypes for a certain column. Eg. right now there's a column that SSIS sometimes interprets as Float and sometimes as Unicode String....and if the Datatype conversion Task is expecting to a Unicode String for it's conversion to Float, but instead gets a float, then the package fails.
How to deal with the unpredictability of how SSIS will interpret the flat file's column datatypes?
February 24, 2015 at 8:30 am
Is there a chance that you can provide a sample package with two sample text files to demonstrate this behaviour?
It should not happen, in my opinion.
If you were using Excel source files, I might say differently ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 24, 2015 at 9:13 am
Ditto what Phil said.
We probably need a more complete understanding of what you are doing because the expected types should not be changing at runtime if you defined them in the connection manager.
March 2, 2015 at 5:57 am
Phil and Nevyn, thanks for reply.
With your vote behind mine, that this is non-logical behavior, I set up setting up bare bones package to deal with just the ingestion of two different versions of the source files. Set the datatypes inside the flat file connection manager. Then switched the connection manager to another version of the source file, having slightly different default datatypes for the same column. Yet, both worked as they should.
It was a good baseline from which to move forward with sense that it's got to be something else....and it was. In the real life example I have a master package that calls out child dtsx packages. Having worked on this project, I had two different folders with multiple copies of the child dtsx package and it turns out that my master package was pointed to different child dtsx packages then the ones I thought I was troubleshooting.
The appearance of datatypes being reset was actually just me getting errors for the child packages that were not the same ones as I was editing in response to those errors.
Ick. Glad you asked for sample.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply