Issues when using SSIS Execute SQL Task to insert a row into SQL Server with multiple parameters

Question

I am trying to use SSIS to insert a row with multiple parameters but I get this error :

[Execute SQL Task] Error: Executing the query "insert into ids (id,sqljobname,ojobname,ojobstartd..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is how I set up the Execute SQL Task:

insert into ids (id, sqljobname, ojobname, ojobstartdate)
values (?, ?, ?, ?)

Please let me know where I set it up wrong. I don't think the ParentJobStartDate is an issue because I set it up with string.

I'm not sure how does it work, but you might miss the apostrophies in param values(?,'?','?','?'). Try to profile the query, it might give you an idea what was wrong. — Dmitrij Kultasev
Can you check the connection, if it is set properly? Also, check the size of the strings you have as params - maybe it's bigger than 100? — Rigerta

Jack Jack · Accepted Answer · 2018-07-10T15:29:30

I am sorry guys, everything I did was correct. I just made a mistake when creating the table in sql server. I created the table like this:

create table ids
(id int,
sqljobname nvarchar(255),
ojobname nvarchar(255),
ojobstartdate nvarchar(255),
sqljobstartdate datetime )

So the columns will only have 1 for the size and make the insert failed. I have already fixed it.

Issues when using SSIS Execute SQL Task to insert a row into SQL Server with multiple parameters

1 Answers