Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Error Conversion Datatype Variable Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 1:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:23 AM
Points: 63, Visits: 258
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

"
Post #1398860
Posted Thursday, December 20, 2012 10:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:23 AM
Points: 63, Visits: 258
????
Post #1399061
Posted Friday, December 21, 2012 3:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1399302
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse