INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

My changed Stored Procedures in SQL are not changed in SSIS

My changed Stored Procedures in SQL are not changed in SSIS

(OP)
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. sad

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

Thank you in advance.

Kathy,
Bus Sys Analyst

"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

RE: My changed Stored Procedures in SQL are not changed in SSIS

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

Thanks again!!

Kathy,
Bus Sys Analyst

"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

RE: My changed Stored Procedures in SQL are not changed in SSIS

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: What should I know before I post?

RE: My changed Stored Procedures in SQL are not changed in SSIS

(OP)
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/redepl...

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.

Kathy,
Bus Sys Analyst

"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

RE: My changed Stored Procedures in SQL are not changed in SSIS

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close