Stored procedure inSSIS package

Question

Stored procedure inSSIS package

unikhath

SSC-Addicted

Points: 497
More actions
August 18, 2008 at 3:24 pm

#192502

Hi,
here is the scenario of the package. There is a company XYZ and its name is being chnaged to ABC, I have to update all the users under the company XYZ. I have to pass a SP with 2 string parameters the agency name and eid of the user. How should i pass a SP and what are the transformations needed to build this package. It is nightly update package.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You would use an Execute SQL Task, but it sounds like you could do this in a simple SQL Server Agent Job, unless you need the parameters to be set dynamically.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

unikhath SSC-Addicted Points: 497 More actions · Answer 2

Is this approach possible:

Use an OleDB source:

Read all the Agencies

Look up transformation for any Agency name changes

Use Oledb Command to update the tables in Database

And use second OleDB command to send Notifications

My doubt here is Can I pass the SP parameters in Command Property in OleDB command transformation?

please advice

thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Sure it's possible, but I'm not sure you are going about it the best way.

Are you operating against 2 databases on different servers?

What is your name changed lookup going to be against? I mean where are you storing the old name if the new name is in the "active" table?

If you post the definitions of the tables involved I think you'd get a response that does not require using SSIS, but just a simple stored procedure.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

unikhath SSC-Addicted Points: 497 More actions · Answer 4

Yes, I will be operating on 2 different databases but on same server. There is one active connection. The look up will be against the userids who have different agency name. Hope it makes sense.

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

It still doesn't make sense to me. How do you know that Agency 'ABC' was changed to 'XYZ', do you have a log table?

Could you post the schemas?

Shouldn't something like this be handled through referential integrity?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

unikhath SSC-Addicted Points: 497 More actions · Answer 6

Hi Jack,

This is what I have as the requirement:

•When an agency Name changes then all Users associated with that agency should be updated to the new agency name.

•When a sponsor of any agency changes then all Users associated with that agency should be updated with new sponsor.

•When a sponsor and agency name changes then: A) Update agency name for users of that agency. B) Update Sponsor for all users who have previous sponsors (A sponsor can have more than one agency assigned to them).

the sponsor here is the person who has the right to create the users id.

Any help in using the transformation tasks will be great

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

I really want to help but I can't unless you answer these questions:

What are the table structures? Is this even close:

agencies(agencyname, sponsorname)

sponsors(sponsorname)

What are the relationships between tables?
Are there primary keys or unique keys(index) on the tables?
Are there foreign keys on the tables?
How do you know old agency name AFTER it has been changed to the new name?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question