SSIS - Passing DATETIME parameter to where caluse for ADO.net connection

Question

Hi,

Extracting the data from oracle DB and loading it into SQL server 2017 using SSIS package. my source is oracle and filed date type is TIMESTAMP (6). I have created "STRAT_DATE" as a parameter and the value is coming from the SQL server table and the data type and Parameter were defined as - DATETIME

when I added as below into where clause package is running with failer but it extracting all data from the source.

Where source_date >= '" + @[User::start_date]) + "'

Please advise how to define the parameter?

When I run the below query in oracle it giving current result but does not work in SSIS

Oracle SQL:

SELECT * FROM ACCOUNTS WHERE OPEN_DATE > TO_DATE('2022-06-12 00:00:12,'YYYY-MM-DD HH24:MI:SS)

Answer 1

Hi @Avinash Pentyala ,

1. You need to change the @[User::start_date] variable as SSIS string data type and match its value the to_date() function below 2nd parameter.
2. After that you need to closely match Oracle's WHERE clause: WHERE source_date >= to_date('" + @[User::start_date] + "', 'MM/DD/YYYY HH24:MI:SS');"

Answer 2

Hi @Avinash Pentyala ,

After set the variables, use the command to pass the parameter.

SELECT        *  
FROM            TABLE  
WHERE        OPEN_DATE > to_date('"+@[User::SSIS_date]+"' , 'YYYY-MM-DD HH24:MI:SS')  

A same thread you may take a reference to.

passing-a-sql-datetime-variable-to-an-oracle-query

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 3

You use a ADO.NET connection, here you can use named parameter = @ parameter in the SQL statement and map that SQL parameter to your SSIS parameter

Where source_date >= @startDate AND ...  

See https://learn.microsoft.com/en-us/sql/integration-services/data-flow/map-query-parameters-to-variables-in-a-data-flow-component?view=sql-server-ver16