May 24, 2009 at 8:31 pm
Hello All,
My SSIS package is calling a stored procedure. When I change my stored procedure and then execute the package, there is no change. SSIS is doing the same thing even after me changing the SP, proving my SP change is not recognized by package. However if I directly execute the SP, then I can see the change.
Is there a possibility that SP is cached in the SSIS when used in Execute SQL task? If true how to verify this and get rid of this caching.
Thanks.
May 24, 2009 at 9:35 pm
sridevi sriraman (5/24/2009)
Hello All,My SSIS package is calling a stored procedure. When I change my stored procedure and then execute the package, there is no change. SSIS is doing the same thing even after me changing the SP, proving my SP change is not recognized by package. However if I directly execute the SP, then I can see the change.
Is there a possibility that SP is cached in the SSIS when used in Execute SQL task? If true how to verify this and get rid of this caching.
Thanks.
Unless your SSIS package is actually running at the time that you attempt to change the package, there is no caching of the stored procedure or its contents by your SSIS package. If it is running when you try to change it. then you get a situation similar to that of an Agent Job or a user session that is using a sProc that you change. SQL Server will keep around the prior (now hidden) version of the sProc for as long as the current users/sessions are actively using it. Once they are done, the old version will be released and never seen again. So even if this happened to you, it shouldn't last very long, and if you re-run your package, it should be gone.
However, I doubt that this is what is happening to you. In my experience things like this appear to happen in SSIS because an enterprise SSIS package and it's supporting stored procedures, typically have several environments that it can run in (Dev, QA and one or more production targets, at a minimum), but it is not always clear which one it is using for as task or component at any particular time. In other words, I suspect that the procedure that you changed was not the one that your SSIS package was using (probably different server or database).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2009 at 5:12 pm
Hello There,
Thanks for your Reply. I was pretty sure in which environment I was changing my procedure. I had my visual studio open with package. Then ran package couple of times and then changed sp. could not see any change. Then ran the job from management studio. Again no change. Later I figured out a way out for the issue. I dropped the stored procedure and recreated with my new changes. Then it worked.
Thanks.
May 26, 2009 at 5:24 pm
If you add additional columns to the output, those changes will show up in SSIS but will not do anything. The new column(s) are ignored in the data flows until you explicitly modify the tasks to map those columns to the appropriate output.
Could this be the problem?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2009 at 4:09 pm
Not sure if this will help but my failing memory is pointing towards something that I heard in a SQL Server 7 course many years ago, that when you do an "Alter Procedure", anything that currently uses that proc will not use the new version unless you do a "With Recompile". My current co-workers seem to have a fascination with using Alter Procedure rather than Drop->Create (which forces a recompile).
The other alternative possibility is that you are creating the proc under your schema rather than dbo.
May 28, 2009 at 1:17 am
I agree with what Jefffrey said. Once more try to create the new simple package that will call the changed procedure with extra columns and see if this will make any difference...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply