Sara4


I'm having some problems importing data from a memo column (Access) into varchar column in SQL Server.

My idea was to use slowly changing dimesion to identify modified and new rows. No matter what data type I use to convert the memo column (using Data Conversion Transformation) and then using the converted column in SCD, I get the following error :

'The SCD transform does not allow mapping between columns of different types except for DT_STR and DT_WSTR.'

What do I have to do to get Memo column 'to behave' as a string

Same problem with a different data type - decimal (18,5) in sql server - no matter what datatype I use in Data Conversion Trans, I get the same error trying to generate scd.

Thank you for your answers.




Re: Memo field da Access into varchar SQL server

Phil Brammer


Your field in SQL Server should be an NVARCHAR(MAX) to map directly to the MEMO field in Access. The data types inside the SSIS package for that field should be DT_NTEXT.






Re: Memo field da Access into varchar SQL server

Cho Yeung - msft

Memo type in Access is mapped to DT_NTEXT in SSIS. DT_NTEXT is considered a BLOB type in SSIS and some transforms (like lookup) do not support joining on BLOB type column.






Re: Memo field da Access into varchar SQL server

Sara4

When I modified my destionation column's type into nvarchar(max), the scd gives me this error - Slowly Changing Dimension : The input column x has a long object data type of DT_TEXT, DT_NTEXT, or DT_IMAGE which is not supported.

Does that mean that I can't use the scd to determine if the column x has been modified





Re: Memo field da Access into varchar SQL server

Phil Brammer

Sara4 wrote:

Does that mean that I can't use the scd to determine if the column x has been modified



Correct. It is not supported as per the post above.





Re: Memo field da Access into varchar SQL server

Sara4

My access Memo filed is, in fact, plain text. Is there any work around I'd like to confront my Access field and my SQL field in order to know if any modifications have been done. Is it possible





Re: Memo field da Access into varchar SQL server

Phil Brammer

Sara4 wrote:

My access Memo filed is, in fact, plain text. Is there any work around I'd like to confront my Access field and my SQL field in order to know if any modifications have been done. Is it possible

(Note, I restored this post because it is a valid question and has an easy workaround.)

We know it's a text field, but the way that text is stored inside SSIS is binary. (For the memo typed fields)

A workaround is to load a temporary table in SQL Server straight from Access with your data.

Then, in a second data flow, in the OLE DB source, write a SQL query to join the temporary table with your comparison table, performing the comparison inside the SQL.