SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
Use the Transact-SQL function TRY_CONVERT (Transact-SQL) to surpress/ignore conversion failures.
How to fix data convertion error from string value to int in ssis
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 1%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA5%3C/text%3E%3C/svg%3E)
AvijitSwarnakar-5337
0
Reputation points
Currently, I am working on a task where I am using the "SSIS Integration Toolkit for Microsoft Dynamics 365" to fetch data from online dynamic CRM source and populate this to on prem CRM database(just a copy activity).
I am using dynamic crm source to pull data using "SSIS Integration Toolkit for Microsoft Dynamics 365" choosing source entity.
All records from this entity should be copied to on prem CRM database(destination).
But the problem here is source column from source entity has data type nvarchar(64) and destination on prem database column has int data type.
I have to change the source nvarchar value to int actually. So I am using a derived column to do this like the way in screenshot attached.
But after executing the package SSIS is throwing error like attached screenshot.
Considering my source column has various bad records and for those I want to populate database "NULL". It seems it is not going to the else part of the condition which is "NULL".
Is there any. change needed in the expression (see attached) to do the same ?
Could you please provide a solution for the same ?
SQL Server Integration Services
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
SQL Server | Other
SQL Server | Other
Additional SQL Server features and topics not covered by specific categories
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Yitzhak Khabinsky 27,196 Reputation points2023-09-20T18:21:36.6466667+00:00 Please edit your question, and add a screen shot of your expression.
-
AvijitSwarnakar-5337 0 Reputation points
2023-09-20T20:07:36.57+00:00 @Yitzhak Khabinsky , I have edited the question and attached a pdf copy of Screenshot.
-
Yitzhak Khabinsky 27,196 Reputation points
2023-09-20T21:08:57.33+00:00 I asked for SSIS Expression, not the errors.
And no need to attach PDF file, you can add a picture directly to your original question body. -
-
ZoeHui-MSFT 41,551 Reputation points2023-09-21T02:19:02.43+00:00 Hi @Avijit Swarnakar,，
First please show us your expression in the Derived Column.
Secondly, i didn't see the error thrown at the Derived Component, you may run the package and attach the error message in VS for us to narrow down the issue.
-
AvijitSwarnakar-5337 0 Reputation points
2023-09-21T02:52:18.99+00:00 Hi , In the question I have attached a file where error and deriverd column expression are there. Could you please check if you can view that! Else please check below and confirm if you are able to view. The error message and the derived column expression from VS I have copied and pasted to notepad ++ for better viewing.
Derived column expression:
Error message:
Sign in to comment
2 answers
Sort by: Most helpful
-
ZoeHui-MSFT 41,551 Reputation points
2023-09-21T06:36:29.3066667+00:00 Hi @Avijit Swarnakar,，
Add (DT_I4) before your expression to convert the string to int.
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.
-
AvijitSwarnakar-5337 0 Reputation points
2023-09-21T06:49:58.71+00:00 Hi @ZoeHui-MSFT
I tried with (DT_I4), but it’s throwing the same error. Any other alternatives?
Sign in to comment -