6

I am writing ETL. I have created View in my source database. My View is a join of two tables. Now, I need to fetch data from View. But there are two columns in View which have nvarchar(max) data type.

But when I perform lookup operation in DFT, I am facing this error:

Cannot map the lookup column, 'Description', because the column data type is a binary large object block (BLOB).

I have seen following links:

  1. SSIS Lookup By NVARCHAR(MAX) Column
  2. SSIS Lookup with Derived Columns

Note that, Description column may have large amount of text.

Image is attached for reference. Thank You! Type Conversion Error

Community
  • 1
  • 1
Faizan Mubasher
  • 4,427
  • 11
  • 45
  • 81
  • 3
    you may need to pass that column through a Data Conversion transformation (from ntext to string) – Jayvee Mar 13 '14 at 09:19
  • I have tried it but getting same error – Faizan Mubasher Mar 13 '14 at 09:49
  • 1
    You have to convert nvarchar(max) to nvarchar(4000). It will work just fine.. – Maverick Mar 13 '14 at 13:31
  • As Maverick proposed, have you tried reading the view in your lookup transformation with a sql query like select ...,cast(description as nvarchar(400)) from view ? – Jayvee Mar 13 '14 at 15:18
  • If you have a key for these two table you could instead do a `MERGE JOIN` and then do a conditional split to compare those columns if that helps. – Zane Mar 13 '14 at 15:57
  • Actually! I created the View in relational database already. And SSIS gives you an option to read from View directly just like you read from table. And there is a cstr(...) function used for casting which is not working. I think nvarchar(4000) will do a work. And this is second limitation of SSIS that I have came accross. – Faizan Mubasher Mar 14 '14 at 06:08
  • @FaizanMubasher - only 2 limitations? You must be just starting with SSIS :-) – Mike Honey Mar 17 '14 at 02:34
  • @FaizanMubasher what you try to do isn't a *lookup*, it's a query. Lookup is when you load a table of eg customers or products so you can match incoming product or customer ids to actual names. That's why the lookup transformation has the ability to cache data. That's why it *doesn't* make sense to allow BLOBs that may be 100MB or 4GB large to be loaded into RAM and transferred along the pipeline – Panagiotis Kanavos Jan 17 '19 at 15:24

2 Answers2

5

What you is a lookup, and the lookup transformation supports join columns with any data type, except for DT_R4, DT_R8, DT_TEXT, DT_NTEXT, or DT_IMAGE (i.e. BLOB's)

Personally I try to avoid handling BLOB's as much as possible in SSIS. Convert and treat the BLOB as a nvarchar with a max value, and you should be fine.

OMG rainbows
  • 79
  • 3
0

You might get this problem if the column based on which you are comparing with in the lookup table have a been assigned different constraints.

Ex: If custid in source table allows NULL but the custid of target table does not allows NULL . you might get this error.

enter image description here

enter image description here

user2807083
  • 2,962
  • 4
  • 29
  • 37
Sukanya Sanyal
  • 1