Disconnected Edit in SSIS 2005

Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in SQL
Server 2005 Integration Services ? If so, What ?
Thanks.In SSIS you can accomplish editing packages without a connection to the SQL
Server Several different ways.
If you are using file system deployment you can just make sure that your
development folder for the dtsx files is something different then the folder
that the agent is executing packages from.
If you are using SQL Server deployment then every package that you are
working is essentially disconnected. You have to deploy it to the sql
server using the deploy option inside of the business intelligence studio or
a package installer.
In both cases, you do not need any type of connection to the SQL server
until you deploy. You will lose some niceties that way since you cant
connect to get column or table info.
Make sense?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
> Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in
> SQL
> Server 2005 Integration Services ? If so, What ?
> Thanks.|||No it doesn't. Here is why,
I imported a DTS package from a SQL Server 2000 server to 2005 Integration
services on another SQL Server 2005 computer. Regardles of where I open it,
it takes at least 20 second to scroll the horizantal bar 1 inch. I am
assuming that it is trying to check the resources/connections even though
they don't exists on the new server. This is why I thought that disconnected
edit may help to speed editing the DTS package or the SSIS package since it
has been converted..........
Thanks.
"Warren Brunk" wrote:
> In SSIS you can accomplish editing packages without a connection to the SQL
> Server Several different ways.
> If you are using file system deployment you can just make sure that your
> development folder for the dtsx files is something different then the folder
> that the agent is executing packages from.
> If you are using SQL Server deployment then every package that you are
> working is essentially disconnected. You have to deploy it to the sql
> server using the deploy option inside of the business intelligence studio or
> a package installer.
> In both cases, you do not need any type of connection to the SQL server
> until you deploy. You will lose some niceties that way since you cant
> connect to get column or table info.
> Make sense?
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
> > Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in
> > SQL
> > Server 2005 Integration Services ? If so, What ?
> >
> > Thanks.
>
>

Disconnected Edit in SSIS 2005

Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in SQL
Server 2005 Integration Services ? If so, What ?
Thanks.In SSIS you can accomplish editing packages without a connection to the SQL
Server Several different ways.
If you are using file system deployment you can just make sure that your
development folder for the dtsx files is something different then the folder
that the agent is executing packages from.
If you are using SQL Server deployment then every package that you are
working is essentially disconnected. You have to deploy it to the sql
server using the deploy option inside of the business intelligence studio or
a package installer.
In both cases, you do not need any type of connection to the SQL server
until you deploy. You will lose some niceties that way since you cant
connect to get column or table info.
Make sense?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
> Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in
> SQL
> Server 2005 Integration Services ? If so, What ?
> Thanks.|||No it doesn't. Here is why,
I imported a DTS package from a SQL Server 2000 server to 2005 Integration
services on another SQL Server 2005 computer. Regardles of where I open it,
it takes at least 20 second to scroll the horizantal bar 1 inch. I am
assuming that it is trying to check the resources/connections even though
they don't exists on the new server. This is why I thought that disconnected
edit may help to speed editing the DTS package or the SSIS package since it
has been converted..........
Thanks.
"Warren Brunk" wrote:

> In SSIS you can accomplish editing packages without a connection to the SQ
L
> Server Several different ways.
> If you are using file system deployment you can just make sure that your
> development folder for the dtsx files is something different then the fold
er
> that the agent is executing packages from.
> If you are using SQL Server deployment then every package that you are
> working is essentially disconnected. You have to deploy it to the sql
> server using the deploy option inside of the business intelligence studio
or
> a package installer.
> In both cases, you do not need any type of connection to the SQL server
> until you deploy. You will lose some niceties that way since you cant
> connect to get column or table info.
> Make sense?
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
>
>

Disconnected Edit in SSIS 2005

Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in SQL
Server 2005 Integration Services ? If so, What ?
Thanks.
In SSIS you can accomplish editing packages without a connection to the SQL
Server Several different ways.
If you are using file system deployment you can just make sure that your
development folder for the dtsx files is something different then the folder
that the agent is executing packages from.
If you are using SQL Server deployment then every package that you are
working is essentially disconnected. You have to deploy it to the sql
server using the deploy option inside of the business intelligence studio or
a package installer.
In both cases, you do not need any type of connection to the SQL server
until you deploy. You will lose some niceties that way since you cant
connect to get column or table info.
Make sense?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
> Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in
> SQL
> Server 2005 Integration Services ? If so, What ?
> Thanks.
|||No it doesn't. Here is why,
I imported a DTS package from a SQL Server 2000 server to 2005 Integration
services on another SQL Server 2005 computer. Regardles of where I open it,
it takes at least 20 second to scroll the horizantal bar 1 inch. I am
assuming that it is trying to check the resources/connections even though
they don't exists on the new server. This is why I thought that disconnected
edit may help to speed editing the DTS package or the SSIS package since it
has been converted..........
Thanks.
"Warren Brunk" wrote:

> In SSIS you can accomplish editing packages without a connection to the SQL
> Server Several different ways.
> If you are using file system deployment you can just make sure that your
> development folder for the dtsx files is something different then the folder
> that the agent is executing packages from.
> If you are using SQL Server deployment then every package that you are
> working is essentially disconnected. You have to deploy it to the sql
> server using the deploy option inside of the business intelligence studio or
> a package installer.
> In both cases, you do not need any type of connection to the SQL server
> until you deploy. You will lose some niceties that way since you cant
> connect to get column or table info.
> Make sense?
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
>
>
sql

Disconnect All Ussers But Me

I have setup a small SSIS package that will regularly delete my log files by detaching the database, removing the file and then reattaching it.

Problem I'm having is that there is almost always at least one other connection to the database and I have to kill it for this to work.

Is there any TSQL command that allows me to shut down all open connections except the one the command is coming from?

There is no command like that. But you could use ALTER DATABASE to set the database in single user or restricted user mode. This may or may not work depending on the permissions of the login used by the SSIS package and how often the clients reconnect etc. Check out the ALTER DATABASE topic and see.

Btw, why are you recreating the log file periodically? What is the purpose?

|||

just to add what Umachander has already told.. read about Alter Database and Singler_User in BOL first

ALTER DATABASE SomeDatabase SET SiNGLE_USER WITH ROLLBACK IMMEDIATE;

and ofcourse answer the abovementioned questions also... very risky and unethical method of truncating and shrinking log.. not atall recommended and you should not do this. there are another /professional approach to control the growth and size of log file.

Refer this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1272445&SiteID=1

Madhu

|||Thank you, I'll check those links out.

As for the reason, right now I inherited a database that is running some heavy SSIS packages, every time these run they do a lot of copying and deleting, in the end they inflate the log files drastically. However we don't really need the log files at all, we have all the data ready to be loaded in backup tapes should anything drastic happen.

I bet the SSIS packages can use a lot of optimization but for the time being I'm not in control of these and was instructed to take care of the logs ASAP. I already did this process manually and it worked so I figured it may be an OK temporary solution to run weekly until I got a hold of what is truly going on in this database and how to optimize it.

Heck, for the purposes of this project, if it was possible to disable logging as a permanent option, I'd do it.
|||If you aren't worried about he log files, then set the database recovery model to "simple". That will keep the logs much smaller and increase the speed of updates to the tables.

The way to shrink the log files is to use a maintenance plan. You can back it up and shrink the logs at night, which is what you are trying to do anyway.

Disconnect All Ussers But Me

I have setup a small SSIS package that will regularly delete my log files by detaching the database, removing the file and then reattaching it.

Problem I'm having is that there is almost always at least one other connection to the database and I have to kill it for this to work.

Is there any TSQL command that allows me to shut down all open connections except the one the command is coming from?

There is no command like that. But you could use ALTER DATABASE to set the database in single user or restricted user mode. This may or may not work depending on the permissions of the login used by the SSIS package and how often the clients reconnect etc. Check out the ALTER DATABASE topic and see.

Btw, why are you recreating the log file periodically? What is the purpose?

|||

just to add what Umachander has already told.. read about Alter Database and Singler_User in BOL first

ALTER DATABASE SomeDatabase SET SiNGLE_USER WITH ROLLBACK IMMEDIATE;

and ofcourse answer the abovementioned questions also... very risky and unethical method of truncating and shrinking log.. not atall recommended and you should not do this. there are another /professional approach to control the growth and size of log file.

Refer this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1272445&SiteID=1

Madhu

|||Thank you, I'll check those links out.

As for the reason, right now I inherited a database that is running some heavy SSIS packages, every time these run they do a lot of copying and deleting, in the end they inflate the log files drastically. However we don't really need the log files at all, we have all the data ready to be loaded in backup tapes should anything drastic happen.

I bet the SSIS packages can use a lot of optimization but for the time being I'm not in control of these and was instructed to take care of the logs ASAP. I already did this process manually and it worked so I figured it may be an OK temporary solution to run weekly until I got a hold of what is truly going on in this database and how to optimize it.

Heck, for the purposes of this project, if it was possible to disable logging as a permanent option, I'd do it.|||If you aren't worried about he log files, then set the database recovery model to "simple". That will keep the logs much smaller and increase the speed of updates to the tables.

The way to shrink the log files is to use a maintenance plan. You can back it up and shrink the logs at night, which is what you are trying to do anyway.

Disappearing Precedence Constraints

Has anyone seen precedence constraints disappear in a package after closing and opening again? In this case, it's not package-wide. Only constraints inside one Foreach Loop container disappeared. Any idea what causes this to happen?

p.s: Yes, I did Save All. Most of these contraints were saved in the package for more than a week anyway.

I have not seen that. Is your package under any source control software? is that the case compare it against previous versions. A similar issue has been reported for many of us; but the problem was inside the Dataflow (when using VSS as source control)though....

|||

Hi M. Glenn,

Yep, I've seen it too.

I've seen this and other strange behavior when my Visual Studio environment has been running for days on end (it happens...). My solution is to shut it down and restart the environment. Sometimes the metadata for the objects remains out of sync and the only way to get it back is to either copy it and paste it into a new package or recreate it.

Hope this helps,

Andy

|||

Thanks for the quick replies. "...Visual Studio environment...running for days on end": Yeah that's me alright. We're not using any source control utilities, but I was in the habit of leaving VS open for days while working on projects. Not any more. I save frequently, but obviously that's not enough.

I could see and edit the constraints in Package Explorer, and tasks that allow only one connection would correctly return an error if I tried to add another constraint, but they were invisible in Control Flow designer. Closing and restarting the project and/or VS didn't help. Selecting all tasks in the container and dragging them outside the container made the connecters visible again. Then just drag everything back and all is well. Thanks for the tip.

BTW, I'm using VS 2005 Pro--installed after SS05/BIDS. I wonder if those using BIDS without a full VS 2005 install are experiencing this issue. Actually, I’m impressed with how stable SS05 and VS05 are--especially considering how much more complex and feature-filled these products are compared to their predecessors.

|||

M.Glenn wrote:

BTW, I'm using VS 2005 Pro--installed after SS05/BIDS. I wonder if those using BIDS without a full VS 2005 install are experiencing this issue. Actually, I’m impressed with how stable SS05 and VS05 are--especially considering how much more complex and feature-filled these products are compared to their predecessors.

Oh my word. A realist!

Thanks M Glenn. You've restored my faith in the community following some particularly galling diatribes of late!

|||

Jamie, those guys must not realize how much progress these products have made in terms of functionality, stability, performance and ease-of-use features (although overall complexity is an unavoidable companion of added functionality). Add to this a world-class customer feedback/support infrastructure (including newsgroups like this one). We all have complaints about Microsoft, but that shouldn't completely blind someone to what's been accomplished here.

The SS and VS "2005" releases are nothing short of amazing. And whoever thought of selling SS05 Developer Edition for $50 is a genius. A year ago I couldn't even get our CIO to spring for that. Ironically, there are so many compelling reasons to upgrade it created a problem of how to fit it all in an executive summary. But DBA's and developers could recognize something special early on. I shelled out $50 from my own pocket and got wowed right off the bat just watching the install routine! Before long I was hounding the CIO without mercy. He eventually gave in when some funds came available.

Recently, I automated report creation, delivery and notification to 40+ healthcare providers with an SSIS package so simple it would make folks who hang out here yawn. But it made me and the CIO look like geniuses. That's what I call progress!

|||

Hi M.Glenn,

Cool! I have to agree with Jamie - it's nice to read about someone's successes with the product.

The forum is similar to a doctor's office - no one shows up and says "Hi folks, everything's fine! See ya!" They usually pop in when something bad is happening or about to happen.

Thanks,
Andy

|||

Andy Leonard wrote:

The forum is similar to a doctor's office - no one shows up and says "Hi folks, everything's fine! See ya!" They usually pop in when something bad is happening or about to happen.

Good analogy. Helps keep the complaints in perspective.

Disable SSIS Studio validations?

Is there any way to disable the validations within the package upon startup of the SSIS studio? We have a few really large packages that can take almost an hour to validate, since the packages could be pointed to different environments (depending on each developer). The alternative (which could get messy) is to alter the *.dtsx file to point the connections to the correct DB before starting them up in studio.

Any ideas?Try setting DelayValidation=TRUE on the package.
-Jamie|||You can use configurations so that the connections point to the correct place on any server (as long as the configuration is valid and that way you don't have to alter the dtsx file directly).

You can switch into offline mode before opening the package.

You can use DelayValidation set to true.

HTH,
Matt

Disable package validation

Hy everybody,

I just one question : How can I disable the package validation when I open it ?

When I collect the packages from the production environment, I open them on my pc and I wait 5 minutes by package while the validation are complete... It's so long because the connection point to the production and they must point to the test environment (My pc isn't connected to the network )

To make this change, I must wait 5 long minutes by package and I'm eager...

Someone have the answer ?

regards

Arnaud

Select "Work Offline" on the SSIS menu in BIDS.

-Jamie

|||

thanks a lot Jamie

Have a nice afternoon

Arnaud

Disable Job Steps

Hi
I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
packages. Each package is run as a separate step in the job. I am busy
testing the job and want to skip some of the longer running packages for now.
Is there a way to disable a job step and then enable it again when I am
ready. Deleting the step and recreating it again is one option, but there
must be a better way
ThanksTake a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e158802c-c347-4a5d-bf75-c03e5ae56e6b.htm
"Trevor Howe" <TrevorHowe@.discussions.microsoft.com> wrote in message
news:13A6603B-003D-4BD8-805F-F37C8AC23A6F@.microsoft.com...
> Hi
> I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> packages. Each package is run as a separate step in the job. I am busy
> testing the job and want to skip some of the longer running packages for
> now.
> Is there a way to disable a job step and then enable it again when I am
> ready. Deleting the step and recreating it again is one option, but there
> must be a better way
> Thanks|||Trevor Howe wrote:
> Hi
> I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> packages. Each package is run as a separate step in the job. I am busy
> testing the job and want to skip some of the longer running packages for now.
> Is there a way to disable a job step and then enable it again when I am
> ready. Deleting the step and recreating it again is one option, but there
> must be a better way
> Thanks
On each job step you have to option to decide what to do on "Success"
and on "Failure". Here you can choose to go to certain step in the job
on e.g. "Success".
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks guys. That was fast!! I also found the solution about five minutes
after I sent out the original post. Would'nt it be nice if all responses were
as successful and quick as this one. Now for my next mind bender: What is the
first name of the founder of Microsoft - :0) - Jokes
""Steen Schlüter Persson (DK)"" wrote:
> Trevor Howe wrote:
> > Hi
> >
> > I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> > packages. Each package is run as a separate step in the job. I am busy
> > testing the job and want to skip some of the longer running packages for now.
> > Is there a way to disable a job step and then enable it again when I am
> > ready. Deleting the step and recreating it again is one option, but there
> > must be a better way
> >
> > Thanks
> On each job step you have to option to decide what to do on "Success"
> and on "Failure". Here you can choose to go to certain step in the job
> on e.g. "Success".
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Disable Job Steps

Hi
I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
packages. Each package is run as a separate step in the job. I am busy
testing the job and want to skip some of the longer running packages for now
.
Is there a way to disable a job step and then enable it again when I am
ready. Deleting the step and recreating it again is one option, but there
must be a better way
ThanksTake a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e158802c-c347-4a5d-bf75-
c03e5ae56e6b.htm
"Trevor Howe" <TrevorHowe@.discussions.microsoft.com> wrote in message
news:13A6603B-003D-4BD8-805F-F37C8AC23A6F@.microsoft.com...
> Hi
> I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> packages. Each package is run as a separate step in the job. I am busy
> testing the job and want to skip some of the longer running packages for
> now.
> Is there a way to disable a job step and then enable it again when I am
> ready. Deleting the step and recreating it again is one option, but there
> must be a better way
> Thanks|||Trevor Howe wrote:
> Hi
> I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> packages. Each package is run as a separate step in the job. I am busy
> testing the job and want to skip some of the longer running packages for n
ow.
> Is there a way to disable a job step and then enable it again when I am
> ready. Deleting the step and recreating it again is one option, but there
> must be a better way
> Thanks
On each job step you have to option to decide what to do on "Success"
and on "Failure". Here you can choose to go to certain step in the job
on e.g. "Success".
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks guys. That was fast!! I also found the solution about five minutes
after I sent out the original post. Would'nt it be nice if all responses wer
e
as successful and quick as this one. Now for my next mind bender: What is th
e
first name of the founder of Microsoft - :0) - Jokes
""Steen Schlüter Persson (DK)"" wrote:

> Trevor Howe wrote:
> On each job step you have to option to decide what to do on "Success"
> and on "Failure". Here you can choose to go to certain step in the job
> on e.g. "Success".
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

disable guest account in MSDB

I'm trying to enforce DTS package managment security in an
Enterprise Enviroment. Microsoft has published KB Article 282463
related, recommending disable guest account in msdb. But,
what it's the impact of doing that? it's just a simple
"execute sp_revokedbaccess 'guest'" ' will everything continue
functioning normally?
Thanks
--
Javier Herrera P.
MCDBA - OCP
SQL*Technology
Santiago - Chile
--Generally things would be okay but it depends on if you have
anything setup or configured that relies on users gaining
access to msdb through the guest account. You'd need to
assess that based on your databases, apps, etc. Removing
guest from msdb means that users would need be granted
access to msdb in order to execute or access anything that
the guest account access through permissions via the public
role.
-Sue
On Mon, 2 Aug 2004 06:55:05 -0700, "Javier Herrera P."
<Javier Herrera P.@.discussions.microsoft.com> wrote:

>I'm trying to enforce DTS package managment security in an
>Enterprise Enviroment. Microsoft has published KB Article 282463
>related, recommending disable guest account in msdb. But,
>what it's the impact of doing that? it's just a simple
>"execute sp_revokedbaccess 'guest'" ' will everything continue
>functioning normally?
>Thanks
>--
>Javier Herrera P.
>MCDBA - OCP
>SQL*Technology
>Santiago - Chile
>--

Disable a task in the current package programmatically?

Hi,

I have a package comprising a number of Data Flow Task steps, to import various tables of data from some Access databases into SQL Server. The name of some of these Access databases will change depending on the date, e.g. last year's data is currently in a database called "2005data.mdb". At year end this will be superseded by "2006data.mdb". The Access databases are within a 3rd party system so I have no control over the file names.

I have a Script Task that checks the current date, and changes the name of an Access database in the connection string to reflect last year's date. But to complicate matters, last year's file might not exist.

So the Script Task checks whether the file name exists. If not, I would like to disable the Task that uses this connection. But how do I reference a task within the package that contains my Script Task, by name, to set the Disabled attribute to true?

I want to do something like CurrentPackage.Tasks("MyDataFlowTask").Disabled = True.

I would also welcome alternative suggestions for how to achieve this.

Many thanks,

Keith.

You cannot reference inwards within a package, so you cannot reference a task. The task must reference the value. The way to do this is to set a variable, and use a property expression that uses that variable value. So you would set a variable, DisableMyTask as true to false in the script then set the property expression on the task's Disable property to that variable, "@.DisableMyTask".

I would however be careful using the Disable property, as the evaluation can be done before you set the variable, so it is too late. A better way to do this is to use an expression on Workflow. Have a constraint leading to your task, and then just add the variable as the test, or rather the negative, so that the constraint is only satisfied when the variable is false. If you have no preceding task to anchor the constraint, just use a sequence container. It will act as an anchor, but do nothing else.

|||

Keith,

You can also add an Expression to the constraint from your Script Task that is modifying the connection string. By doing so you can control the flow without having to enable/disable the invalid tasks.

Here's a link on how to add expressions to a precendence constraint: http://msdn2.microsoft.com/en-us/library/ms140040.aspx

Cheers,
Patrik

|||

Hi,

Thanks for the replies. I have tried adding expressions to the precedence constraints, but it is not working correctly.

I originally had 7 Tasks, with a simple precedence constraint of Success between one and the next. I've now added an expression based on a variable, and changed the precedence constraint to "Constraint and Expression", but with this change, steps 5 to 7 no longer execute. They are still validated, but do not Start.

In simplistic terms, the logic I have added is:

After Task 3, if Success and (@.boolPCStatsLastYearExists) then Execute Task 4.

After Task 4, if Success, execute Task 5.

After Task 3, if Success and (@.boolPCStatsLastYearExists = False) then Execute Task 5.

So, whether the boolean value @.boolPCStatsLastYearExists is True or False, Task 5 should be executed. In fact I think it is set to True, because Task 4 executes and creates rows in the database, so it appears Task 4 is executing but Task 5 isn't.

I am developing on my local machine, but cannot run locally (I get an error on the first Data Flow Task), because the SQL database is on a server. So I am deploying to the server and running there.

Can you advise how I should get debugging information to indicate what happens after Task 4 executes, and why Task 5 isn't reached?

Many thanks,

Keith.

|||

To debug stuff, use the debugger. Creating a mock DB on your local machine would be a good idea.

Sounds like you have two constraints leading to task 5, but they follow different, mutually exclusive execution paths. So you will never satisfy both constraints in a single execution, correct? If so, have you turned the Or logic option on for those constsraints? It is in the constraint UI, or the LogicalAnd property of the constraint, set it to false.

|||

Hi again,

Thanks for that. I had left the default setting (AND) on the constraints. Changing all the constraints where 2 constraints affect one task, led to some progress.

Tasks 1 - 6 now execute fine. But Task 7 never executes.

The constraints on it are:

From Task 5, Success AND @.boolPCFDR1LastYearExists = False

OR

From Task 6, Success.

The first of these conditions should be met, as Task 5 succeeds and writes data, and the boolean is set to False by Task 1 (the Script task), which I can verify in the debugger.

I would greatly appreciate any further suggestions. However, I suspect I will now need to install SQL Server on my already overloaded laptop and recreate the server database locally, so that I can debug. But having looked at the debugger, I am not confident I will have a sufficiently granular view to identify the problem. I'll report back!

Thanks for all your help so far,

Keith.

|||

Hi,

OK, I've installed SQL Server locally, copied the database to it, and changed my connection to point to the local database. The same error occurs, and I don't see how to use the debugger to help me resolve it.

I can put a stop in Step 5 Post-Execute, but I can't do any more than verify that the boolean is set to False as expected. What can I do to identify why the processor does not proceed to execute Step 7?

Thanks,

Keith.

|||

Solved! (Sort of). I've had a problem with conditions on a precedence constraint. I can set a condition of @.myBooleanVariable, and it executes correctly, only when the variable is set to true. However, I can't find a syntax to say execute when this boolean is False. I tried the obvious NOT @.myBooleanVariable, but this does not pass the syntax check. @.myBooleanVariable = False passes the syntax check, but crucially, does not execute. I've not found another syntax that is accepted.

So reluctantly, I created 2 new variables, set them to the boolean opposite of the 2 existing variables, and used them in my "negative" conditions. So all my conditions on Precedence Constraints now comprise just the name of a boolean variable, prefixed with @..

Thanks,

Keith.

|||

Keith,

In the expressions not is represented with ! and equivalency is tested with ==. This should help you remove the additional variable.

Cheers,
Patrik