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 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.