July 22, 2009 at 3:28 am
Hi all,
I am using an OLE DB Source in an SSIS package I inject sql script below to retrieve data from datasource. The source has 700 000 records but when the package runs it only shows 2 806 records in the green lines. If i run the same script in SQL not in OLE Db source i get all the records. What could I be missing. Im using SQL server 2005. The script:
select fundcode, memberstatus,paymentmethod, memberrefno, CASE WHEN paymentdate IS NOT NULL THEN CAST([paymentdate ] AS DATETIME) ELSE CASE (null AS DATETIME) END PaymentDate,CASE WHEN recieveddate IS NOT NULL THEN CAST([recieveddate ] AS DATETIME) ELSE CASE (null AS DATETIME) END RecievedDate
FROM Members
Many Thanks
July 22, 2009 at 3:54 am
phumlo1 (7/22/2009)
Hi all,I am using an OLE DB Source in an SSIS package I inject sql script below to retrieve data from datasource. The source has 700 000 records but when the package runs it only shows 2 806 records in the green lines. If i run the same script in SQL not in OLE Db source i get all the records. What could I be missing. Im using SQL server 2005. The script:
select fundcode, memberstatus,paymentmethod, memberrefno, CASE WHEN paymentdate IS NOT NULL THEN CAST([paymentdate ] AS DATETIME) ELSE CASE (null AS DATETIME) END PaymentDate,CASE WHEN recieveddate IS NOT NULL THEN CAST([recieveddate ] AS DATETIME) ELSE CASE (null AS DATETIME) END RecievedDate
FROM Members
Many Thanks
What do you mean by 'inject'?
Is 'received' deliberately spelled wrongly?
What are the datatypes of [PaymentDate] and [ReceivedDate] (or [RecievedDate])?
If you Cast NULL as a datetime, doesn't it just return NULL (ie, no effect)? So can't you just use:
Select fundcode, memberstatus, paymentmethod, memberrefno, CAST([paymentdate] AS DATETIME) PaymentDate,CAST([recieveddate] AS DATETIME) RecievedDate
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 22, 2009 at 4:18 am
Hi Phil,
Thanks for a reply, by inject I mean putting thie script on the sql command text box. Received was a typing error. [PaymentDate] and [ReceivedDate] are Varchar datatypes. I have tried to use CAST as per your suggestion still i dont get all the records.
Many Thanks.
July 22, 2009 at 4:50 am
I am afraid I have no idea what could be happening, it all sounds very straightforward.
Does it always process exactly the same number of records? If yes, try putting an ORDER BY clause on your SQL and running again - is the number of records different?
Have you tried adding a data viewer to the OLEDB source output - just to check that all looks OK?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply