• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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
0
motioneye
Asked:
motioneye
  • 4
  • 4
4 Solutions
 
Daniel_PLDB 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_PLDB 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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_PLDB 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_PLDB Expert/ArchitectCommented:
Does it contain only select * into tableA from tableSource code?
0
 
motioneyeAuthor Commented:
Thanks, I resolved it myself
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now