Integration Services Project (BIDS)

Question

Post reply

Integration Services Project (BIDS)

tasnim.siddiqi

Ten Centuries

Points: 1063
More actions
June 30, 2010 at 9:56 pm

#226285

Hi,
As a starter in integration Services project, I have used a single block under control flow. This single block I used is a 'Execute SQL Task'. In that I have double clicked, chose my database etc. and also wrote a sql statement. But the thing is the sql statement is something I just copy pasted from a stored procedure in the database I have given. And beside the field 'IsQueryStoredProcedure' I cannot change it to True. Its always False. How do I change it from False to True.
Also another question, when a package execution is successfully completed, what does it mean? What I want to achieve is include a stored procedure in integration services proj and run that stored proc from there rather than run it separately from sql server.
Basically I want to automate a process workflow. 🙂
Thanx in advance.

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

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

Hello Tasnim,

Welcome to SSIS!!!:-):-):-)

http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx

This should help you in understanding the various options available to execute a stored procedure using SSIS SQL Task

The lifecycle of package execution encounters following stages:

1. Development

2. Debug/during Execution - Yellow

3. Success - Green

4. Fail - Red

The green symbolizes the package has successfully completed it's execution.

Hope this helps

Raunak J

tasnim.siddiqi Ten Centuries Points: 1063 More actions · Answer 2

Hey,

Thanx for the blog...But it still doesn't answer my question...

I am interested to know when stored procedure is executed successfully in integration services, does it do the updates/changes to the tables/views that is in my database? If so then what are the settings in the SQL Task Editor and any other things to watch out for?

As I mentioned earlier, I want to automate a process workflow by running stored procedures using integration services, whereby changes/updates will be done to the table based on the code in stored procedure.

Thanks!

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

Yes..upto the extent that you may change or even drop the databse schema...:-D:-D

Raunak J

tasnim.siddiqi Ten Centuries Points: 1063 More actions · Answer 4

Hi could you tell me what setting I should use? Because I have created a stored procedure in the database. Now I want to run that stored procedure in the integration services project, as an 'Execute SQL Task' block, and in return update a certain table in the database. The update code is written in the stored procedure, like this :

ALTER PROCEDURE [dbo].[_fromThird_table]

-- Add the parameters for the stored procedure here

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

UPDATE dbo.Third_table

SET dbo.Third_table.Person = dbo.First_table.id

FROM dbo.First_table

WHERE dbo.First_table.id = '154x8'

END

So, when a package have been executed completely, in integration services project, Third_table should be updated, but that's not happening!!! Any help with this?

Regards,

Tasnim

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

Tasnim,

You just have to include EXEC procedure_name <parameter_markers> for OLEDB

else procedure_name <parameter_markers> forADO/ADO.NET in the SQL Statement and IsQueryStoredProcedure as True...

Set ByPassQuery to True/False as desired.

:cool::cool::cool:

Please follow the link attached in my earlier post for any further reference

Raunak J

tasnim.siddiqi Ten Centuries Points: 1063 More actions · Answer 6

Hey,

Thanx a lot. Its the exec Proc_name that I didn't give earlier.

Cool, works fine.

Thanx and best regards,

Tasnim