My changed Stored Procedures in SQL are not changed in SSIS

[MsHart2u]

Hi,
I am calling 7 separate stored procedure via the SQL command tasks using " exec <sp_name>" as query commands exporting to flat files.
They are running with no errors... BUT today I made changes/improvements to 3 of the Stored procedures in SQL to the stored procedures.
I ran the SQL to see what the new record counts and field values were changed as planned.
Then, I did the "drop <sp_name>" and the "create proc <sp_name> as ..." again,
I verified I am in the correct database server, etc.
YET the package/taskes are are still running old sp's with old record counts and values.
I get no errors and the fields have not changed as expected. I tried deleting the flat files and rerunning the tasks. No luck.

Have you any ideas on what I am overlooking or not doing correctly??

Thank you in advance.


[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[MsHart2u]

I should tell you, I have all this in a package in SSIS on SQL SERVER 2008 in 32 bit environment.

Thanks again!!



[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[SQLBill]

Let's see I if understand this correctly.

You have 7 stored procs on a database.
You made changes to 3 of them.
You tested them. They worked.
You then dropped them and recreated them. (why?)
You then ran the SSIS package that runs the 7 stored procedures. The process didn't work.

Since you dropped and recreated the stored procedures after testing them, did you make sure they are new versions and not the original versions?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[MsHart2u]

let me try again.

I have 7 stored procedures in 7 tasks within 1 package, called BW_exports, of SSIS. Each task runs a separate sp.
I got replies Monday from our vendor that required fixes to 3 of the sp. Fixes are in.
I tested and recreated the sp by doing the drop and create, perhaps I should have done alter, but too late for all that now.

Currently the 'exec <sp_name>' runs fine within SQL server and gives me the records and changes I need to send in the files to the vendor, but when using the nightly process to run SSIS package, the old sp are in a cache somewhere, somehow

1. I have deleted the .dtsx files within the BW_Exports SSIS package and recreated the tasks.
2. We have stopped/started SSIS agent to try to flush the cache.
(Neither of these worked)

Our enviro in SQL Server 2008 on 32 bit servers.
I read online that there might be a compiled version of the package that needs to be recompiled...

http://stackoverflow.com/questions/11344683/redeploying-ssis-packages-cache

I am fresh out of ideas as to what to do to get this thing to see my changes. I will be added more tasks to the package as this project to integrate goes on.
I really need help to understand what is required to refresh the package, etc.

Thank you again for your help.


[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[gk53]

Are you using Execute SQL task to run your stored procedures?
Did you try to run package interatively in Microsoft visual studio?