SSIS OLE Source returning wrong results

will-308 1 Reputation point
2021-03-27T02:14:34.293+00:00

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
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,891 Reputation points
    2021-03-29T02:54:46.407+00:00

    Hi @will-308 ,

    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.

  2. will-308 1 Reputation point
    2021-04-07T15:07:08.55+00:00

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