Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
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 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 Jun 20 '13 at 20:54
    
Ahhhh, ok. Thanks so much! –  Kris Gruttemeyer Jun 20 '13 at 21:32
    
Glad that it helped. Do you want me to post as answer ? –  Kin Jun 20 '13 at 21:34
show 1 more comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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