SQL Query in ADO NET source in SSIS

Question

Hi,

I am using SSIS 2019 to create ETL packages. I am using ADO NET as a Source to extract data from Sybase system with doesn't support OLEDB as a source.

I am using a Variable to pass a SQL Query to the ADO NET Source. It's as below

SELECT T1.*

FROM Table1 as T1

INNER JOIN Table2 as T2

ON T1.Col1 = T2.col1

WHERE T1.DateCol >= '01/04/2021' -- Date is passed dynamically.

The Package keeps on failing when its trying to execute this SQL.

Could you please help out, if the query needs to be written in a different format than the regular T-SQL way, because i am using ADO NET as a source.

Thank you for your time in Advance.

Answer 1

Hi @Suman Gupta ,

1. The Package keeps on failing when its trying to execute this SQL.
   Could you please share the error messages?
2. We can add expression for [ADO NET Source].[SqlCommand] in the properties of Data Flow Task.
   Please refer to the following expression and pictures:
   "SELECT T1.*
   FROM Table1 as T1
   INNER JOIN Table2 as T2
   ON T1.Col1 = T2.col1
   WHERE T1.DateCol >='"+ @[User::Date]+"'"
   
   
   

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.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 2

Please try to pass date in the ISO 8601 format: yyyy-MM-dd
iso-8601-date-and-time-format

WHERE T1.DateCol >= '2021-01-04'