suspect execution in ssis packeg flow running before data collected completely

In My ssis package:

1: Execute sql task has : select * into tableA from tableSource, usuallay it will copy around 5 millions of rows
2:delete_Invalid_Test_Data: delete from tableA for certain unwated records
3:Insert data to actual table:

My problem is that when sometimes this package run, it not completely copy all the rows, for eg: it should copy all data until 23:59:59.000 but for soem reasons it just copy until 23:04 or sometime 23:30 and so on..

why this can happen ? i
ssis-packages.jpg
motioneyeAsked:
Who is Participating?
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
Check your delete task logic, it may delete also wanted records.
0
 
motioneyeAuthor Commented:
That is what I'm thinking too, then again I ran the T-sql  manually and perform delete,  I can see the data available...
0
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
You wrote that problem is happening only sometimes, maybe problems with SELECT INTO? You can use INSERT INTO SELECT copying to declared temp table.
Have you tried to use data flow task instead? You can easily get an example of it by creating sample package in data export wizard in SSMS, then by saving package to disk you are able to review contents in BIDS.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
motioneyeAuthor Commented:
hmm is any problenm when we using select into ? what the advantages of using insert into..select ? I try to avoid any lock in the database..
0
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
I don't believe there are problems with SELECT INTO, I just wanted to show you other possibility.
0
 
motioneyeAuthor Commented:
Hi,
I think there might something wrong with "execute sql task" since this is the 3rd days it stopped at 11:30 .... never get completed until 23:59:59...
0
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
Does it contain only select * into tableA from tableSource code?
0
 
motioneyeAuthor Commented:
Thanks, I resolved it myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.