0

We are using SQL Server 2012 and SSDT 2010 for the development and debug purpose for SSIS packages.

I have a simple data flow task where it has two components only- OLE DB source and OLE DB target. The target table stores data for multiple dates and is loaded incrementally from the source table i.e. whenever the source table receives data with a new date, it is loaded into the target table. There is no transformation, calculation or logic applied on the data flow.

The source OLE DB uses the following query to select data from the source- SELECT * FROM source_table WHERE date_col NOT IN (SELECT DISTINCT date_col FROM target_table);

In the OLE DB target page, it is set as Fast Load and Table Lock option is also unchecked.

Whenever we are executing the package from SSDT, it only shows some rows in figures in the data flow path as processing and goes into the never ending stage. The figure doesn't grow and the package never ends until stopped forcefully.

Thanks in advance.

Abhishek.Roy
  • 11
  • 4
  • Hi @Abhishek take a look at the [SSIS](http://stackoverflow.com/questions/20063225/ssis-how-to-copy-data-of-one-table-into-different-tables), if you still face issues, can you share screenshots. – DataWrangler May 12 '17 at 12:10
  • Your data flow should use Lookup to accomplish what you want in the WHERE clause. – Wendy May 12 '17 at 12:21
  • 1
    Lookup will introduce even more performance issues. Your first task is to run the query in SSMS and see how long it takes. Then I suggest you use `EXISTS` rather than in and see if performance improves – Nick.Mc May 12 '17 at 13:20
  • Hi @Nick.McDermaid, I tried running my original query in SSMS which gave results in a minute. I also tried using the EXISTS clause which gave me the results faster than before in SSMS. But the SSIS package is not going through. I believe there is some issue with the various options available in Table - fast load in OLE DB destination. I tried tweaking the Rows per Batch and Maximum Insert Commit Size but no help – Abhishek.Roy May 17 '17 at 10:27
  • Perhaps you have a locking issue. Use spWhoIsActive in the target database to see if it is locked. – Nick.Mc May 17 '17 at 11:48
  • I have checked with sp_whoisactive and it is actually a locking issue. I had unchecked the Table Lock option though in the OLE DB destination page. – Abhishek.Roy May 18 '17 at 06:29

1 Answers1

0

Try substituting sub query with a left join as below:

SELECT a.* FROM source_table a
left join target_table b
on a. date_col=b.date_col where b.date_col is null
Kapil
  • 987
  • 5
  • 11
  • Possibly even better is using `exists` – Nick.Mc May 12 '17 at 13:19
  • I tried using a left join and exists both. The exists gave the result faster in SSMS. But I think the issue lies somewhere in the SSIS package. Data flow records doesn't grow when I run the package in the debug mode from SSDT. – Abhishek.Roy May 17 '17 at 10:30
  • Same problem here, did you find an answer? – Henrov Mar 07 '18 at 15:24