Sql-server – SQL Import/Export defaults to byte stream on export

exportsql serversql-server-2012

So, I've done some research and I can't seem to figure this out. I'm not sure if it's some setting that I'm missing or what.

For the basic info, running SQL2012, 64 bit, all that good stuff. I noticed that, for some reason, when I export from a table into a flat file using a query, the data type is defaulting to bytestream. In the past, it always defaulted to DT_STR and went through without any issues.

Here's the query:

SELECT pin from IncrementalDM_Emails
where email in (select email from [7755_forsql])
order by pin

Here's the error I'm getting:

Error dialog

Here's what the export is showing when I select "Edit Mappings…"

Mapping dialog

Now, this can be easily fixed by simply selecting "DT_STR" under the Mappings dialog but I frequently export using this method so I'd like to find out why it's doing it and fix it so I don't always have to go into the Edit Mappings dialog.

Is it something to do with the query and the use of the IN function? Any help would be greatly appreciated!

EDIT: The data in both tables is stored as varchar(50)

Best Answer

When you retrieve or load data, SSIS tries to automatically convert it to the correct types. If SSIS can’t implicitly convert the data—and transforming the data within the package doesn't work—you might need to modify the XML mapping files (C:\Program Files\Microsoft SQL Server\110\DTS\MappingFiles -- folder), stage the data so it’s compatible with both SSIS and the data source, create a custom component that can retrieve or load the data, or implement a solution outside of SSIS to prepare the data.

SSMS on the background creates an SSIS package. At the end it will even allow you to save it as an SSIS package.

Related Question