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?

Solution

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

Comments

[SFrejofsky]

depending on the version of sql server you may need to separate the declare and set statements. I am pretty sure that you could do a combined statement starting in 2008 but 2005 would require the statement be split.