March 21, 2010 at 8:50 pm
Hi All,
This problem seems just too bizarre. I have a stored procedure that I'm trying to execute from an SSIS package. For what it's worth, the procedure looks like this:
1. Log start of procedure execution
2. Create a small tally table (41 rows)
3. Do an INSERT ... SELECT ... statement to populate a metric calculation table with rows containing only primary key values (the remainder of the columns are populated later)
4. Do an INSERT ... SELECT ... statement to populate another table with similar rows.
5. Drop the tally table
6. Update the statistics for the tables populated in steps 3 and 4
7. Log the completion of the procedure.
The problem: With what appears to be complete randomness, steps 3 and 4, together or individually, fail to insert any rows. I have run this package three times today, with the following results:
1. Neither insert inserted any rows
2. The first insert insertedabout 315K rows (the expected number), the second insert inserted 0 rows (expect around 7700)
3. Same as 1
There are never any errors reported.
If I execute the stored procedure from SSMS, it runs fine and inserts rows as expected.
I don't recall if this behaviour occurred executing the package from Visual Studio, but I don't think I would have said "This is ready for production" if I had.
I've added writes to the log to give an indication of how many rows are written. Those INSERTs execute just fine. Any help on debugging this is greatly appreciated.
Cheers,
Tom
March 21, 2010 at 10:08 pm
are you using same stored procedure to insert rows into two different tables.. If so did you try inserting them using data flow task with one source(SP) and two destinations.. This is just another approach... might solve your problem.. It sounds weird that you aren't able to insert any rows into second table using execute sql task
March 22, 2010 at 2:55 am
Does the INSERT require the tally table, and does the tally table need to be populated? I wonder if some sort of concurrency problem could be the cause...
I assume you're running SQL Profiler to see what order statements are executed in, what output does it provide. Is there any delay between the population of the tally table and the INSERT statements that likely rely on it?
March 22, 2010 at 4:48 pm
divyanth - It is a single stored procedure that executes the 7 steps I described above.
Leo - The tally table is used to create a date range, so yes, it is required. I haven't used Profiler yet. But are you implying that SqlServer will execute statements in a stored procedure in random order? These aren't individual Execute Sql tasks in SSIS, it's 7 sequential steps in a single stored procedure executed in a single Execute Sql task.
March 22, 2010 at 5:35 pm
Since you have added logging to it, that is good.
Does the stored procedure run in a transaction?
Maybe encapsulate the statements in the proc within a try...catch block and output errors if encountered to a table.
I would certainly run this from BIDS, add some break points and data viewers. Then step through the whole thing - or at least the pertinent stuff before and after the proc execution that is causing grief.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 23, 2010 at 9:33 am
Tom Bakerman (3/22/2010)
divyanth - It is a single stored procedure that executes the 7 steps I described above.
Since you say that its only a single Sp . I would recommend you to try the approach i have mentioned in my earlier post.. that would be faster too. Also, use a data viewer before each destination to see the output..
March 28, 2010 at 2:11 pm
I haven't had time to try rewriting the task and stored procedure into seperate tasks yet, but I did run this with SQL Profiler on. And of course, everything worked fine (that is, all insert statements inserted the expected number of rows).
Anybody have any more thoughts about what is happening here?
March 28, 2010 at 4:20 pm
SSIS may be running into an issue where there is a failure of some sort (maybe truncation) and then stops the proc at that point since it has nowhere to redirect the failed data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 29, 2010 at 5:31 pm
This problem disappeared for awhile, but we recently upgraded to SQL 2008 R2, and It's Baaaack.
There is absolutely no indication of any problem when running the stored procedure in SSIS. The package validates fine. The SSIS package only executes the stored procedure; there's no input or output from the package.
I tried to replicate the context in which this is happening by watching the SSIS job (because there are 2 other execution streams in the whole job, neither of which have any issues, btw), and when the step in question ended, I looked at the destination table. Empty. So I ran the stored procedure from SSMS. Runs like a champion. Nearly 4.8 million rows inserted, as expected.
I'm tearing out what little hair I have left. Any ideas?
May 31, 2011 at 2:09 pm
Have you found a resolution? We're running into the same problem.
SSIS runs properly, 100% complete. Stored procedure run through SSIS does not insert any rows. Ran the sproc through SSMS and it works fine.
We're adding more detailed logging, but haven't found anything yet.
May 31, 2011 at 2:14 pm
JD Carlston (5/31/2011)
Have you found a resolution? We're running into the same problem.SSIS runs properly, 100% complete. Stored procedure run through SSIS does not insert any rows. Ran the sproc through SSMS and it works fine.
We're adding more detailed logging, but haven't found anything yet.
Resolution? Not really. I rearranged the process flow so that it is more serial (sort of defeats the purpose, wouldn't you say?) and haven't experienced the issue. But I don't really consider this a resolution. Good luck.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply