3

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)
Improve this question

4 Answers 4

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I think "query" order rather than chronological? Chronological implies there is a temporal component which there really isn't. The correct order is numeric in the order that the values are used in the query.
0

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.

Improve this answer

2 Comments

It was in that order of 0,1,2 etc and didn't work hence I put it's ? .
As @SanjayJ specified above, delete all your parameters from 'mapping window' and put it in the order with which you are assigning them. It should be in the 0,1,2 series..
0

As an option:

You may be trying to insert too long values into shorter columns

For example if you create Source DB you need to be sure that each column have sufficient maximum length and you don't put largest values (too long values) into this column.

So unfortunately SSIS don't specify the problematic column, and you need to find it by yourself or enlarge maximum length of every column.

Improve this answer

Comments

0

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] + ")"

Be aware! This approach makes your solution vulnerable to SQL injections

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.