I create new ETL package with SQL Server Integration Services 2005, and I was thinking that nothing can surprise me, but It seems that I was wrong. My package contains simple date parameter which I would like to pass to sql command source. But every time I’ve pressed parameters button I get message:
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the “SQL command from variable” access mode, in which the entire SQL command is stored in a variable.
Strange because my t-sql command was very simple… So what’s happened? I used my query on different system and I need to set format of the date time, so I put at begging of the statement following part:
SET DATEFORMAT DMY
And that was the source of error when I removed it from script everything works fine.