Executing Package Question

Hi,

Simplification of setup:
- SQL Server Box with Enterprise Edition of SQL Server 2005
- Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP

1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio?
2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct?
3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)?
4. Does Service Pack 1 address these issues?

Thanks a lot for any help you could provide!

John

1 - Yes

2 - Not sure, maybe, but I would just install it all, some stuff will not work unless it is all there correctly.

3 - On your workstation. SSIS is a client-side hosted process, so it runs under the context of the excution host, which means the same machine and security context as teh user running dtexec, BIDS, or when scheduled it use the same rules as for the SQL Server Agent job context, normally the service account, or maybe the proxy account if set.

4 - What issues?

|||

Hi there,

As far as my experience goes the following :

1. you need to have the developer edition op SQL SERVER 2005 to be able to run your packages from the command line With the regular enterprise edition you can only run them under devenv

3. You can only install SQL SERVER 2005 Integration services on a server not on a workstation

Greetings,

Paul

|||Thanks for the info!

The issues I mean are only being able to run an SSIS package via the command line from a server with SSIS installed. With DTS2000, I could use a command on any box that would specify the SQL Server the DTS package resided on. Is this lost functionality with 2005?|||

John, DTS could be installed as a client redistributable, and required a CAL only. SSIS is a licensed as part of the server and has a slightly more involved install, it is not redistributable. You can install SSIS only, you don't need tools or SQL Server etc, but you need to license the machine as a SQL Server.

Paul, sorry but wrong on both counts. Executing packages from the command line is available in all SQL Server supporting SSIS editions, Dev, Std and Ent editions. (The three editions that include SSIS). You can run SSIS on a workstation, the Developer edition is just for that scenario, and *all* components can be installed. People often install SQL Server workstation components only, and forget the SSIS service which causes issues. Just search this forum!

|||Darren,

Thank you for the information. As I understand it, I can't execute an SSIS package via the command line utility unless I'm doing so on a Server OS (i.e. not XP) and have SSIS running.

That being said, is there a work around method that would allow me to run an SSIS package from the command line utility without having a Server OS (i.e. XP) and without SSIS running? You mentioned something about a "client redistributable" and a "CAL", but I'm not sure what you mean. Is what I want to do possible? I'm basically trying to preserve the functionality and processes we had in place before we upgraded from 2000 to 2005.

Thanks again!

John|||Does anyone have a work around for this?

Being able to call an SSIS package via the command line from a workstation that is executed on the server. (This is what I do with DTS2000.)

Thanks!

John|||

For the DTS 2000, unless you're doing a remote execution or starting a job, you're actually loading that DTS package from the server to your workstation and then running it on your workstation. DTExec I believe works the same way on SQL 2005, i.e. if run on your desktop, that's where the package will execute.

|||I believe I found a suitable solution to my scenario. I can use the sqlcmd.exe at the command line to execute a stored procedure on the SQL Server running SSIS with the packages. I just need to set up a SQL Agent with the SSIS package as a step without a schedule. The built-in stored procedure for executing a SQL Agent should take care of it. Thoughts? Thanks!

John|||

Larry is correct. DTS is no different from SSIS in the execution location and behaviour of command line tools, the only difference for you to be aware of is the is the licensing. You still needed to install DTS on the workstation to get DTSRUN, as you need to install SSIS to get DTEXEC.

To execute packages on a server, the job method works well. It is asynch, and gives no progress, but it absracts you away from the workstation and also the user security context of that which I see as a good thing.

|||

If you're trying to execute an SSIS package via the job scheduler but don't want to use the built in job type for some reason, you could just set up a OS Command and use DTExec to execute it and you are correct that the job would need no schedule.

You can start the job with sp_start_job assuming the user has permissions to that and the job or set up a stored procedure with an EXECUTE AS to elevate priviledges. Just be careful what accounts you give the ability to execute that stored procedure.

|||Great - thanks for the confirmation!

I have a follow-up question though. With DTS2000, I was able to execute a command and it would return witha success/failure code when the package would end. My programs would wait for that message until continuing with other tasks. However, now with SSIS I get a message returned right away from the -Q "exec ..." statement, not when the SQL Agent and in turn SSIS package has succeeded/failed. Is there anyway to get this information returned? Thanks again for all the help!|||Anyone have a solution to this? Thanks!

Executing Package Question

Hi,

Simplification of setup:
- SQL Server Box with Enterprise Edition of SQL Server 2005
- Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP

1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio?
2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct?
3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)?
4. Does Service Pack 1 address these issues?

Thanks a lot for any help you could provide!

John

1 - Yes

2 - Not sure, maybe, but I would just install it all, some stuff will not work unless it is all there correctly.

3 - On your workstation. SSIS is a client-side hosted process, so it runs under the context of the excution host, which means the same machine and security context as teh user running dtexec, BIDS, or when scheduled it use the same rules as for the SQL Server Agent job context, normally the service account, or maybe the proxy account if set.

4 - What issues?

|||

Hi there,

As far as my experience goes the following :

1. you need to have the developer edition op SQL SERVER 2005 to be able to run your packages from the command line With the regular enterprise edition you can only run them under devenv

3. You can only install SQL SERVER 2005 Integration services on a server not on a workstation

Greetings,

Paul

|||Thanks for the info!

The issues I mean are only being able to run an SSIS package via the command line from a server with SSIS installed. With DTS2000, I could use a command on any box that would specify the SQL Server the DTS package resided on. Is this lost functionality with 2005?|||

John, DTS could be installed as a client redistributable, and required a CAL only. SSIS is a licensed as part of the server and has a slightly more involved install, it is not redistributable. You can install SSIS only, you don't need tools or SQL Server etc, but you need to license the machine as a SQL Server.

Paul, sorry but wrong on both counts. Executing packages from the command line is available in all SQL Server supporting SSIS editions, Dev, Std and Ent editions. (The three editions that include SSIS). You can run SSIS on a workstation, the Developer edition is just for that scenario, and *all* components can be installed. People often install SQL Server workstation components only, and forget the SSIS service which causes issues. Just search this forum!

|||Darren,

Thank you for the information. As I understand it, I can't execute an SSIS package via the command line utility unless I'm doing so on a Server OS (i.e. not XP) and have SSIS running.

That being said, is there a work around method that would allow me to run an SSIS package from the command line utility without having a Server OS (i.e. XP) and without SSIS running? You mentioned something about a "client redistributable" and a "CAL", but I'm not sure what you mean. Is what I want to do possible? I'm basically trying to preserve the functionality and processes we had in place before we upgraded from 2000 to 2005.

Thanks again!

John|||Does anyone have a work around for this?

Being able to call an SSIS package via the command line from a workstation that is executed on the server. (This is what I do with DTS2000.)

Thanks!

John|||

For the DTS 2000, unless you're doing a remote execution or starting a job, you're actually loading that DTS package from the server to your workstation and then running it on your workstation. DTExec I believe works the same way on SQL 2005, i.e. if run on your desktop, that's where the package will execute.

|||I believe I found a suitable solution to my scenario. I can use the sqlcmd.exe at the command line to execute a stored procedure on the SQL Server running SSIS with the packages. I just need to set up a SQL Agent with the SSIS package as a step without a schedule. The built-in stored procedure for executing a SQL Agent should take care of it. Thoughts? Thanks!

John|||

Larry is correct. DTS is no different from SSIS in the execution location and behaviour of command line tools, the only difference for you to be aware of is the is the licensing. You still needed to install DTS on the workstation to get DTSRUN, as you need to install SSIS to get DTEXEC.

To execute packages on a server, the job method works well. It is asynch, and gives no progress, but it absracts you away from the workstation and also the user security context of that which I see as a good thing.

|||

If you're trying to execute an SSIS package via the job scheduler but don't want to use the built in job type for some reason, you could just set up a OS Command and use DTExec to execute it and you are correct that the job would need no schedule.

You can start the job with sp_start_job assuming the user has permissions to that and the job or set up a stored procedure with an EXECUTE AS to elevate priviledges. Just be careful what accounts you give the ability to execute that stored procedure.

|||Great - thanks for the confirmation!

I have a follow-up question though. With DTS2000, I was able to execute a command and it would return witha success/failure code when the package would end. My programs would wait for that message until continuing with other tasks. However, now with SSIS I get a message returned right away from the -Q "exec ..." statement, not when the SQL Agent and in turn SSIS package has succeeded/failed. Is there anyway to get this information returned? Thanks again for all the help!|||Anyone have a solution to this? Thanks!

executing OPENQUERY

Hi,

I'm trying to execute an openquery statement from SQL Server 2005 against a linked server AS2005 (both on same machine) from Management Studio.

If I use a one connection with full rights I have no problem. But if I use the specific connection for that application I got this error:

OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2" returned message "The following system error occurred: A specified logon session does not exist. It may already have been terminated. .".

Msg 7303, Level 16, State 1, Line 5

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2".

"Ad hoc data-mining query" is checked.

MSOLAP Provider has "Allow inprocess" checked.

Code Snippet

SELECT * FROM OPENQUERY(ASLOCAL2,'SELECT non empty {[Measures].[Prices Avg] ,[Measures].[Prices Max] ,[Measures].[Prices Min] } ON COLUMNS,non empty [Tbl DW Dim Type].[Name].&[1] ON ROWS FROM [DW DEV] ')

Any ideas ?

Thnx.

Have you tried running a profiler trace against SSAS while trying to run the OPENQUERY() ?

I am wondering if it is a permissions issue for the account that the application is using for the connection.

|||The only difference between these tow accounts is that one account is Windows account and the other one is Sql server account.|||

Well that is probably your issue. SSAS only supports windows authentication. You would need to have the linked server setup to authenticate using the current login's security context, which works fine for windows accounts, but if you use a sql account, SQL Server will fall back to authenticating against the SSAS server using the account that the SQL Server is running under, which probably does not have permissions to query the cubes.

So your choices are to either only use windows accounts against the linked server or to make sure that the account that SQL Server is running under has access to the appropriate objects in SSAS.

|||

You're right.

I changed the way that the (web)application is connecting to the Sql server (until now I used sql account) and now I am using an windows account.

Executing Northwind Script to Create the Database on SLQEXPRESS Edition

I have Visual Studio 2005 Beta 2.00 install which have installed the SQLEXPRESS server.

I have the Script for the Northwind database which I need to run some demos but I can figure out how to execute this script.

Can someone tell me what todo or how to attatched the database to this server. I also have a copy of the database already created. But When I tried login in into the database I get an error login fail.

Which is the default user amd password for the northwind database?

Tia

Charles

I found a way to do it. I installed SQL manager and the tool has a option to attatch a database.

Thanks

Charles

ExecuteRow - Missing Definition

Hello,

I'm using Visual Studio 2005 with ADO.Net 2.0 but I am missing a definition for the sqlcommands' object "ExecuteRow"

oCommand =new System.Data.SqlClient.SqlCommand();

oCommand.ExecuteRow() ---------> this is the missing definition

Any ideas anyone ?

The SqlCommand class has no ExecuteRow method. Seethis thread.

What are you trying to do?

Don

|||

well I'm actually just running some tests (I'm currently migrating from 1.1 to 2.0)

I have a book by WROX which specifies the definitions for the command object (See attached image), it contains

a definition for ExecuteRow

|||

ItayZ:

I have a book by WROX which specifies the definitions for the command object (See attached image), it contains

a definition for ExecuteRow

Looks like you need a better book. ExecuteRow was dropped between Beta 1 and Beta 2.

|||

Geez, I hate day and date books. They inevitably have stuff wrong, since they have to go to press months before the final release. And I'm afraid that I've written a few. But Never Again.

A better book is indeed in order, but in the meantime you might check the Wrox Web site for an errata sheet.

Don

Execute Stored Procedure menu not appearing

Hi,

I have created an SQL Server instance in SQL Server Management Studio. I have a few databases, and stored procedures in them. When I right click on the stored procedure, I have the menu for "New Stored Procedure, Modify, Script Procedure as, and so on". But, I could not see the "Execute Stored Procedure" item.

Could any one help to find out what could be the problem and solve it?

Thanks,
Luke.Stored procedures in 2000 must be executed from a utility such as Query Analyzer.
I think the 2005 management interface allows you to execute them directly and submit parameters, but I'd have to check to be sure.
Regardless, it is not good to be doing these types of activities in the GUI. Use QA instead.

Execute SQL greyed out in Query Designer?

If I right click in a query pane and select Query Designer in SQL management Studio, or select an existing query and do the same I get the query designer and can monkey about with the SQL add tables etc...

But I can't Execute the SQL or see the results. These options are greyed out. This is a pain as I keep having to select the SQL and paste it back into the query window, easy enough but when it takes a coulpe of minutes to untangle the tables as they are laid out in the visual editor this is just a very painful way of debugging the bad joins and stuff my web team keep putting in.

Is there any way of getting a visual query designer I can actively work in?

many thanks

Steve

I've had exactly the same problem and after a bit of research came across this:

http://www.developermania.com/newsgroups/item/130965/Query_Designer.aspx

Its basically a reply to a posting last year from Keith Wilson at microsoft that seems to indicate that this is functionality they didn't implement in the original release and that was beyond the service pack. I get the impression therefore that the menu selections are simply acting as placeholders for functionality that has not been built yet?!!

There's a few posts out there concerning this issue but I've not come across a solution. Let me know if you have better luck!

Regards

Rick Edwards

|||

The functionality is there to execute the query, but not available in the mode the designer is running in when you design a query in the T-SQL script editor. The general idea was that you would run the query in the editor itself, not in the dialog hosting the designer.

The "open table"/"open view" functionality is really the query designer as well, but its running in a more capable mode. When you right click on a table or view in object explorer and select the "Open..." menu item, the query designer will start with "select * from {my table}" as the SQL text, but with only the results pane being shown. You can use this as a trick to get to a more functional version of the query designer. Create an empty table (so there are no results to fetch), then open it. Once you have the table open, you can show the SQL, Diagram, or Query By Example panes by clicking the appropriate button in the toolbar, delete the existing text and do what you need to do.

I know this is a pretty baroque way to get to the query designer. If you find yourself using this functionality often, you might consider going to the http://connect.microsoft.com/sqlserver site and suggesting that we provide a more direct way to get to the more fully-functional version of the query designer. We use customer feedback like this when we are considering which features to implement in future versions.

Hope this helps,
Steve

|||

OK its a bit of an obscure way of getting there but I find that pretty acceptable. The greyed out option confused the hell out of me though.

Many thanks for taking the time to answer this one. The trick of getting at the full query designer is going to save me a huge amount of time.

Execute SQL greyed out in Query Designer?

If I right click in a query pane and select Query Designer in SQL management Studio, or select an existing query and do the same I get the query designer and can monkey about with the SQL add tables etc...

But I can't Execute the SQL or see the results. These options are greyed out. This is a pain as I keep having to select the SQL and paste it back into the query window, easy enough but when it takes a coulpe of minutes to untangle the tables as they are laid out in the visual editor this is just a very painful way of debugging the bad joins and stuff my web team keep putting in.

Is there any way of getting a visual query designer I can actively work in?

many thanks

Steve

I've had exactly the same problem and after a bit of research came across this:

http://www.developermania.com/newsgroups/item/130965/Query_Designer.aspx

Its basically a reply to a posting last year from Keith Wilson at microsoft that seems to indicate that this is functionality they didn't implement in the original release and that was beyond the service pack. I get the impression therefore that the menu selections are simply acting as placeholders for functionality that has not been built yet?!!

There's a few posts out there concerning this issue but I've not come across a solution. Let me know if you have better luck!

Regards

Rick Edwards

|||

The functionality is there to execute the query, but not available in the mode the designer is running in when you design a query in the T-SQL script editor. The general idea was that you would run the query in the editor itself, not in the dialog hosting the designer.

The "open table"/"open view" functionality is really the query designer as well, but its running in a more capable mode. When you right click on a table or view in object explorer and select the "Open..." menu item, the query designer will start with "select * from {my table}" as the SQL text, but with only the results pane being shown. You can use this as a trick to get to a more functional version of the query designer. Create an empty table (so there are no results to fetch), then open it. Once you have the table open, you can show the SQL, Diagram, or Query By Example panes by clicking the appropriate button in the toolbar, delete the existing text and do what you need to do.

I know this is a pretty baroque way to get to the query designer. If you find yourself using this functionality often, you might consider going to the http://connect.microsoft.com/sqlserver site and suggesting that we provide a more direct way to get to the more fully-functional version of the query designer. We use customer feedback like this when we are considering which features to implement in future versions.

Hope this helps,
Steve

|||

OK its a bit of an obscure way of getting there but I find that pretty acceptable. The greyed out option confused the hell out of me though.

Many thanks for taking the time to answer this one. The trick of getting at the full query designer is going to save me a huge amount of time.

Execute Mutiple Tasks In Debug Mode

Maybe I'm missing something, but I can't find how to run multiple tasks in sequence while in Visual Studio debug mode. In DTS design mode I grew accustomed to right-clicking tasks one-at-a time, but in SSIS I find the additional step of having to exit Debugging mode after every task gets old after a while.

There must be a way to start execution at a certain task and have the package continue all the way to some other specified task. It would also be nice to have every task in a Group execute in sequence and stop (even if connections continue beyond the group). I could even settle for repeatedly clicking the Continue button in Debug mode, but it's always grayed out when the current task is finished!

Can this be achieved by setting breakpoints?

I have previously requested "Execute from here" functionality in the control flow. You can vote for this request here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a2548612-b602-42ab-9ff9-563d927674f8

Adding a comment would help as well.

In the meantime, you can place all required tasks into a sequence container, right click on the sequence container and select "Execute task". All tasks in the container will be executed.

-Jamie

|||

Sequence container appears to be what I was looking for. Thanks. It seems obvious now that you've pointed it out, but features are easy to miss with so much new functionality.

I added some comments on your request.

execute dts 2000 failure

I have used to the execute dts 2000 task in Visual Studio to successfully execute a structured storage file for a simple DTS package.

I am trying to use the same method for another DTS package, which executes five other packages. I've tried both the 'structured storage' and 'embedded in task' methods pointing to the parent package but in debugging mode the execution immedately fails. When I execute the parent package in Management Studio, it successfully executes the five child packages. I am including the debugging message below, thogh it doesn't help me any. Does anyone know what the problem is?

SSIS package "BPSA_LOAD_ACCT_SUMMARY_NEW.dtSX" starting.
Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task
Warning: 0x80019002 at BPSA_LOAD_ACCT_SUMMARY_NEW: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "BPSA_LOAD_ACCT_SUMMARY_NEW.dtSX" finished: Failure.

My misunderstanding.

I realize now that I have to execute the 5 DTS 2000 package tasks in the one 2005 project.

execute dts 2000 failuer

I have used to the execute dts 2000 task in Visual Studio to successfully execute a structured storage file for a simple DTS package.

I am trying to use the same method for another DTS package, which executes five other packages. I've tried both the 'structured storage' and 'embedded in task' methods pointing to the parent package but in debugging mode the execution immedately fails. When I execute the parent package in Management Studio, it successfully executes the five child packages. I am including the debugging message below, thogh it doesn't help me any. Does anyone know what the problem is?

SSIS package "BPSA_LOAD_ACCT_SUMMARY_NEW.dtSX" starting.
Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task
Warning: 0x80019002 at BPSA_LOAD_ACCT_SUMMARY_NEW: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "BPSA_LOAD_ACCT_SUMMARY_NEW.dtSX" finished: Failure.

My misunderstanding.

I realize now that I have to execute the 5 DTS 2000 package tasks in the one 2005 project.

|||I have small info about this problem. The real message in the DTS(2000) package log (but no in SSIS "Execute DTS 2000 Package Task" on SQL 2005 Server!) is:
"Step Error Source:
Step Error Description:(1:"My DTS 2000 Task Name") SubStep 'DTSStep_DTSExecutePackageTask_1' failed with the following error:
Exception of type 'System.OutOfMemoryException' was thrown.
Execution was canceled by user.Exception of type 'System.OutOfMemoryException' was thrown.
Step Error code: 80040427
Step Error Help File:
Step Error Help Context ID:0"
And this problem interrelated, as i any more know, with parallel execution of nested packages on DTS ("Execute on main package tread" option).
If you are yet interesting this problem - answer me there or email on sas72@.rambler.ru

Execute check sql syntax programatically

HI, Is there a way to check sql syntax from a .Net application? Sql server management studio is made in .Net and it has that option. Is teher a way to know how it is implemented in SSMS?

Thank you,
Ccote

Hi Ccote,

SSMS performs some farly rudimentary syntax checks, but not a complete TSQL syntax validation. This is done via TSQL command

SET PARSEONLY ON

This functionality is also exposed in SMO via ParseOnly option:

Code Snippet

Server.ConnectionContext.ExecuteNonQuery(myquery, ExecutionTypes.NoCommands);

|||HI, Thank you for your answer!|||Not being very familiar with VB.Net, how would I caputure the output of the ParseOnly result into a variable?