Run DTS Olap Package

Dear Friends,

I needed to know how to solve this situation.

I have a DTS with OLAP objects and when I schedule the execution is always to give mistake in the Customer's servant...

In our facilities it is always to work well...

Did anybody already try problems of this type?

Thank you
Paulo A. J. PiresUsually DTS includes names of involved servers, accounts, etc. Is it all the same on customer server?|||You are right the names are not the same ones...

But it was to trust the programmer that is to install DTS in the Customer (as always they did)...

Where do I can me to see the servers where it is linked?

Thank you,
Paulo A. J. Pires

Run all .sql files (scripts) in project (SQL Server 2005 SP2)

Hello,

I've run as many keyword combinations searching for this as I can
think of, but cannot find a simple answer.

The situation: I have a database that is mid-conversion/redevelopment.
I frequently rebuild my dev copy from the production version, which
requires a bunch of DDL script, and then adding in all the stored
procedures necessary to the new version only. Each sp may have changed
since the last time it was run, so I can't exactly keep them all
concatenated in a single file or anything like that.

So the only way I can see to execute each and every one of these saved
script files is to open it up in SSMS and hit F5 for execute. This is
a silly waste of time, it seems to me, and hardly foolproof. Is there
not some simple way to run every script in a project? What purpose
does a project serve, otherwise?

(Worth mentioning that the tool I found here
http://www.codeproject.com/useritem...criptRunner.asp does not
seem to work.)

Thanks in advance.downwitch (downwitch@.gmail.com) writes:

Quote:

Originally Posted by

I've run as many keyword combinations searching for this as I can
think of, but cannot find a simple answer.
>
The situation: I have a database that is mid-conversion/redevelopment.
I frequently rebuild my dev copy from the production version, which
requires a bunch of DDL script, and then adding in all the stored
procedures necessary to the new version only. Each sp may have changed
since the last time it was run, so I can't exactly keep them all
concatenated in a single file or anything like that.
>
So the only way I can see to execute each and every one of these saved
script files is to open it up in SSMS and hit F5 for execute. This is
a silly waste of time, it seems to me, and hardly foolproof. Is there
not some simple way to run every script in a project? What purpose
does a project serve, otherwise?

SQL code should be kept under source control, just likely any other code,
and deployment should be done from the version-control system.

Having said that, there are a couple of options you consider. One is
SQL Compare from Red Gate, although they currently have no direct support
for Source Control. The latest version supports comparing a database to
folder structure on disk, though.

A much more simple-minded way is to get a list of all stored procedures
you need to load. Put that in a text file, and then use a text editor
with good find-replace capabilities to transform the list to a BAT
file that runs SQLCMD to load the files. Just make sure that you run
SQLCMD with the -I option, so that you run with QUOTED_IDENTIFIER ON.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Aug 22, 5:52 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

downwitch (downwi...@.gmail.com) writes:

Quote:

Originally Posted by

I've run as many keyword combinations searching for this as I can
think of, but cannot find a simple answer.

>

Quote:

Originally Posted by

The situation: I have a database that is mid-conversion/redevelopment.
I frequently rebuild my dev copy from the production version, which
requires a bunch of DDL script, and then adding in all the stored
procedures necessary to the new version only. Each sp may have changed
since the last time it was run, so I can't exactly keep them all
concatenated in a single file or anything like that.

>

Quote:

Originally Posted by

So the only way I can see to execute each and every one of these saved
script files is to open it up in SSMS and hit F5 for execute. This is
a silly waste of time, it seems to me, and hardly foolproof. Is there
not some simple way to run every script in a project? What purpose
does a project serve, otherwise?

>
SQLcode should be kept under source control, just likely any other code,
and deployment should be done from the version-control system.
>
Having said that, there are a couple of options you consider. One isSQLComparefrom Red Gate, although they currently have no direct support
for Source Control. The latest version supports comparing a database to
folder structure on disk, though.
>
A much more simple-minded way is to get a list of all stored procedures
you need to load. Put that in a text file, and then use a text editor
with good find-replace capabilities to transform the list to a BAT
file that runs SQLCMD to load the files. Just make sure that you run
SQLCMD with the -I option, so that you run with QUOTED_IDENTIFIER ON.
>
--
Erland Sommarskog,SQLServerMVP, esq...@.sommarskog.se
>
Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

downwitch - we have a new product that will be released in the next
few days, xSQL Builder that allows you to create a database deployment
package which between other things allows you to specify a folder
where you can have all the scripts you want to run before the database
synchronization takes place and also you can specify a folder where
all your post synchronization scripts are. The application will run
all those scripts on one big transaction and log everything.

Email us at our support address @. xsqlsoftware.com and we will send
you the pre-release build so you can give it a try.

Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com|||On Aug 22, 11:24 am, downwitch <downwi...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I've run as many keyword combinations searching for this as I can
think of, but cannot find a simple answer.
>
The situation: I have a database that is mid-conversion/redevelopment.
I frequently rebuild my dev copy from the production version, which
requires a bunch of DDL script, and then adding in all the stored
procedures necessary to the new version only. Each sp may have changed
since the last time it was run, so I can't exactly keep them all
concatenated in a single file or anything like that.
>
So the only way I can see to execute each and every one of these saved
script files is to open it up in SSMS and hit F5 for execute. This is
a silly waste of time, it seems to me, and hardly foolproof. Is there
not some simple way to run every script in a project? What purpose
does a project serve, otherwise?
>
(Worth mentioning that the tool I found herehttp://www.codeproject.com/useritems/SSMSScriptRunner.aspdoes not
seem to work.)
>
Thanks in advance.

Hello downwitch,

xSQL Software has just released a free utility, xSQL Script Executor,
that allows you to run multiple T-SQL scripts at once - you can choose
individual scripts or whole folders and run them all. You can choose
to wrap all scripts in one big transaction or not, you can call it
from the command line etc. Check it out at:
http://www.xsqlsoftware.com/Product...torDetails.aspx
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Run a task multiple times

I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.

Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.

I would like to do this:

Task1 Success--> Set Vars --Success--> Log
|
Task2 Success--> Set Vars --Success-| (do the Log task again)
|
Task3 -Success--> Set Vars --Success-| (do the Log task again)
|
etc

This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.

Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.

Any ideas?I take it these are variables in SetVars are a custom variable set, since the default SSIS logging can handle many logging situations.

This looks like a case for event handlers.

Create three package scoped event handlers

OnPreExecute:
Set a package scoped variable IsFailed to false

OnTaskFailed:

Set a package scoped variable IsFailed to true provided the task is

one of the (Task1,Task2,Task3 or SetVars tasks)

OnPostExecute:
Check the package scope IsFailed variable
and log as needed (only one log implementation and instance needed)

There are scenarios were this would not work, involving parallelism of tasks, but the depiction you have would appear to be task1 followed by task2...|||It seems that you may want to look into SSIS logging capabilities and custom logging component http://www.thejoyofcode.com/SSIS_Custom_Logging_Task_for_Event_Handlers.aspx. There is also Microsoft example for it I reckon (html and smtp log provider samples)- http://msdn2.microsoft.com/en-us/library/ms365179.aspx

In case you have requirement doing it in task - I would recommend creating a custom task - http://msdn2.microsoft.com/en-us/library/ms345156.aspx. In this case of custom "Log" task - the code would not be duplicated - although you would still need to drag and drop it 14 times to designer and link it|||Ok. LOG was a bad wording. I was trying to get is how to run the same task multiple times.

Lets just say for argument the "LOG" task above is a "Send Email Task", it doesn't matter what the task does. I don't want 14 send email tasks which are exactly the same. I want ONE task and have it execute 14 times.

The best thing, would be if there was a "shortcut" mechanisim, which doesn't exist, to allow this. So I could "reference" the orginal task, even if I had to have 13 shortcuts to the original task that would be ok with me.|||

Tom Phillips wrote:

Ok. LOG was a bad wording. I was trying to get is how to run the same task multiple times.

Lets just say for argument the "LOG" task above is a "Send Email Task", it doesn't matter what the task does. I don't want 14 send email tasks which are exactly the same. I want ONE task and have it execute 14 times.

The best thing, would be if there was a "shortcut" mechanisim, which doesn't exist, to allow this. So I could "reference" the orginal task, even if I had to have 13 shortcuts to the original task that would be ok with me.

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

|||

Jamie Thomson wrote:

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

Jamie,

"The smallest unit of reusability in SSIS (currently) is a package" is the key.

Under any circimstance, I have to duplicate something, either the execute package or send email or in the above event handler example 3 TIMES 14 tasks, over and over and over.

I would like to suggest to MS they add a "shortcut task" which "references" another task. So only one ocurance of the actual task exists. Even though I would have to duplicate the "shortcut" over and over, if I make a change to the original task it would propagate to all the "shortcuts". There are some techincal details to work out on this, for example, what about expressions in the original, do they run or not, can you override them in the "shortcut"? But I think it would be doable.

In my case I have 14 tasks which are identical, and I had to change one thing in every single one of them. What happens when there are 30, or 100 of them? The shortcut would have eliminated that problem.

Thank you|||

Tom Phillips wrote:

Jamie Thomson wrote:

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

Jamie,

"The smallest unit of reusability in SSIS (currently) is a package" is the key.

Under any circimstance, I have to duplicate something, either the execute package or send email or in the above event handler example 3 TIMES 14 tasks, over and over and over.

I would like to suggest to MS they add a "shortcut task" which "references" another task. So only one ocurance of the actual task exists. Even though I would have to duplicate the "shortcut" over and over, if I make a change to the original task it would propagate to all the "shortcuts". There are some techincal details to work out on this, for example, what about expressions in the original, do they run or not, can you override them in the "shortcut"? But I think it would be doable.

In my case I have 14 tasks which are identical, and I had to change one thing in every single one of them. What happens when there are 30, or 100 of them? The shortcut would have eliminated that problem.

Thank you

Tom,

What you're talking about is reusability of tasks. This is my number one request (in a list of literally hundreds) for the next version of SSIS. Unfortunately I don't think we'll see it in SQL Server 2007/8 which is intended to be a small release after the masses in SQL Server 2005, but hopefully in the one after that.

Until then, lump all your reusable stuff into a dedicated package.

-Jamie

|||I would hope to see something before 2022.

I "think" the shortcut method would be realtively easy to do. I wonder if I could create a custom task which allowed referencing another task, basically copy the functionallity at runtime? I will have to look into it.

Thanks