SSIS error : [Execute SQL Task] Error: Executing the query

1
votes

I am creating a SSIS package and trying to extract data by calling stored procedures from one database and inserting the result set values into another table of different database. I have created a Execute SQL task to extract the data, a for each loop container to loop through the result set and Execute SQL task within the for loop container to insert the result set data into another database table. I am getting an the following error while inserting the records. I guess its the issue with the mapping.

[Execute SQL Task] Error: Executing the query "insert into EmployeeCount (companyId..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Following the screenshot of the template design

enter image description here

Following is the edit window of execute sql task which in inside the foreach container

enter image description here

The insert statement

insert into EmployeeCount (companyId,dataItemName,dataItemvalue,fiscalYear,fiscalQuarter,PeriodTypeId) values(companyId,dataItemName,dataItemvalue,fiscalYear,fiscalQuarter,PeriodTypeId)
3
sql-serverssisetl

3 Answers

2
votes

You will have to set "Parameter Name" in chronological order. 

 i.e companyID parameter must be 0, dataItemvalue to 1 ....PeriodTypeId to 5

Sample : enter image description here

1
votes

Simple Workaround

You can achieve this without using parameters, you can use expressions:

  1. Double click on the Execute SQL Task, Go To Expressions.

  2. add the following expression to SqlStatementSource:

    "insert into EmployeeCount (companyId,dataItemName,dataItemvalue,fiscalYear,fiscalQuarter,PeriodTypeId) 
values(" + (DT_WSTR,50)@[User::companyId] + ",'" + @[User::dataItemName] + "'," + (DT_WSTR,50)@[User::dataItemvalue] + "," + (DT_WSTR,50)@[User::fiscalYear] + "," + (DT_WSTR,50)@[User::fiscalQuarter] + "," + (DT_WSTR,50)@[User::PeriodTypeId] + ")"
0
votes

In the 'Parameter Mapping' for Parameter Name, instead of '?' give the values in a 0 based index i.e. 0,1,2,... till the end.