question

WillieYeo-2342 avatar image
0 Votes"
WillieYeo-2342 asked Monalv-msft commented

SSIS OLE Source returning wrong results

OLE source is either returning wrong result or no rows. SSIS Project version 2014. Same issue with 2016. The workflow pull the maxdate from Dest table. Using SQL Task result as input parameter for OLE Source query. SQL Task - SELECT MAX(CreateDate) as maxdate from Dest DFT OLE Source - SELECT from Source where CreateDate > ? (Parameter from SQL Task) This method is generating maxdate value of '3/26/2021 7:36:59 AM. Results returned from OLE query include previously pull last record. Thus cause duplicate PK error. 2nd Option is to convert maxdate value to DATETIME2. But no results is returned. Tried converting the OLE Source query to sql variable. Same issue. No rows are returned. SQL Task - SELECT MAX(CAST(CreateDate as DATETIME2) ) as maxdate from Dest DFT OLE Source - SELECT from Source where CAST(CreateDate as DATETIME2) > ? (Parameter from SQL Task) Tried rebuilding a new package within the same project. The problem persist in the new package. There is another package performing the same extract on different tables from the same source without issues. Any ideas? Source table Id uniqueidentifier (PK) CreateDate datetime Dest table Id uniqueidentifier (PK) CreateDate datetime

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @WillieYeo-2342 ,

Could you please check if you set the parameter correctly in the Execute SQL Task?

SQL statements and stored procedures frequently use input parameters, output parameters, and return codes. In Integration Services, the Execute SQL task supports the Input, Output, and ReturnValue parameter types. You use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes.

Please refer to Parameters in the Execute SQL Task and SSIS - How To Pass input parameter in Execute SQL Task.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

yes. the parameter is set correctly. As part of the test, the value of the parameter was written to a table.

0 Votes 0 ·

Hi @WillieYeo-2342 ,

Please test the following sql query in SSMS.

 1.Use MyDB;
 Select MAX(CAST(CreateDate as DATETIME2)) as maxdate from Dest;
 GO
    
 2.Use MyDB;
 Select * From Source Where CAST(CreateDate as DATETIME2) > (Select MAX(CAST(CreateDate as DATETIME2)) as maxdate from Dest);
 GO


Hope the following pictures will be helpful:
82686-t-sql.png
82713-cf.png
82697-est1.png
82714-est2.png
82698-df.png
82699-oledbsource1.png
82715-oledbsource2.png

Best regards,
Mona


0 Votes 0 ·
t-sql.png (52.2 KiB)
cf.png (46.5 KiB)
est1.png (29.2 KiB)
est2.png (12.1 KiB)
df.png (76.3 KiB)
oledbsource1.png (34.1 KiB)
oledbsource2.png (28.8 KiB)

Hi @WillieYeo-2342 ,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·
WillieYeo-2342 avatar image
0 Votes"
WillieYeo-2342 answered Monalv-msft commented

I put in a workaround by adding a sec to the datestamp which is not ideal. but it resolved the issue.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @WillieYeo-2342 ,

Please remember to accept your own answer that resolved your issue.

This can be beneficial to other community members reading this thread.

Best Regards,
Mona

0 Votes 0 ·