ssis - No value given for one or more required parameters

Question

I am working with SSIS 2008. i am getting error on sql task editor query as Error: Executing the query "SELECT SUBSTRING(?, 8, 2) + SUBSTRING(?, 10, 2) +..." failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Execute sql task containing query

SELECT SUBSTRING(?, 8, 2) + SUBSTRING(?, 10, 2) + '20' + SUBSTRING(?, 10, 2) AS File_Date

i had selected result_Set as Single_row

The parameter mapping used containing field name as var_name:User::File_Name_update direction:input Data type: varchar parameter_name:0 Parameter_size:-1

The result set i have added is: Result_Name:File_date variable_name:User::File_date

So here i am getting the file date from the updated input file name in substring.May i know what am i doing wrong to handle the above scenario?

Answer 1

You need to map 3 parameters for that query. If you want to map only one, you can do this:

declare @str varchar(max) = ?
SELECT SUBSTRING(@str, 8, 2) + SUBSTRING(@str, 10, 2) + '20' + SUBSTRING(@str, 10, 2) AS File_Date

Answer 2

In my case, My local machine supports date format as 'mm-dd', But when I deploy in server It is accepting only 'dd-mm'. So, I solved the same issue after changing the format. This could help someone.