Controlling flow in a stored procedure

I have a stored procedure with two UPDATE statements in it. The second UPDATE statement relies on the completion of the first UPDATE statement to run correctly.

The problem I am running into is that SQL Server sometimes runs the second statement before completing the first.

To get around this, I tried putting the second UPDATE statement in a different stored procedure called within the first procedure, but I am still having problems.

I do not believe I am doing anything wrong, but just in case, here is the relevant code from the proc:

-- Look up County ID

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET CountyID =

(SELECT CountyID

FROM tblCountyLkp

WHERE tblCountyLkp.CountyName = LTRIM(RTRIM(tmpZoneTypes.CountyName)))

COMMIT TRANSACTION

-- Look up existing Zone Type IDs

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET ZoneTypeID =

(SELECT tblZoneTypes.ZoneTypeID

FROM tblZoneTypes

WHERE tblZoneTypes.CountyID = tmpZoneTypes.CountyID

AND tblZoneTypes.FieldNbr = tmpZoneTypes.FieldNbr

AND LTRIM(RTRIM(tblZoneTypes.ZoneAbbrev)) = LTRIM(RTRIM(tmpZoneTypes.ZoneAbbrev))

AND LTRIM(RTRIM(tblZoneTypes.ZoneFull)) = LTRIM(RTRIM(tmpZoneTypes.ZoneFull)))

COMMIT TRANSACTION

Is there a way to control the flow so the second update statement won't run until the first statement has been completed? I thought about maybe using a trigger to fire whenever the CountyID field is updated. Other options?

chris

1 variant (for SQL 2000 & SQL 2005):

begin transaction

declare @.ErrorVar int

update .... --The First Update

set @.ErrorVar = @.@.Error

if @.ErrorVar <>0

begin

-- Insert your error hadling code

rollback --For Example rollback transaction

end

else

begin

update ... --The second update

commit

end

2 variant (for SQL 2005 only):

begin tran

begin try

update ... --The first update

--If you have error in fist update you go to catch block

update ... - The second update

commit

end try

begin catch

-- Insert your error hadling code

rollback --For Example rollback transaction

end catch

|||SQL always executes "top down" and completes the first statement before starting the 2nd. What makes you think it is not complete?

The only way I see you would get different results than expected with what you posted, would be if you have the isolation level set to "read uncommitted". You can set the isolation level by using:

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

at the top of your stored proc and that will force all updates to be committed and locks to be placed on the data until you are done.|||

Thanks for the suggestions from both of you. It turns out the problem was a bug in a subsequent UPDATE statement that was changing my ZoneTypeID back to NULL. I fixed the bug, and now the proc works perfectly.

chris

Control of flow around "CREATE PROCEDURE"

Hi there.
I am trying to write a single script to create some stored procedures. One
of the stored procedures however, refers to a database which may or may not
be present on the server. In the case of that database NOT being present, I
would like to create the stored procedure with different contents (as the
original contents cause script errors when the missing database os referred
to). However, I'm having trouble controlling the flow of execution in the
script around CREATE PROCEDURE as it needs to be the first instruction in a
batch.
Basically I'd like to do something like this:
Use SomeOtherDatabase
GO
IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
CREATE PROCEDURE p_MyStoredProc
AS
SELECT * FROM MyDatabaseName.dbo.SomeTable
GO
ELSE --The database doesn't exist
CREATE PROCEDURE p_MyStoredProc
AS
PRINT 'The Database doesnt exist on this server'
GO
The reason I want to take this approach is to avoid script errors when the
script is run on servers where that database is missing.
Any ideas how I should go about this?
Any help would be much appreciated!!Len,
Kinda questionable approach. Try something like this using Dynamic SQL:
IF DB_ID('MyDatabaseName') IS NOT NULL
EXEC('CREATE PROCEDURE ...')
ELSE
EXEC('CREATE PROCEDURE ...')
Also see Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"len" <len@.discussions.microsoft.com> wrote in message
news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
> Hi there.
> I am trying to write a single script to create some stored procedures. One
> of the stored procedures however, refers to a database which may or may
> not
> be present on the server. In the case of that database NOT being present,
> I
> would like to create the stored procedure with different contents (as the
> original contents cause script errors when the missing database os
> referred
> to). However, I'm having trouble controlling the flow of execution in the
> script around CREATE PROCEDURE as it needs to be the first instruction in
> a
> batch.
> Basically I'd like to do something like this:
> Use SomeOtherDatabase
> GO
> IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
> CREATE PROCEDURE p_MyStoredProc
> AS
> SELECT * FROM MyDatabaseName.dbo.SomeTable
> GO
> ELSE --The database doesn't exist
> CREATE PROCEDURE p_MyStoredProc
> AS
> PRINT 'The Database doesnt exist on this server'
> GO
> The reason I want to take this approach is to avoid script errors when the
> script is run on servers where that database is missing.
> Any ideas how I should go about this?
> Any help would be much appreciated!!|||Why not just fix the code that is calling the wrong proc? Seems like an
unusual architecture if neither your client code or your procs will
know whether a database exists or not.
Where possible I find it better to reference other databases only in
views and then write procs against the views. That way views act as
your database indirection and the database names are hard-coded in as
few places as possible.
David Portas
SQL Server MVP
--|||Perfect - thanks! - I had tried dynamic SQL but got stuck on sp_executesql a
s
my stored proc was over 4000 chars long - Erland's article covers this thoug
h
"Jerry Spivey" wrote:

> Len,
> Kinda questionable approach. Try something like this using Dynamic SQL:
> IF DB_ID('MyDatabaseName') IS NOT NULL
> EXEC('CREATE PROCEDURE ...')
> ELSE
> EXEC('CREATE PROCEDURE ...')
> Also see Erland's article:
> http://www.sommarskog.se/dynamic_sql.html
> HTH
> Jerry
> "len" <len@.discussions.microsoft.com> wrote in message
> news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
>
>|||I'm not too happy with the architecture myself! Unfortunately it's a legacy
thing whereby my principal aim was just to minimize the number of scripts
needed to install some additional stored procs.
"David Portas" wrote:

> Why not just fix the code that is calling the wrong proc? Seems like an
> unusual architecture if neither your client code or your procs will
> know whether a database exists or not.
> Where possible I find it better to reference other databases only in
> views and then write procs against the views. That way views act as
> your database indirection and the database names are hard-coded in as
> few places as possible.
> --
> David Portas
> SQL Server MVP
> --
>

Control Flow Task Error shouldn't fail package

Hi all,

I have a Send Mail Task in my control flow to notify users that the processing is done. I want to avoid the package to fall in error if the Send Mail task failed.

What is the best practice to do that ?

Should I raise the MaximumErrorCount of theSend Mail Task ? Should I play with ErrorHandler ?

Try setting the Send Mail Task's "ForceExecutionResult" to "Success"

Control Flow setup

Is it possible to setup a Control Flow at the solution level rather than at the package level? I'd like to setup a Control Flow that truncates multiple tables in a staging database then runs multiple packages that reloads those tables. Each package has a Control Flow tab that seems to be specific to the package. Is it possible to set something up that governs the execution of multiple packages?

Thanks.

SSIS contains a task called the "Execute package Task" which does it exactly what it says on the tin. It is ideally suited to the scenario you mentioned.

-Jamie

|||Since the control flows are all at the package level, if you have a solution with multiple packages, you probably need to just pick a control flow from one of the packages and use that control flow to run all the packages at once using the 'execute package task' you mentioned. There should be control flow objects at the solution level that control the running of all packages. Most etl tools have this.|||

lwrich wrote:

Since the control flows are all at the package level, if you have a solution with multiple packages, you probably need to just pick a control flow from one of the packages and use that control flow to run all the packages at once using the 'execute package task' you mentioned.

I'm not understanding why that is a problem. Can you elaborate?

lwrich wrote:

There should be control flow objects at the solution level that control the running of all packages. Most etl tools have this.

This isn't meant to be as rude as it sounds but I couldn't disagree more. SSIS has this i.e. control-flow objects to control the running of packages as well - packages themselves. It seems you are suggesting that we use SSIS packages for "work" and a completely different tool for workflow.

SSIS packages have a a workflow engine built into them so why would you use something else?

That's not rhetoric. I'm interested to hear your views here.

-Jamie

control flow of execution of statement

is there a way to check to see if the previous sql statement has completely executed before executing the next statement?

I have a stored procedure that basically has several insert statements. At the end of the insert statements I call bcp to write the table to a text file. The first insert will write a header record into the table. Then it will insert a bunch of records that are selected from other tables and then lastly will write the footer record. My dilemna is that for some reason the first insert of the header record isn't actually happening until the middle of the second set of inserts where it inserts several records from another table. so basically my file ends up looking like this

payment record
payment record
payment record
Header Record
payment record
payment record
payment record
payment record
Footer Record

Can I tell it to wait for the first insert to complete before starting the other insert?

Can you post your sp, table structure and a actual sample of the data? SP's by their nature do not execute the next statement until the previous one has completed. I wonder if you have an index on your table that is causing the data to sort in the format that you have shown even though the insert is happening in the correct order...|||

Here is the stored procedure

The table I am inserting stuff into literally is one field. It is just a way to grab and format data from another table and then call bcp to write the data to a text file.

ALTER PROCEDURE [dbo].[PREPAREFILE]
@.DATE_PAID as char(8), @.HEADER as varchar(MAX), @.FOOTER as varchar(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
END

BEGIN
DELETE FROM Temp_Formatted
END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@.HEADER)
END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)

SELECT '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted

END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)

SELECT '7' + '000000000000001' + @.DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
FROM dbo.Temp_Unformatted

END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@.FOOTER)
END

Then I call bcp to write the Temp_formatted data to a text file.

What happens though in both the table and the file I get this:
Stub
Stub
Stub
Header
Stub
Stub
Payment
Payment
Payment
Payment
Payment
Footer

What I need is:
Header
stub
stub
stub
stub
stub
payment
payment
payment
payment
Footer

Of course my example output is scaled down. I have over 40,000 stub and payment records.

|||

Does your table have an index on the formattedRecord column?

Looks the first byte for a stub is always "6", first byte for a payment is "7". What is does the Header record look like, especially the first byte (you are passing as an arguement), what does the Footer record look like, especially the first byte (you are passing as an arguement)?

If a table does not have an index, it will store the data in the format that it receives it. The insert statements in your proc run sequentially (meaning each insert has to complete successfully before the next insert statement executes).

|||well, for testing purposes i've been just passing 'header' for @.header and 'footer' for @.footer. But it will always be different. No, there are no indexes on the the formatted table. No keys no indexes...nothing. I even ran a test with only writing header/payments/footer without stubs and it does basically the same thing. I will get a bunch of payments then the header and then the rest of the payments and then the footer. It is really really weird.|||

I'm at a loss. Do you know what the header and footer rows will look like (really what the first byte will be)? Will it always be the same?

You could get around this by using a query with an order by clause to load your bcp.

As an example, let's say your header will always start with 'h' and your footer will always start with 'f'

Select formattedRecord
From Temp_Formatted
Order by
Case left(formattedRecord, 1)
When 'h' then 1
When '6' then 6
When '7' then 7
When 'f' then 9
Else 8 -- This forces everything else to sort before the footer
End

Try the above query and see if that gives you the order you want.

|||yeah they are going to be different. Gee, you would think this would be pretty simple. I don't get why it is doing it this way. its really odd that it inserts between the stubs. You don't know of anyway to do the check to see if the header is there first?|||If you know what the header will look like (and it is formatted differently than the footer), you can probably modify the order by clause I posted. Only other thing I can suggest is to drop the table and recreate it. I've never seen this happen before.|||

SQL is a set-based language and tables are unordered set of rows. So even if you insert some rows in a particular order you will not be able to read it in the same order without specifying an ORDER BY clause in your SELECT statement. Any other assumption to the order of the rows based on index or query plan is incorrect. The easiest way to solve this problem is to add an identity column to the table and then modify your BCP to use queryout option & issue a SELECT on the table with the ORDER BY clause specifying the identity column. This will ensure that the you can retrieve rows in the order in which you inserted and this assumes that there is only one instance of SP inserting data into the table at any point in time.

Alternatively, you can do this without any table at all like below:

-- PrepareFile SP

SELECT t.Data

FROM (

SELECT 0, @.HEADER

UNION ALL
SELECT 1, '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted

UNION ALL

SELECT 1, '7' + '000000000000001' + @.DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
FROM dbo.Temp_Unformatted

UNION ALL

SELECT 2, @.FOOTER

) as t(SortCol, Data)
ORDER BY t.SortCol

Now, change your BCP to just call this SP using queryout option.

Control flow in scripts and 'GO'

(Same problem as yesterday).
Using controlflow in scripts and using 'GO' in a script
conflicts.
I use the control flow to check if the scripts are run
in the correct order and have not run before.
Some scripts 'create' views so a 'GO' is needed
in those scripts. This conflicts with the controlflow.
Any solutions to this 'problem' ?
ben brugmanControl of flow handling is confined inside a batch, we can't change this. So the 'solution' depends
on what you want to do with the control of flow handling.
If you are after exiting the script on error, you can do a raiserror with state 127 if you execute
the script using OSQL (this doesn't work with QA).
Or do a raiserror with a severe severity level. Check out the scripts for pubs and northwind for how
this can be done.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ben brugman" <ben@.niethier.nl> wrote in message news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
> (Same problem as yesterday).
> Using controlflow in scripts and using 'GO' in a script
> conflicts.
> I use the control flow to check if the scripts are run
> in the correct order and have not run before.
> Some scripts 'create' views so a 'GO' is needed
> in those scripts. This conflicts with the controlflow.
> Any solutions to this 'problem' ?
> ben brugman
>|||Thanks Tibor,
We are using the QA, using the raiserror with a high (above 19) severity
error, I think
is inappropriate and might lead to confusion.
So we have to work around this 'problem'.
> Check out the scripts for pubs and northwind for how
> this can be done.
What scripts are you refering to ? Where ?
Thanks for your time,
ben brugman
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6PSJSj6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> Control of flow handling is confined inside a batch, we can't change this.
So the 'solution' depends
> on what you want to do with the control of flow handling.
> If you are after exiting the script on error, you can do a raiserror with
state 127 if you execute
> the script using OSQL (this doesn't work with QA).
> Or do a raiserror with a severe severity level. Check out the scripts for
pubs and northwind for how
> this can be done.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "ben brugman" <ben@.niethier.nl> wrote in message
news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
> > (Same problem as yesterday).
> >
> > Using controlflow in scripts and using 'GO' in a script
> > conflicts.
> >
> > I use the control flow to check if the scripts are run
> > in the correct order and have not run before.
> > Some scripts 'create' views so a 'GO' is needed
> > in those scripts. This conflicts with the controlflow.
> >
> > Any solutions to this 'problem' ?
> >
> > ben brugman
> >
> >
>|||> What scripts are you refering to ? Where ?
The scripts I'm referring to are named instpubs.sql and instmsdb.sql, I believe. They should be
found in the SQL Server install directory and on the SQL Server install CD. You use these scripts to
re-create the database. However, I believe that they use the high severity level technique.
From where are you executing the scripts? If you use OSQL or ISQL, I suggest you check out the state
127 option.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ben brugman" <ben@.niethier.nl> wrote in message news:eJv0r1j6DHA.3804@.tk2msftngp13.phx.gbl...
> Thanks Tibor,
> We are using the QA, using the raiserror with a high (above 19) severity
> error, I think
> is inappropriate and might lead to confusion.
> So we have to work around this 'problem'.
> > Check out the scripts for pubs and northwind for how
> > this can be done.
> What scripts are you refering to ? Where ?
> Thanks for your time,
> ben brugman
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u6PSJSj6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > Control of flow handling is confined inside a batch, we can't change this.
> So the 'solution' depends
> > on what you want to do with the control of flow handling.
> > If you are after exiting the script on error, you can do a raiserror with
> state 127 if you execute
> > the script using OSQL (this doesn't work with QA).
> > Or do a raiserror with a severe severity level. Check out the scripts for
> pubs and northwind for how
> > this can be done.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
> > > (Same problem as yesterday).
> > >
> > > Using controlflow in scripts and using 'GO' in a script
> > > conflicts.
> > >
> > > I use the control flow to check if the scripts are run
> > > in the correct order and have not run before.
> > > Some scripts 'create' views so a 'GO' is needed
> > > in those scripts. This conflicts with the controlflow.
> > >
> > > Any solutions to this 'problem' ?
> > >
> > > ben brugman
> > >
> > >
> >
> >
>|||>
> From where are you executing the scripts? If you use OSQL or ISQL, I
suggest you check out the state
> 127 option.
We are using the QA for executing the scripts.
ben brugman
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "ben brugman" <ben@.niethier.nl> wrote in message
news:eJv0r1j6DHA.3804@.tk2msftngp13.phx.gbl...
> > Thanks Tibor,
> >
> > We are using the QA, using the raiserror with a high (above 19) severity
> > error, I think
> > is inappropriate and might lead to confusion.
> > So we have to work around this 'problem'.
> >
> > > Check out the scripts for pubs and northwind for how
> > > this can be done.
> > What scripts are you refering to ? Where ?
> >
> > Thanks for your time,
> > ben brugman
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:u6PSJSj6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > Control of flow handling is confined inside a batch, we can't change
this.
> > So the 'solution' depends
> > > on what you want to do with the control of flow handling.
> > > If you are after exiting the script on error, you can do a raiserror
with
> > state 127 if you execute
> > > the script using OSQL (this doesn't work with QA).
> > > Or do a raiserror with a severe severity level. Check out the scripts
for
> > pubs and northwind for how
> > > this can be done.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
> > > > (Same problem as yesterday).
> > > >
> > > > Using controlflow in scripts and using 'GO' in a script
> > > > conflicts.
> > > >
> > > > I use the control flow to check if the scripts are run
> > > > in the correct order and have not run before.
> > > > Some scripts 'create' views so a 'GO' is needed
> > > > in those scripts. This conflicts with the controlflow.
> > > >
> > > > Any solutions to this 'problem' ?
> > > >
> > > > ben brugman
> > > >
> > > >
> > >
> > >
> >
> >
>

Control flow in scripts and 'GO'

(Same problem as yesterday).
Using controlflow in scripts and using 'GO' in a script
conflicts.
I use the control flow to check if the scripts are run
in the correct order and have not run before.
Some scripts 'create' views so a 'GO' is needed
in those scripts. This conflicts with the controlflow.
Any solutions to this 'problem' ?
ben brugmanControl of flow handling is confined inside a batch, we can't change this. S
o the 'solution' depends
on what you want to do with the control of flow handling.
If you are after exiting the script on error, you can do a raiserror with st
ate 127 if you execute
the script using OSQL (this doesn't work with QA).
Or do a raiserror with a severe severity level. Check out the scripts for pu
bs and northwind for how
this can be done.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"ben brugman" <ben@.niethier.nl> wrote in message news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...[QUO
TE]
> (Same problem as yesterday).
> Using controlflow in scripts and using 'GO' in a script
> conflicts.
> I use the control flow to check if the scripts are run
> in the correct order and have not run before.
> Some scripts 'create' views so a 'GO' is needed
> in those scripts. This conflicts with the controlflow.
> Any solutions to this 'problem' ?
> ben brugman
>[/QUOTE]|||Thanks Tibor,
We are using the QA, using the raiserror with a high (above 19) severity
error, I think
is inappropriate and might lead to confusion.
So we have to work around this 'problem'.

quote:

---

> Check out the scripts for pubs and northwind for how
> this can be done.

---

What scripts are you refering to ? Where ?
Thanks for your time,
ben brugman
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6PSJSj6DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Control of flow handling is confined inside a batch, we can't change this.

---

So the 'solution' depends

quote:

---

> on what you want to do with the control of flow handling.
> If you are after exiting the script on error, you can do a raiserror with

---

state 127 if you execute

quote:

---

> the script using OSQL (this doesn't work with QA).
> Or do a raiserror with a severe severity level. Check out the scripts for

---

pubs and northwind for how

quote:

---

> this can be done.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "ben brugman" <ben@.niethier.nl> wrote in message

---

news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...

quote:

---

>

---

|||> What scripts are you refering to ? Where ?
The scripts I'm referring to are named instpubs.sql and instmsdb.sql, I beli
eve. They should be
found in the SQL Server install directory and on the SQL Server install CD.
You use these scripts to
re-create the database. However, I believe that they use the high severity l
evel technique.
From where are you executing the scripts? If you use OSQL or ISQL, I suggest
you check out the state
127 option.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"ben brugman" <ben@.niethier.nl> wrote in message news:eJv0r1j6DHA.3804@.tk2msftngp13.phx.gbl...[QUO
TE]
> Thanks Tibor,
> We are using the QA, using the raiserror with a high (above 19) severity
> error, I think
> is inappropriate and might lead to confusion.
> So we have to work around this 'problem'.
>
> What scripts are you refering to ? Where ?
> Thanks for your time,
> ben brugman
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:u6PSJSj6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> So the 'solution' depends
> state 127 if you execute
> pubs and northwind for how
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
>[/QUOTE]|||>

quote:

---

> From where are you executing the scripts? If you use OSQL or ISQL, I

---

suggest you check out the state

quote:

---

> 127 option.

---

We are using the QA for executing the scripts.
ben brugman

quote:

---

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "ben brugman" <ben@.niethier.nl> wrote in message

---

news:eJv0r1j6DHA.3804@.tk2msftngp13.phx.gbl...

quote:

---

in[QUOTE]
this.[QUOTE]
with[QUOTE]
for[QUOTE]
http://groups.google.com/groups?oi=...ublic.sqlserver[QUOTE]
>

---

Control flow drawing bug

Minor issue here. But I just wanted to point it out.

I think this is how to reproduce it. If I disable a dataflow task and run the package, after it is complete, but yet still in the debugger, reenable the dataflow task. When I close the debugger, the dataflow task is still shaded as if it were disabled, but it will execute on the next run.

Close the package and re-open it and the work flow task is drawn correctly.

Thanks for the report, Jim. What build is this with? We've fixed one of these occurrences recently.

regards,
ash|||I am still on the April CTP.|||OK. I believe this has been fixed in the next CTP (CTP16). Please review when you get that, thanks!

Control Flow Diverging then Converging Back

I am somewhat new to SSIS and have a question on branching/control flow. We have 3 manufacturing facilities on AS400. Requirement were to create independent SSIS packages in case certain facilities were down, to not interrupt or fail other facilities. to be honest, I didn't want to maintain 3 * (each plant package) packages and decided to create one that takes in a command line parameter and will create seperate batch files (requirements for our archaic scheduling system) that pass in the plant to be processed.

Everything works fine and the packages analyze the passed parameter with no problem and evaluate it when calling stored procedures int a SQL Task. I am required (I think - let me know if I'm wrong here) to have 3 seperate Data Flow tasks depending on which plant is being processed because they are different connections (same server, different DBs - AS400). Which Data Flow Taks run is based on a precedence constraint that analyzes the passed in plant number.

After the Data Flow Tasks are complete, I'd like to merge back into one path again (even though1 out of N will only run at a time) because they are all simply stored proc calls with different parameters. It seemed silly to me to have the same tasks repeated 3 different times. I looked into started up tasks after events, but I don't know a lot about it and it seems as though I would still be maintaining 3 different paths anyway.

Can anyone point me in the right direction?

P.S. (Even if the sollution is to have 1 Data Flow Task I'm still interested in knowing how to converge paths back together)

You can take an upstream control flow item and hook it to three data flows. Then coming out of each data flow you can hook them up to one downstream control flow item. Then you'll just have to work with your precedence constraints to ensure that if one doesn't run the whole package doesn't fail.|||

If I'm undertanding you correctly, that's exactly what I tried. I should have mentioned that. The problem is that the package stops (with success) after the data flow is complete. I assumed it was because all but one of the paths coming to the post-data-flow task did not even execute (which is what I want) so they did not pass a complete or success flag tp the first post-data-flow task. So in short, everything runs great, it just stops as soon as the data flow task is complete.

Is there some other condition that I can put on the post-data-flow constraints? Or another toolbox item that I can use to connect them?

BTW. I appreciate the quick response!

|||You can change the precedence of those connectors. Double click on the connector line and you'll see the options available to you. Look at using "OR" in the multiple conditions.|||

Yahtzee!!

Totally my bad. I had THOUGHT I set them all to "OR", but your suggestion made me check again. One was set to "AND".

Much appreciated!

Control Flow design question

hi all of you,

I haven’t idea if the following description is a issue or not, anyway:

I begin from Control Flow layer.

I’ve created a sequence container and inside I’ve got two groups, one own a sql task and another one own a Data Flow task. Both are linked for a completion conector. Up to here everything is fine. But when I collapse my sequence container the arrow remains there for these tasks and you can see the sequence container “closed” and the arrow lonely.

Not very esthetic, not practical.

Any clarification or though will be as usual welcomed

I've seen similar problems in the past. I think this is termed a "ghost" - meaning that the UI is getting lost somehow.

The SSIS team will want to know about this so bug it at Microsoft Connect with a screenshot! And if you post a repro - even better!

-Jamie

|||

hi Jamie,

Well, at first if you collapse both groups and then the sequence container the arrow is not visible at all..

f..stuff

|||

I don't doubt it. These things are hard to reproduce and we shouldn't try and explain why they happen. If you can repro it on demand tho - you should bug it.

-Jamie

|||

Maybe the problem is that we're assuming that BIDS have capabilities such as the ones that shows VISIO or something like that. Error.

see you

|||

Did you raise the bug?

|||No, laziness

Consuming a Recordset in a Script Transformation

I have a situation where I have created a recordset in a previous data flow task where the output was a recordset.

Now I want to write that recordset to a flat file.

There is no direct recordset input data flow source. So I thought I would use a source script transformation to read the recordset. Then I searched on this and found no information on how I might do this.

Anyone have any ideas on how to do this in a script transformation?

There are reasons that I would like to do it this way, but if I have to choose another way, well that's life.

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

|||

DarrenSQLIS wrote:

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

I echo what Darren has said. And if you need proof, go here:

Comparing performance of a raw file against a recordset destination
http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4159.aspx

-Jamie