SSIS expression how to convert DateTime SQL statement ole db source?

Question

when i put this code on the ole db source (sql commnd from variable ) :

"select * from [DistrisysDW].[dbo].[Wrk_Agreg_Faible] where cast(Dat_Echeance as date)= "+ @[User::p_Date_Ref] +" ;"

i have this error message :

   "The data types "DT_WSTR" and "DT_DATE" are incompatible for the binary operator "+".
 Can not perform implicit type conversion operands compatible types for the operation. 
To perform this operation, an explicit conversion of one or both of the operands must be
 made with a conversion operator.
Failed to result type definition of the binary operation
 "" select * from [DistrisysDW]. [Dbo]. [Wrk_Agreg_Faible] 
where cast (as Dat_Echeance date) = "+ @ [User :: p_Date_Ref]." Error Code: 0xC0047080."

Dat_Echeance is a smalldatetime and @[User::p_Date_Ref] is my parameter, its datatype is a Datetime.

(i have sql server 2008R2)

*DON'T* concatenate strings. SSIS allows parameterized queries, just use `?` as the parameter name. In this case, SSIS refuses to concatenate the statement string and the date parameter and saves you from guaranteed failure as the resulting SQL statement would contain an unquoted date value in an arbitrary date format — Panagiotis Kanavos, Dec 22 '14 at 14:17
tnk u, but, it's in an ole db source, and these statement will be contained in a variable that will be used to get records in ole de source. i think it's a sql dynamic, and in this case (case of sql from variable) i can't use mappings, like ?, we can plny use @parameter — NidalaS, Dec 22 '14 at 14:53
That's not how SSIS works. You can and *should* use parameterized queries with OLEDB sources. The parameters *must* be named `?` and mapped to a parameter or variable. It's pointless to use typed variables if you are going to concatenate them — Panagiotis Kanavos, Dec 22 '14 at 15:11
possible duplicate of [Parameterized OLEDB source query](http://stackoverflow.com/questions/18288183/parameterized-oledb-source-query) — Panagiotis Kanavos, Dec 22 '14 at 15:12

score 0 · Answer 1 · answered Dec 22 '14 at 17:08

This is the solution : (But I still don't know why my parameter is a DateTime and the field "Dat_Echeance" is a smalldatetime and it doesn't work. so i should convert with a (DT_WSTR, 10) to match the two things? and i can't convert my parameter to a DT_Time). So, it worked with this :

"select Dat_Mvt_Ecrs ,Cod_Type_Tcn_Transcod ,Cod_Devise ,Cod_Famille_Taux ,Id_Fam_Tranche_Rec ,Id_Tranche_Rec_Dur_Init ,Cod_Emet ,Lib_Zone_Geo_Mec ,Id_Categorie_Not ,coalesce(SUM(Taux * Emiss * Qte_Devise) ,0) AS Qte_Emiss_Taux ,Round(coalesce(SUM(Taux * Emiss * Qte_Devise * Tx_Actuariel) /
               case when SUM(Taux * Emiss * Qte_Devise)=0
                     then NULL
                     else SUM(Taux * Emiss * Qte_Devise)
                     end
              ,0)
          ,4) as Mnt_Remun_Emiss_Tx , Round(coalesce(SUM(Taux * Emiss * Qte_Devise * Tx_Declare) / case when SUM(Taux * Emiss * Qte_Devise)=0
                     then NULL
                     else SUM(Taux * Emiss * Qte_Devise)
                     end
              ,0)
          ,4) as Mnt_Remun_Emiss_Tx_Monet, coalesce(SUM(Marge * Emiss * Qte_Devise)
        ,0) AS Qte_Emiss_Marge ,Round(coalesce(SUM(Marge * Emiss * Qte_Devise * Mnt_Marge_Eonia) / case when SUM(Marge * Emiss * Qte_Devise)
                     =0
                     then NULL
                     else SUM(Marge * Emiss * Qte_Devise)
                     end
              ,0)
          ,4) as Mnt_Remun_Emiss_Marge, Round(coalesce(SUM(Marge * Emiss * Qte_Devise * Mnt_Marge_Cal) /
               case when SUM(Marge * Emiss * Qte_Devise)
                     =0
                     then NULL
                     else SUM(Marge * Emiss * Qte_Devise)
                     end
              ,0)
          ,4) as Mnt_Remun_Emiss_Marge_Cal ,coalesce(SUM(case when Dat_Echeance= " + (DT_WSTR, 10) @[User::p_Date_Ref] + "
                   then 0
                   else Taux * Qte_Devise
                   end
                   )
        ,0) AS Qte_Ecrs_Tx ,coalesce(SUM(case when Dat_Echeance= " + (DT_WSTR, 10) @[User::p_Date_Ref] + "
                   then 0
                   else Marge * Qte_Devise
                   end)                    
        ,0) AS Qte_Encours_Marge
      FROM Wrk_Agreg_Faible
     WHERE Id_Tranche_Rec_Dur_Init IS NOT NULL
       AND Cod_Famille_Taux <> 'S'
     GROUP BY Dat_Mvt_Ecrs
             ,Cod_Type_Tcn_Transcod
             ,Cod_Devise
             ,Cod_Famille_Taux
             ,Id_Fam_Tranche_Rec
             ,Id_Tranche_Rec_Dur_Init
             ,Cod_Emet
             ,Lib_Zone_Geo_Mec
             ,Id_Categorie_Not"

SSIS expression how to convert DateTime SQL statement ole db source?

1 Answers1