My team recently had to use BI Publisher 11g with a SQL Server 2008R2 datasource and found that simply copying the source SQL code into BIP and setting variables declared in the script equal to BIP parameters didn’t work. Long story short, a simple convert did the trick.

So, the following (followed by an appropriate SELECT query), where :Var1 is a datetime BIP parameter, :Var2 is an int BIP parameter, and :Var3 is a text BIP parameter, does not work.

Declare @Var1 datetime = :Var1;
Declare @Var2 int = :Var2;
Declare @Var3 varchar(15) = :Var3;

But applying a convert to the BIP parameters does.

Declare @Var1 datetime = convert(datetime, :Var1);
Declare @Var2 int = convert(int, :Var2);
Declare @Var3 varchar(15) = convert(varchar(15), :Var3);