SSIS: The parameter type for ‘@P1’ cannot be uniquely deduced; two possibilities are ‘sql_variant’ and ‘xml’

Recently, while working on SSIS we came across a situation to use parameters. We wanted to pass 2 parameters.

We were using SQL Command as Data access mode in OLE DB Source Editor.

After writing the query the below query:

select ? from channelmaster where displaychannel=?

When we clicked on parameters to provide the parameter values we got the error as shown below:

The parameter type for ‘@P1’ cannot be uniquely deduced; two possibilities are ‘sql_variant’ and ‘xml’

SSIS_Error

To fix this issue, we did the type cast of above parameters first before clicking on Parameters button. In our case, we did type cast them to varchar(30) as shown below:

select cast(? as varchar(30)) from channelmaster where displaychannel=cast(? as varchar(30))

After doing this, when we clicked on Parameters button, it asked for value of the parameters as shown below:

SSIS_Error_Fixed

Basically, when there is a scenario to select the value that comes through the parameter then we need to type cast that parameter.

Hope it helps !!

*Please note that the above example of SQL query is for demonstration purpose only, actual SQL query was different but similar.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.