During our execution of one of SSIS that has multiple tasks we have encountered a case of one task fails that runs SQL server command , the weird thing was this tasks executes with no errors on SQL server management studio but fails on SSIS!
After reviewing the SQL commands for this tasks , it only inserts records on database ,and when we execute the same command on SQL management studio it runs successfully
After trail and error we reached to the root cause of the issue which was the “$(“ in the text value
The insert statement looks like
INSERT Table Name(Column name)
VALUES ( N’J.B. BODA & CO. PVT. LTD. NT$(TAIWAN DOLLAR)’)
adding a space between the dollar sign and the bracket will solve the issue