SSIS OLE Source returning wrong results

Question

question

WillieYeo-2342 asked Mar 27, '21 Monalv-msft commented Apr 8, '21

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

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

Answer 1 · 2021-03-29T02:54:46.407Z

Monalv-msft answered Mar 29, '21 Monalv-msft edited Apr 7, '21

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

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

WillieYeo-2342 · Mar 29, 2021 at 06:04 PM

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

0 ·

Monalv-msft WillieYeo-2342 · Mar 30, 2021 at 09:01 AM

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:

Best regards,
Mona

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)

Monalv-msft WillieYeo-2342 · Apr 05, 2021 at 03:03 AM

Hi @WillieYeo-2342 ,

May I know if you have anything to update?

Best regards,
Mona

0 ·

Answer 2 · 2021-04-07T15:07:08.55Z

WillieYeo-2342 answered Apr 7, '21 Monalv-msft commented Apr 8, '21

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

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

Monalv-msft · Apr 08, 2021 at 02:46 AM

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 ·

question