Data Type Conversion causing error in SSIS Package

Question

I have an SSIS package that has a step that creates a temp table using a Execute SQL Task.

Inside of this query I have a case statement that is something like:

Cast(Case
    When billing_address is Like '%DONOTUSE%' Then 1
    When billing_address is Like '%DONTUSE%' Then 1 
Else 0
End as nvarchar)DoNotUseAccounts

I have an update statement in a different Execute SQL Task that is like this:

Update #StatementAccounts 
Set Issues = Issues + ' - Do Not Use Account' 
Where Product In ('prod1','prod2','prod3','prod4') 
     And DoNotUseCustomer= 1

When executing the package I am receiving an error: "Error: String or binary data would be truncated."

Am I using the wrong data type?

Does the Update statement need to be cast/converted as well?

Any guidance would be helpful.

I have tried using datatype int, numeric, and Casting the update statement as an int as well.

you don't have a length on nvarchar something like nvarchar(100) — KeithL, Jul 17 '19 at 19:59
if you have any Issue that is already at max length of datatype, this will blow up because you are adding len( ' - Do Not Use Account') chars to the end of it — KeithL, Jul 17 '19 at 20:02
the fix goes back to creating the temp table and extending the length of Issues — KeithL, Jul 17 '19 at 20:04

score 1 · Accepted Answer · answered Jul 17 '19 at 20:21

You have one of two possible issues here.

1) You have an explicit CREATE TABLE #StatementAccounts statement where you're defining Issues as NVARCHAR with no length specified, in which case it's one character, or with a length that's too small to accommodate the additional characters that you're trying to append with your UPDATE statement.

FIX: Make the declaration at least len( ' - Do Not Use Account') characters longer.

2) Much more likely from the sound of things, you're using a SELECT...INTO #StatementAccounts statement and letting SQL Server define your data types for you. In this case, it's setting Issues to be just big enough to accommodate the largest value in that initial statement.

FIX: Issue an explicit CREATE TABLE #StatementAccounts statement and declare appropriately sized data types, then change the SELECT...INTO to an INSERT INTO.

It worked! Your assumption was correct, I did not have a defined length for Nvarchar in "Issues". Thank you! — vinylmike, Jul 17 '19 at 22:44

Data Type Conversion causing error in SSIS Package

1 Answers1