March 28, 2017 at 10:18 am
I have a package that executes a stored procedure in the OLEDB source and loads the data into a table through the ODEDB destination. I removed a couple of columns from the stored procedure which had automatically removed them from the SSIS package source and destination. Now my package hangs in the pre-execute phase. I have tried everything I can think of, including deleting the source and destination and re-creating them, all with no success. If I put the columns back in, it works, but I do not want them in the SP because it slows it down and they're not needed. Does anyone have any suggestions?
March 29, 2017 at 1:40 pm
cpeck - Tuesday, March 28, 2017 10:18 AMI have a package that executes a stored procedure in the OLEDB source and loads the data into a table through the ODEDB destination. I removed a couple of columns from the stored procedure which had automatically removed them from the SSIS package source and destination. Now my package hangs in the pre-execute phase. I have tried everything I can think of, including deleting the source and destination and re-creating them, all with no success. If I put the columns back in, it works, but I do not want them in the SP because it slows it down and they're not needed. Does anyone have any suggestions?
1.) Remove the source and destination OLEDB objects
2.) Remove the associated connection manager(s?)
3.) Save the package
4.) Close the solution and Visual Studio
5.) Open Visual Studio, the solution, and the package
6.) Recreate the connection manager(s?)
7.) Recreate the OLEDB Source and OLEDB Destination objects and connect them.
VS has a way of caching things that makes one's butt hurt.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 30, 2017 at 12:51 pm
Hi Steve,
I tried it and it didn't work, but then figured out it has to do with my stored procedure, not SSIS. I am using a stored proc to insert records into a physical temp table then doing a SELECT on those records. The stored proc works fine with the two columns I don't need. The stored proc works fine if I remove the two columns and don't insert the records into the temp table. The stored proc hangs indefinitely if I remove the two columns and try to insert them into the temp table (I did remove them from the temp table as well). I'm at a loss! Thank you for your response.
March 30, 2017 at 3:43 pm
cpeck - Thursday, March 30, 2017 12:51 PMHi Steve,I tried it and it didn't work, but then figured out it has to do with my stored procedure, not SSIS. I am using a stored proc to insert records into a physical temp table then doing a SELECT on those records. The stored proc works fine with the two columns I don't need. The stored proc works fine if I remove the two columns and don't insert the records into the temp table. The stored proc hangs indefinitely if I remove the two columns and try to insert them into the temp table (I did remove them from the temp table as well). I'm at a loss! Thank you for your response.
If I follow correctly, the two removed columns have been removed from the final select and from the temp table but an attempt to insert those columns into that temp table is still in place.
If that is the case, can you just remove them from the insert?
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 31, 2017 at 6:36 am
No, when I remove the two columns altogether from everywhere, the query hangs upon trying to insert the records into the table. If I put the two columns back in, it works fine again. It makes no sense to me.
March 31, 2017 at 8:41 am
SSIS, stored procedure sources, and temp tables can be tricksy. Very tricksy.
It seems like SSIS will take the first temp table definition it finds and make that the source. So that means tricking it into doing what you want. If you've been using SSIS for any amount of time this won't be the first time you've done it either.
Create your 'final' temp table in the beginning, populate it with one row of fake data, then delete the data. That will give SSIS something to consume right at the start. Then do whatever you need to do to get your output.
March 31, 2017 at 11:31 am
cpeck - Friday, March 31, 2017 6:36 AMNo, when I remove the two columns altogether from everywhere, the query hangs upon trying to insert the records into the table. If I put the two columns back in, it works fine again. It makes no sense to me.
Is the proc by chance pulling data from any views at all? Or a linked server maybe?
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 31, 2017 at 1:52 pm
I've read this thread through a couple of times and I'm still not sure I understand what's going on. However ...
Are the results of the proc being used as a source in an SSIS data flow?
If so, check the data flow and look for 'EXECUTE WITH RESULT SETS' – the expected columns may be defined here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 31, 2017 at 3:28 pm
Phil Parkin - Friday, March 31, 2017 1:52 PMI've read this thread through a couple of times and I'm still not sure I understand what's going on. However ...Are the results of the proc being used as a source in an SSIS data flow?
If so, check the data flow and look for 'EXECUTE WITH RESULT SETS' – the expected columns may be defined here.
The proc is freezing outside of SSIS at the moment. The issue has been narrowed down to execution issues of the proc itself. At least that is my understanding.
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
April 6, 2017 at 8:06 am
Yes, Jason, your understanding was correct.
I found a WHERE IN being used in the stored procedure that I changed to an INNER JOIN which made it much more efficient. Once that was done, I was able to remove the two fields and it worked fine. Thank you for all your replies and sorry it took me so long to get back to this!
April 6, 2017 at 2:04 pm
cpeck - Thursday, April 6, 2017 8:06 AMYes, Jason, your understanding was correct.I found a WHERE IN being used in the stored procedure that I changed to an INNER JOIN which made it much more efficient. Once that was done, I was able to remove the two fields and it worked fine. Thank you for all your replies and sorry it took me so long to get back to this!
Congrats and glad it is working.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply