GregsListAcct


I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."

Seems simple, right

I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import

Does anyone else find this surprising I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.




Re: Exported flat file data will not import to same table without extensive data-type manipulation

Phil Brammer


Just out of curiosity, why the need for a flat file

If it is just a staging file, Raw files would be a better choice. But then I'd try to get an SSIS data flow to just transport the data across servers.






Re: Exported flat file data will not import to same table without extensive data-type manipulation

GregsListAcct

Actually I am switching to raw files, but the conversion issue just bugged me to the point of posting.




Re: Exported flat file data will not import to same table without extensive data-type manipulation

Phil Brammer

Hmm... I don't like that at all. A bit column stores "bits" that is 0s or 1s. TRUE/FALSE is a presentation layer concept. If SSIS is exporting bit columns and not using 0s or 1s, I see that as a bug.





Re: Exported flat file data will not import to same table without extensive data-type manipulation

GregsListAcct

I was surprised as well, but if you go through the generic export wizard from Management Studio with a table that has bit data types, and opt to save the DTSX package, you'll see the DTSX package defines the columns as DT_BOOL. The data exports without error, but it will have "FALSE"/"TRUE" values in the column vs. 0/1. I'm sure this is considered "by design", it's just really odd and inconvenient.



Re: Exported flat file data will not import to same table without extensive data-type manipulation

Phil Brammer

GregsListAcct wrote:
I'm sure this is considered "by design", it's just really odd and inconvenient.


It shouldn't be -- that's my point. A boolean value is not "TRUE"/"FALSE", it's 1/0.





Re: Exported flat file data will not import to same table without extensive data-type manipulation

Bob Bojanic - MSFT

I do not see a problem with persisting True/False values to flat files and loading them back.

I tested this usin a small SQL Server table with a bit column. I exported this table to a flat file. Then I used the same flat file connetion to import this file to another SQL Server table (with the bit column). It all worked as expected.

SSIS presents DT_BOOL values using True and False literals to represent 1 and 1. Those values are also written to flat files and they can be read back to DT_BOOL columns. 0 and 1 values are still used internally in the data flow buffers and they can be saved back into bit columns.

Thanks,

Bob






Re: Exported flat file data will not import to same table without extensive data-type manipulation

foxjazz2

ssis comes with bulk copy bcp built in. However you have to manipulate the true\false values and convert to signed int. Then you have to multiply the 2 together to eliminate any negative values.

Use derived column tool for this. You will need 2 of them.

ssis does not handle large quantities of data very well, and bulk copy is necessary when dealing with large ammounts of data.






Re: Exported flat file data will not import to same table without extensive data-type manipulation

Gert-Jan van der Kamp

Has anyone figured out how to export bit values as 1/0 to a text file

Now i have to cast them to int on the database and export that to a file. Seems there should be an easier way. I have to export data and send them to a client how wants 10 instead of true/false.

Regards,


GJ





Re: Exported flat file data will not import to same table without extensive data-type manipulation

jwelch

You can use a Derived Column transform to translate the values.