Executing SSIS Package - SQL 2005 Express

Hi,

I have created SSIS Package using DTS vizard in SQL 2005 Express. Help me out to execute the Package.

Thanks

Even I am also trying for the Same. Update me if you get a solution.sql

Executing SQL Package

Hi

I have created a DTS package in SQL 2005 which will pull the data from oracle and pushes into SQLServer. I am able to execute the package from business intelligence wizard.

Is there anyway to trigger this package manually apart from the wizard?.

looking for responses.

Thanks
GaneshIt can also be executed from query analyzer.

You call it from any forntend tool also.|||Hi

Thanks for your response.

When I create package from Business intelligence wizard, It was created as Package1.dtsx. Now , How can I invoke package1.dtsx from query analyzer?.

As far as I know, In oracle, a package will contain main and a body procedures. We can invoke the main procedure from any command prompt. But I am new to SQL packages. Can you suggest me in this regard.

Thanks,
Ganesh

executing package DTExec

I created a package and stored in SQL server.

I am running it as follows

master.dbo.xp_cmdshell dtexec /sq "ABC DEF GHI" /ser "Prod"

ABC DEF GHI --> is my package name.

When i run this from a job, it runs without error

but when i run the above sql i am getting error as

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '/'.

Am i missing some thing, please advice. Is that the problem between spaces in the package name?

I missed the single quotes

master.dbo.xp_cmdshell 'dtexec /sq "ABC DEF GHI" /ser "Prod"'

Executing Oracle Stored Procedure via SQL Server 2000 Linked Serve

All,
I am trying to execute a simple Oracle stored procedure from SQL Server
2000. The SP has no input or output parameters. I have created the linked
server correctly as I am able to execute Oracle functions just fine. I have
tried the following two methods to execute the SP, SKELETON:
{Call ORADB..MARCO.SKELETON()}
Execute ORADB..MARCO.SKELETON
Both methods result in the same error listed below:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SKELETON' on remote server 'ORADB'.
[OLE/DB provider returned message: One or more errors occurred during
processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Any ideas on how I can get this working properly?
Thanks,
MarcoHere is an old post by Umachandar. See if it helps:
http://tinyurl.com/7dxrr
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
> All,
> I am trying to execute a simple Oracle stored procedure from SQL Server
> 2000. The SP has no input or output parameters. I have created the
> linked
> server correctly as I am able to execute Oracle functions just fine. I
> have
> tried the following two methods to execute the SP, SKELETON:
> {Call ORADB..MARCO.SKELETON()}
> Execute ORADB..MARCO.SKELETON
> Both methods result in the same error listed below:
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'SKELETON' on remote server 'ORADB'.
> [OLE/DB provider returned message: One or more errors occurred during
> processing of command.]
> [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
>
> Any ideas on how I can get this working properly?
> Thanks,
> Marco
>

Executing Oracle Stored Procedure via SQL Server 2000 Linked S

Thanks for the link. I have a stored procedure in Oracle that was created a
long time ago by someone else. Without manipulating the stored proc at all,
I wanted to feed it the required input parameters via SQL Server and let it
do its magic. From what I gather, this is not possible? I must create the
proc within a package in Oracle?
"oj" wrote:

> Here is an old post by Umachandar. See if it helps:
> http://tinyurl.com/7dxrr
> --
> -oj
>
> "marco" <marco@.discussions.microsoft.com> wrote in message
> news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
>
>Yes. Creating a package wrapper is your ticket to get to oracle proc.
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:4653000F-F545-4044-9876-E113CD14AFE6@.microsoft.com...
> Thanks for the link. I have a stored procedure in Oracle that was created
> a
> long time ago by someone else. Without manipulating the stored proc at
> all,
> I wanted to feed it the required input parameters via SQL Server and let
> it
> do its magic. From what I gather, this is not possible? I must create
> the
> proc within a package in Oracle?
> "oj" wrote:
>

Executing DTS from Stored Procedure in Sql Server 2000

Hai .....,
I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error message:(I Executed the procedure thru Query Analyzer window and got the following message)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: A connection with the server could not be established
Error source: msxml3.dll
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
Can anyone help me solve this problem please

I am with the same occurrence, but in a server windows 2003 server. Does anybody can me to help?

Guilherme

guilhermer@.correios.com.br

|||

Can you post the code you are running?

This works:

xp_cmdshell'dtsrun /SmyServer /NmyPackage /E'

|||DTSRun /S "smg0062" /N "DBTNT - BAIXA SRO" /W "0" /Esql

Executing DTS from Stored Procedure in Sql Server 2000

Hai .....,
I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error message:(I Executed the procedure thru Query Analyzer window and got the following message)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: A connection with the server could not be established
Error source: msxml3.dll
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
Can anyone help me solve this problem please

I am with the same occurrence, but in a server windows 2003 server. Does anybody can me to help?

Guilherme

guilhermer@.correios.com.br

|||

Can you post the code you are running?

This works:

xp_cmdshell 'dtsrun /SmyServer /NmyPackage /E'

|||DTSRun /S "smg0062" /N "DBTNT - BAIXA SRO" /W "0" /E

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel B1) Locate the job (under SQL Agent)
2) Right click on the job
3) Pick View Job History...
4) Check the Show Job Details box
5) Read the error message(s)

-PatP|||This is the error message i get.

The job failed. The Job was invoked by User REPEATSEAT\dberezniak. The last step to run was step 1 (DTS Execute App).

This doesn't tell me much. It blows up when trying to execute the app.
( The application is test and does work on its own and within the package. )|||Did you click on the "Show Job Details" box to be sure that it is checked? That message sounds like what you'd get if that box wasn't checked.

-PatPsql

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel BThis is really a question for the Microsoft SQL Server (http://www.dbforums.com/f7) forum. I'd be happy to answer the question there!

-PatP

Executing an SSIS package containing a Data Mining Query task from a SQL job

Hi, I'm new to this forum, so please bare with me.

I've created a mining model, i've tweaked it etc and i'm now happy with the results its producing. I'm now looking to automate the processing and exporting of the results of the model i've done this simply by creating an SSIS package with two tasks, one task being to process the model the other task is a Data Mining Query task.

This package works fine in visual studio and when i deploy it to the server.

The problem i'm having is when i then try to execute the package from a job, after a bit of investigating i have tracked it down to the Encryption of "sensitive" properties. By default the encryption is based on UserKey which is why the package works for me when i execute it from VS or even the server, but when the job trys to execute the package running under the sql agent account it fails.

Looking at the security options i have for packages, i can either DontSaveSensitive, EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword plus a few others.

DontSaveSenstive is clearly not an option as this just creates an unusable package.

EncrptSensitiveWithUserKey doesn't seem to be an option as the job runs under the SQL Agent account (also i'm thinking that the UserKey that the encryption is based on also incorporates other factors related to my profile that i can't impersonate? i might be wrong though)

EncryptSensitveWithPassword seems to be an option except that i can't get this to work either, there doesn't seem to be anyware in the job step to give it the password information.

Its frustrating me now because i've fallen at the very last hurdle, if anyone else has experienced this problem and knows how to resolve it that would great.

Thanks

Bob.

There is a comprehensive KB article that may cover your question:

http://support.microsoft.com/kb/918760

|||

Thanks, that has helped.

for reference i employed the DontSaveSensitive level of security and stored the Query String for the Data Mining Query task in an XML configuration file.

this is the only option on the KB article that worked for me.

Thanks.

Executing a View timesout through Open View

Hello All,

I have created a view that will potentially return a huge number of records around 500,000 rows. When i execute this view by clicking Open View from the SQL Management studio i get the following error

SQL Execution error

Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

However the query executes when paste the generated query of the view in the query designer window. It returns the 500,000 rows. I am wondering if there is some option that i will have to configure to return a large size result set in view designer. I also want to know if this is an inherent problem with view designer.

Any help regarding this will be really appreciated.

Satya

Open View creates a fully editable grid of the rows returned by the view so it is not surprising that it is choking on that much data.

First - this is not the view designer, the view designer opens when you select Modify, and that will let you edit the view definition.

If you do want to work with the data in the view through Open View, then after you select Open View, click the stop button down at the bottom of the window, next to the message that says Retrieving Data... It will stop and show whatever data it retrieved before you clicked the button. Now click the Show SQL Pane button on the toolbar (or Query Designer/Pane menu). In the SQL pane, edit the query by adding a WHERE clause to the SELECT statement that is selecting the data from you view. Now click the Execute SQL button (the exclamation mark) and it will run and return only the rows that match. Now you can selectively pull only a portion of the 500,000 rows at a time and work on them, then change the WHERE clause to select other rows. I can't believe you really want all 500,000 rows on screen at the time - and SQL Server doesn't think it's a good idea either :-)

|||

Hi,

Thanks for your reply. I didnt intend to see all the 500,000 rows of data at a time but did want to know the maximum volume that i can see when i click Open View. The stop button is also disabled when i click Open view and i get this error directly and so i cannot see a partial set of the data the way you had specified. Also i wrote <select a top 1 * > query enveloping the query generated by the view so that i could get atleast the first tuple of the query result but even this was aborted by the same error. The error comes immediately after a minute and no option is enabled for me to do anything once i click Open View or execute.

I was trying to see if this was a configuration issue in VIEW or if i was missing something else. The same query does give the intended results when i run in the query designer. Is there a capacity constraint for the Results grid pane in the View Designer as compared to normal query window.

Thanks again for your help and would appreciate if you can clarify the above too

satya

|||Do you by any chance have a TOP and an ORDER BY in the view definition, or is the view just a standard SELECT?|||It just seems that the CommandTimeout property is not updated in Management Studio when using View, and that it is always stuck at 30 sec. I have the same problem, but it seems impossible to fix.|||I have a view that returns over 500,000 rows, it takes about 5 minutes and never times out. I can click the Stop button at any time. I haven't changed anything from default so I'm not sure what's different.sql

executing a stored procedure in the background

I am new to SQL and to vbscript, none the less I have a need to handle
some data that is stored in a MS SQL2000 data base.
I created a stored procedure that will select data from the SQL and
store it in a text file in a folder where is will be accessed and
processed by another process. I did this using the tools in Enterprise
Manager.
I found an example vbscript to use to execute the stored procedure. I
modified it a bit and, when it is run, the stored procedure is executed
and, utlimately, the text file is placed in the folder.
I have a few details to work out before this project is fully
functional. The major detail at this juncture is getting the stored
procedure to run without presenting dialog boxes or the process status
screen. Which brings me to the point of this topic.
I need some guidelines or pointers to the way a stored procedure is
designed so that when it runs, the process stays in the background and
dialog boxes or process screens do not appear.
Any help is appreciated.
PatrickThis isn't a feature of a stored procedure. It's how you wrote your
application. If you call a stored procedure, passing all the parameters
needed to it, you are going to get a return set of data, which the
application will handle (or not handle) based on how you wrote the app.
There is no dialog box or process screen, unless your app is producing them.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"raglin" wrote:

> I am new to SQL and to vbscript, none the less I have a need to handle
> some data that is stored in a MS SQL2000 data base.
> I created a stored procedure that will select data from the SQL and
> store it in a text file in a folder where is will be accessed and
> processed by another process. I did this using the tools in Enterprise
> Manager.
> I found an example vbscript to use to execute the stored procedure. I
> modified it a bit and, when it is run, the stored procedure is executed
> and, utlimately, the text file is placed in the folder.
> I have a few details to work out before this project is fully
> functional. The major detail at this juncture is getting the stored
> procedure to run without presenting dialog boxes or the process status
> screen. Which brings me to the point of this topic.
> I need some guidelines or pointers to the way a stored procedure is
> designed so that when it runs, the process stays in the background and
> dialog boxes or process screens do not appear.
> Any help is appreciated.
> Patrick
>|||raglin (pzelenka@.gmail.com) writes:
> I am new to SQL and to vbscript, none the less I have a need to handle
> some data that is stored in a MS SQL2000 data base.
> I created a stored procedure that will select data from the SQL and
> store it in a text file in a folder where is will be accessed and
> processed by another process. I did this using the tools in Enterprise
> Manager.
> I found an example vbscript to use to execute the stored procedure. I
> modified it a bit and, when it is run, the stored procedure is executed
> and, utlimately, the text file is placed in the folder.
> I have a few details to work out before this project is fully
> functional. The major detail at this juncture is getting the stored
> procedure to run without presenting dialog boxes or the process status
> screen. Which brings me to the point of this topic.
> I need some guidelines or pointers to the way a stored procedure is
> designed so that when it runs, the process stays in the background and
> dialog boxes or process screens do not appear.
It sounds as if the simplest in your case, you would set up a job that
is run from SQL Server Agent. You do this under Management->Jobs in
Enterprise Manager. A job consists of one or more steps and has a schedule.
A step can be as simple as a T-SQL command. In your case it would be
run the VB script. I assume, then, that VB script is the part that receives
the data and creates the file.
If the procedure runs BCP from xp_cmdshell to create the file, then
there is no need for the VBscript thing - you can run the procedure
directly from the job step.
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

Executing a Stored Proc in SMO

Could anyone show me how to execute a stored procedure using SMO. I can easily see how to created one and drop one but I cannot see how to execute one.

Thanks very much.

Smo does not have any method to Execute a StoredProcedure. You can however use the Database.ExecuteNonQuery method to run the TSQL to execute the stored procedure.

Thanks,

Kuntal

Executing A No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Another aspect of this matter would be to put your SQL scripts into stored procedures. You can have one stored procedure invoke multiple other stored procedures, similar to the BATCH action you have described. This plan works for any version of SQL Server, as far as I know.

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker

Executing A No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Another aspect of this matter would be to put your SQL scripts into stored procedures. You can have one stored procedure invoke multiple other stored procedures, similar to the BATCH action you have described. This plan works for any version of SQL Server, as far as I know.

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker

Executing a Job in a C# SQL Server Assembly

I have a C# assembly I've created that I wish to run inside SQL Server. Since SQL Server doesn't support SMO inside of it, does anyone have any suggestions on how I might execute a SQL Server job in my assembly?

Thanks - Amos.

Using SMO in CLR:

http://sqlblogcasts.com/blogs/simons/archive/2007/03/14/Using-SMO-from-within-SQLCLR.aspx

Alternatively you can start the job using the sp_start_job procedure.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

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 SSIS Package using SQl Server Agent

Hi

I hv created a new Job for my SSIS Package... but when i start the job manually it gives me this error below:

"Executed as User:localhost/SYSTEM. THe package could not be loaded.the Step Failed".

i have my package deployed in Storage Packages[MSDB]...

Could you help me on this....

THanks!

Karthik

it's all to do with security:

Your localhost/SYSTEM account probably has no access to the SISS storage [MSDB].

Either you have modify the sql agent job to run as a user with sufficient rights on the SISS store or you grant this account access.

Then there is also the possibility that this user account has not access to the databases that are opened by the package.

Execute SSIS package from asp.Net

Hi,

I am a newbie to SSIS. I am trying to execute a simple package that I created from my ASP.Net application.

I get the error 'DTSER_FAILURE'. Can anyone help?

Thanks

Turn on logging, or supply an implementation of IDtsEvents interface to Execute() method to find out some information about the error.

By the way, the most common problem: security, the package is executed under ASP.NET service account, not your domain account.|||

Thanks for the reply.

I turned on the logging. However, no entires are made in the log file.

I thought that there would be some security issues,

> the package is executed under ASP.NET service account, not your domain account.

How do I address this?

|||

HoustonRocket wrote:

> the package is executed under ASP.NET service account, not your domain account.

How do I address this?

Depends on what you mean by "address". This might be quite OK in some situations, but might be not in others. Just something to be aware of.

If you want the package to be executed in different context, use other ways to execute it, rather than invoke it from object model. A common way is to create SQL Agent job and then execute it using Agent's stored procedures. Another way is to execute package using DtExec under different account (see ProcessStartInfo.UserName and ProcessStartInfo.Password).

|||

Hi,

I tried to create a job and add the package as a 'step'. That didnt work either and I got the error

'Microsoft.SqlServer.ConnectionInfo

The specified '@.subsystem' is invalid

Here's what I am trying to do:

I created a package which grabs the data from excel file and populates sql server 2005 DB - simple. This package executes if I run it from the business intelligence studio.

I want to achieve two things

1) Create a job that will schedule the package to run twice a day

2) Execute this package from an asp.net code.

|||

ok another question

I ran the dbo.sp_enum_sqlagent_subsystems and the result does not list SSIS package.

How can I include the SSIS package as a subsystem ?

|||Strange. Are you connected to SQL 2005 system? Have you installed SSIS (a checkbox during SQL install).

Try connecting to SQL using SQL Server Management Studio, can you create new jobs that use SSIS subsystem?

Execute SQL Task speed

I've created a SSIS package, in a sql 2005 instance, that uses an Execute SQL Task" to call a stored proc as its last step. When run from BIDS, the last step takes about 2 to 3 minutes, consistently. When I run the exact same query from Management Studio (either via exec <spname> or by copying the sp's t-sql code into a query window) it consistently takes about 1 minute. I've run sevral test and these number are quite reproducible.

Any ideas to account for the "slowness" of the Execute SQL Task?

TIA,

Barkingdog

Hi, are you running your package in debug mode?

Try run the package without Visual Studio.

John Bocachica - Colombia

www.iquos-bi.com

|||

The dropdown box at the top of BIDs says "Development"

Here is what I have found. My package runs three control tasks. When I run all three, the Exec SQL tasks takes about 2 minutes to run but when I execute ONLY the Exec SQL task that task runs in about 1 minute!

I saved the package (in BIDS) to a .dtsx file and ran it. The whole process took about 2.5 miniutes which tells me that the Exec SQL task still took about 2 minutes.

barkingdog

|||

What are the other tasks doing? Do they use the same database connection? Are there transactions involved?

|||

The first task truncates a table called Contact. The second task imports a CSV file into a table (uses a SQL Server Destination. Does a straight copy of the data; no transformations. The file imported is on the sql 2005 server and database I'm importing into). The third task (Exec SQL Task) applies various UPDATE statements to the table populated in step 2. Steps 2 and 3 use the same sql connection.

II don't know how to tell if all the tasks belong to the same transaction. I set up three control flows in the same pane but they are not contained in any container object, if that helps at all.)

TIA,

Barkingdog