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 expectthat what SSIS exports, it can importgiven 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.

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.

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

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.

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.

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.

I do not see a problem with persisting True/False values toflat 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 represent1 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

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.

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

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

What is the exact expression in the Derived Column transformation? I'm still getting this on my bit field - and it is a DT_BOOL in the SSIS package [OLE DB Destination [5577]] Error: There was an error with input column "Copy of UserProviderMatch" (6108) on input "OLE DB Destination Input" (5590). The column status returned was: "The value violated the integrity constraints for the column.".

use 1 for true and 0 for false. In many cases you can transfer the data int a table using text fields, then insert into a like typed table convering the field data to the typed values. If you need to this will work also. There are a hundred different ways to do what you need.foxjazz