1

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)

  • 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. – Kin Shah Jun 20 '13 at 20:31
  • That makes sense, but this is happening within SSMS, using the Import/Export tool, not SSIS. Is this answer agnostic and applicable to SSMS also? – Kris Gruttemeyer Jun 20 '13 at 20:32
  • SSMS on the background creates an SSIS package. At the end it will even allow you to save it as an SSIS package. – Kin Shah Jun 20 '13 at 20:54
  • Glad that it helped. Do you want me to post as answer ? – Kin Shah Jun 20 '13 at 21:34
  • Yes, please, ill accept it – Kris Gruttemeyer Jun 20 '13 at 21:51
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.