?
Solved

suspect execution in ssis packeg flow running before data collected completely

Posted on 2011-03-17
8
Medium Priority
?
252 Views
Last Modified: 2012-05-11
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
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 1500 total points
ID: 35154916
Check your delete task logic, it may delete also wanted records.
0
 

Author Comment

by:motioneye
ID: 35156020
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
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 1500 total points
ID: 35156444
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:motioneye
ID: 35158593
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
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 1500 total points
ID: 35158791
I don't believe there are problems with SELECT INTO, I just wanted to show you other possibility.
0
 

Author Comment

by:motioneye
ID: 35162620
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
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 1500 total points
ID: 35163397
Does it contain only select * into tableA from tableSource code?
0
 

Author Closing Comment

by:motioneye
ID: 35186905
Thanks, I resolved it myself
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question