Post reply

SSIS Error Conversion Datatype Variable

  • December 20, 2012 at 1:59 am

    #150196

    Hi All,

    I need your help. i wann be crazy !! 🙂

    Sql server 2008R2

    I Have a variable that I converted into float to put it in a ssis variable ( datatype double)

    Then I use this variable in a 'Command Sql from a variable'

    But I have this error:

    [PrepaPaie gains Serveurs [1]] Erreur : « Code d'erreur SSIS DTS_E_OLEDBERROR. Une erreur OLE DB s'est produite. Code d'erreur : 0x80040E07.

    Un enregistrement OLE DB est disponible. Source : « Microsoft SQL Server Native Client 10.0 » Hresult : 0x80040E07 Description : « Erreur de conversion du type de données varchar en numeric. ».

     »

    This is the script of the 'Command SQl from a variable':

    "

    DECLARE @START_DATE INT

    DECLARE @END_DATE INT

    DECLARE @CoefCoutHoraire decimal

    SET @START_DATE = '"+ (DT_STR, 6, 1252) @[User::DateDebutChargement] +"'

    SET @END_DATE =' "+ (DT_STR, 6,1252) @[User::DateFinChargement]+ "'

    SET @CoefCoutHoraire = '"+ (DT_STR, 4, 1252) @[User::CoefCoutHoraire] +"'

    set datefirst 1;

    --- Calcul Gain serveurs

    select YEAR (DATEADD(day, 1, DATEADD(DAY, -datepart(dw, data.Date), data.Date)))as ANNEE,

    DATEPART (ISO_WEEK,data.Date)as SEMAINE,

    data.Id as ID_ETAB, data.IdPers, data.Matricule, (sum(Gains_Midi+Gains_Soir) * @CoefCoutHoraire) as Gains

    from

    (select P.IdPers, P.Matricule, G.IdServeur, G.EtablissementId, E.Id, G.date,

    sum(case when G.Service='MIDI' then G.Remise else 0 end) as Gains_Midi,

    sum(case when G.Service='SOIR' then G.Remise else 0 end) as Gains_Soir

    from GainsServeurs G, Personnel P, Etablissement E

    where G.Date

    between

    DATEADD(day, 1, DATEADD(DAY, -datepart(dw, G.Date), G.Date))

    and

    DATEADD (day,7, DATEADD(DAY, -datepart(dw, G.Date), G.Date))

    and G.IdPers = P.IdPers

    and G.EtablissementId = P.EtablissementId

    and E.Id = P.EtablissementId

    and P.Tombstone is null

    group by P.IdPers, P.Matricule, G.EtablissementId, E.ID, G.Remise, G.Date, G.IdPers,

    G.CA, G.Couverts, G.IdServeur, G.EtablissementId

    ) as data

    where 100* YEAR (data.date) + datepart(iso_week, data.date) between @start_date and @end_date

    group by data.IdPers, data.Matricule, data.Id,

    YEAR (DATEADD(day, 1, DATEADD(DAY, -datepart(dw, data.Date), data.Date))),

    DATEPART (ISO_WEEK,data.Date)

    order by data.Id, data.Matricule

    "

  • December 20, 2012 at 10:38 am

    #1570618

    ????

  • December 21, 2012 at 3:32 am

    #1570818

    You have a data type error occurring in the database when you run your query. What does your expression evaluate to at runtime? If you can capture that you can try running the SQL against the server and see the exact problem. Post the SQL if you have issues determining the cause and would like some assistance.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply