Running an SSIS package as a job that connects to a DB

I have a package that I want to run as a scheduled job in sql server 2005. The job runs fine in the studio. I am convinced that it is not a password or security issue as I set the protectionlevel to do not save sensitive data and can still run the job in the studio. The User running the job has SA privileges as does the SQL Server Agent. The job reads a flat file, runs it through a script and than inserts the rows into a table. The job runs successfully only if the flat file is empty. The job history says: The return value was unknown. The process exit code was -1073741795. The step failed.

There are no OnError lines in the logging.

Anyone have any Ideas or tricks to try.

Hi, have you seen this article?

http://support.microsoft.com/?kbid=918760

Your issue does still sound 'permission' related. Perhaps not an issue with a SQL connecton but maybe access to the file share where the files are located. One thing the article points out is how to get some logging data back to the console\file via executing the SSIS package using the command exec sub system (in agent) rather than the SSIS subsystem.

Hope that helps

|||

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

|||Anything new about this? I got the same Problem and in fact have completely lost a whole day because of this completele useless and meaningless type of errorhandling. >:(|||

Is there any resolution for this?

Executed as user: xxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:09:19 PM. The return value was unknown. The process exit code was -532459699. The step failed.

|||

HeatherS2006 wrote:

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

But it's not the account that Agent runs under, it's the account that SQL Server runs under that you need to look at.|||

any resolution?

It's been like this since August?

|||

The account is a domain account. It is part of the Local Administrator Group.

It belongs to the BUILTIN/Administrators in SQL.

Did I overlook anything?

sql

Running an SSIS package as a job that connects to a DB

I have a package that I want to run as a scheduled job in sql server 2005. The job runs fine in the studio. I am convinced that it is not a password or security issue as I set the protectionlevel to do not save sensitive data and can still run the job in the studio. The User running the job has SA privileges as does the SQL Server Agent. The job reads a flat file, runs it through a script and than inserts the rows into a table. The job runs successfully only if the flat file is empty. The job history says: The return value was unknown. The process exit code was -1073741795. The step failed.

There are no OnError lines in the logging.

Anyone have any Ideas or tricks to try.

Hi, have you seen this article?

http://support.microsoft.com/?kbid=918760

Your issue does still sound 'permission' related. Perhaps not an issue with a SQL connecton but maybe access to the file share where the files are located. One thing the article points out is how to get some logging data back to the console\file via executing the SSIS package using the command exec sub system (in agent) rather than the SSIS subsystem.

Hope that helps

|||

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

|||Anything new about this? I got the same Problem and in fact have completely lost a whole day because of this completele useless and meaningless type of errorhandling. >:(|||

Is there any resolution for this?

Executed as user: xxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:09:19 PM. The return value was unknown. The process exit code was -532459699. The step failed.

|||

HeatherS2006 wrote:

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

But it's not the account that Agent runs under, it's the account that SQL Server runs under that you need to look at.|||

any resolution?

It's been like this since August?

|||

The account is a domain account. It is part of the Local Administrator Group.

It belongs to the BUILTIN/Administrators in SQL.

Did I overlook anything?

Running a Stored Proc before report runs

Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?

Thanks!

You can make individual datasets be populated by a stored procedures. I think what you're eluding to is having one stored proc return multiple tables/results which is not supported.

The only way to achieve this is potentially to use a custom data delivery extension.

|||

I haven't tried this, but if your dataset's are coming from stored procedures, you could just call your 'data generation' stored procedure at the beginning of your reporting stored procedure.

Hope this helps.

Jarret

|||Nope, not alluding to one stored procedure return mulitple data-sets. :) I knew that wouldn't work. Actually, the stored procedure populates 4 tables with data from various other tables. Those 4 tables are used in the 4 different data-sets in the report. I'd like to be able to run my data-populating stored procedure before the report runs. I can do this using a scheduler and make sure it runs before the report. But sometimes things go wrong, and I can see the stored procedure not running and then the report will go out with no data...or something of that nature. I just thought it would be great if the running of the stored procedure could be tied to the report somehow.|||Actually, I tried that. I put the execution of the SP in the beginning of the dataset of the first table on the report. That 1st table had data. But the other 4 don't. I was hoping that since that was the first one, it might run in sequential order. :) Guess not.|||

Is there anyway you can break up the 'data generation' into 4 stored procedures so that each report calls an individual one to populate the data?

Jarret

|||

Nope. They all use the same tables.

Just for further clarification (don't feel like you need to read this).... The "data generation" SP takes all the call the calls that come into our call-center, gets the number of the person calling, the length of the call, etc. This data (after much manipulation) goes into one table (CTICalls). Then it creates another table for all the "work tickets" that were created due to the calls that came in. Now, all this data is in various other separate tables from the CTI Calls. It's a completely different system. Because of this, you can't just match up the name of the person who called to a ticket...further manipulation is required. All these tickets go into another table (CRMTickets). On top of that, the silly people who want this report want to know that name of the person who called. :) Of course, all I've got is the phone number. This requires another table because the table which actually contains the phone numbers has lots of duplicates and other bad things. So now I've got a phone number table with the name of the person calling. Great. So now the SP creates another table, which matches the CTI calls to the CRM tickets, sticks in the name of the person calling. So now I've got the table I need for the report. The first report is a summary - the number of calls per call center agent, and the number of tickets created. Next report lists all the calls that have no ticket created. Then we just list all of the calls, and then all of the tickets.

Hopefully, now you can see why I want to run the data generation first and why it can't really be broken up.

Jennifer

|||The only way I can think of ensuring the order in which the dataset queries are executed is to use dependant parameters, even if you just use some dummy values.|||

Hi,

I had quite similar problem with multiple sp-based datasets. The first one populated the global temporary tables and subsequent datasets displayed the data. To make sure the master SP will be executed first and no other datasets will be run before master SP completes, you have to do the following:

- Organize your datasets in exact order you need them to run. Datasets can only be moved by editing the RDL file. Find the <Datasets> section and move individual dataset sections.

- Enable transaction flag of the data source to prevent you datasets from being executed concurrently. In the RDL file, add <Transaction> element to the data source properties. Below is the example.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_inv_rep">
<Transaction>true</Transaction>
<DataSourceReference>ds_inv_rep</DataSourceReference>
<rd:DataSourceID>7f21a5bb-83e1-4c9c-a32b-5ee080055ed6</rd:DataSourceID>
</DataSource>

Wapper

Running a Stored Proc before report runs

Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?

Thanks!

You can make individual datasets be populated by a stored procedures. I think what you're eluding to is having one stored proc return multiple tables/results which is not supported.

The only way to achieve this is potentially to use a custom data delivery extension.

|||

I haven't tried this, but if your dataset's are coming from stored procedures, you could just call your 'data generation' stored procedure at the beginning of your reporting stored procedure.

Hope this helps.

Jarret

|||Nope, not alluding to one stored procedure return mulitple data-sets. :) I knew that wouldn't work. Actually, the stored procedure populates 4 tables with data from various other tables. Those 4 tables are used in the 4 different data-sets in the report. I'd like to be able to run my data-populating stored procedure before the report runs. I can do this using a scheduler and make sure it runs before the report. But sometimes things go wrong, and I can see the stored procedure not running and then the report will go out with no data...or something of that nature. I just thought it would be great if the running of the stored procedure could be tied to the report somehow.|||Actually, I tried that. I put the execution of the SP in the beginning of the dataset of the first table on the report. That 1st table had data. But the other 4 don't. I was hoping that since that was the first one, it might run in sequential order. :) Guess not.|||

Is there anyway you can break up the 'data generation' into 4 stored procedures so that each report calls an individual one to populate the data?

Jarret

|||

Nope. They all use the same tables.

Just for further clarification (don't feel like you need to read this).... The "data generation" SP takes all the call the calls that come into our call-center, gets the number of the person calling, the length of the call, etc. This data (after much manipulation) goes into one table (CTICalls). Then it creates another table for all the "work tickets" that were created due to the calls that came in. Now, all this data is in various other separate tables from the CTI Calls. It's a completely different system. Because of this, you can't just match up the name of the person who called to a ticket...further manipulation is required. All these tickets go into another table (CRMTickets). On top of that, the silly people who want this report want to know that name of the person who called. :) Of course, all I've got is the phone number. This requires another table because the table which actually contains the phone numbers has lots of duplicates and other bad things. So now I've got a phone number table with the name of the person calling. Great. So now the SP creates another table, which matches the CTI calls to the CRM tickets, sticks in the name of the person calling. So now I've got the table I need for the report. The first report is a summary - the number of calls per call center agent, and the number of tickets created. Next report lists all the calls that have no ticket created. Then we just list all of the calls, and then all of the tickets.

Hopefully, now you can see why I want to run the data generation first and why it can't really be broken up.

Jennifer

|||The only way I can think of ensuring the order in which the dataset queries are executed is to use dependant parameters, even if you just use some dummy values.|||

Hi,

I had quite similar problem with multiple sp-based datasets. The first one populated the global temporary tables and subsequent datasets displayed the data. To make sure the master SP will be executed first and no other datasets will be run before master SP completes, you have to do the following:

- Organize your datasets in exact order you need them to run. Datasets can only be moved by editing the RDL file. Find the <Datasets> section and move individual dataset sections.

- Enable transaction flag of the data source to prevent you datasets from being executed concurrently. In the RDL file, add <Transaction> element to the data source properties. Below is the example.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_inv_rep">
<Transaction>true</Transaction>
<DataSourceReference>ds_inv_rep</DataSourceReference>
<rd:DataSourceID>7f21a5bb-83e1-4c9c-a32b-5ee080055ed6</rd:DataSourceID>
</DataSource>

Wapper

Running a Report Automatically

I want to be able to setup a report that runs everynight at a certain time. Anyone know how to schedule sql server reports?

Thanks

macca

On your report server:

For an existing or shared schedule

Site settings:

Running a package from another computer with DTSRunUI

I set up a package with runs from a batch file on my computer after creating
the script with DTSRunUI. I cannot run it on another computer. Do I have t
o
copy DTSRun.exe to the other computer with the related rll files to get it t
o
run ? Is there a recommended way to do this ?It sounds like you are looking for the steps on
redistributing DTS. You can find information on this in the
Redist.txt file on the installation CD/DVD for SQL Server.
The dts.com web site has a great article on redistribution:
Redistributing DTS with your program
http://www.sqldts.com/default.aspx?225
-Sue
On Wed, 16 Aug 2006 10:12:02 -0700, rmcompute
<rmcompute@.discussions.microsoft.com> wrote:

>I set up a package with runs from a batch file on my computer after creatin
g
>the script with DTSRunUI. I cannot run it on another computer. Do I have
to
>copy DTSRun.exe to the other computer with the related rll files to get it
to
>run ? Is there a recommended way to do this ?

running a job issue with ole db connections

Hi,

Got another question. I run my job and it runs the first two steps fine but when I hit the third step i get an error. In the third step I have an ole db connection to an AS400 db and downloading data from it. Is there any way that I can get this to read and accept the ole db connection because it seems like it doesn't recognize it.

Thanks

Brian

What is the third step? A task of some sort? What kind of task- what object did you drag onto the surface?

Donald Farmer

sql

Running a job continuously

Dear All,
I would like to have a job that runs all the times, how
should I set up the scheduler ?What does the job do? Do you have a loop so it never finishes? If not, they you cannot have it run
continuous, you can only schedule it very frequently.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pidgeon" <anonymous@.discussions.microsoft.com> wrote in message
news:0bdc01c4a6fc$76513760$a301280a@.phx.gbl...
> Dear All,
> I would like to have a job that runs all the times, how
> should I set up the scheduler ?
>|||Thanks Tibor
We have a fairly complicated audit procedure where we need
to scan data continuously, I think I will go for your idea
of having a loop that never ends.
I know its a bit of a bad solution but I have been out
voted.
>--Original Message--
>What does the job do? Do you have a loop so it never
finishes? If not, they you cannot have it run
>continuous, you can only schedule it very frequently.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Pidgeon" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0bdc01c4a6fc$76513760$a301280a@.phx.gbl...
>> Dear All,
>> I would like to have a job that runs all the times, how
>> should I set up the scheduler ?
>>
>
>.
>|||What I do is creating 3 steps in the job. The 1st step runs "EXEC
msdb..sp_purge_jobhistory 'jobname'" to clear the history for this job. The
2nd step runs the procedure and it goes back to step1 on success and step 3
on failure. Step 3 prints out the error report in case the job fails.
"Pidgeon" wrote:
> Dear All,
> I would like to have a job that runs all the times, how
> should I set up the scheduler ?
>
>

Running a job continuously

Dear All,
I would like to have a job that runs all the times, how
should I set up the scheduler ?
What does the job do? Do you have a loop so it never finishes? If not, they you cannot have it run
continuous, you can only schedule it very frequently.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pidgeon" <anonymous@.discussions.microsoft.com> wrote in message
news:0bdc01c4a6fc$76513760$a301280a@.phx.gbl...
> Dear All,
> I would like to have a job that runs all the times, how
> should I set up the scheduler ?
>
|||Thanks Tibor
We have a fairly complicated audit procedure where we need
to scan data continuously, I think I will go for your idea
of having a loop that never ends.
I know its a bit of a bad solution but I have been out
voted.

>--Original Message--
>What does the job do? Do you have a loop so it never
finishes? If not, they you cannot have it run
>continuous, you can only schedule it very frequently.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Pidgeon" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0bdc01c4a6fc$76513760$a301280a@.phx.gbl...
>
>.
>
|||What I do is creating 3 steps in the job. The 1st step runs "EXEC
msdb..sp_purge_jobhistory 'jobname'" to clear the history for this job. The
2nd step runs the procedure and it goes back to step1 on success and step 3
on failure. Step 3 prints out the error report in case the job fails.
"Pidgeon" wrote:

> Dear All,
> I would like to have a job that runs all the times, how
> should I set up the scheduler ?
>
>
sql

Running a batch command using scheduler

Hi,

I have a batch file that runs an isql script. The sql accepts command line arguments as parameters. I want to invoke this batch file using windows scheduler. How do I pass parameters to my sql? Somebody please help.

Thanks.

I am not sure about the batch file idea, but SQL server has a job scheduler built in called sql agent. You would be much better using this to call a stored procedure and pass in the arguments. To get you started, try this:

http://msdn2.microsoft.com/en-us/library(d=robot)/ms189237.aspx

Otherwise, why not replace your batch file with a .NET console application that takes command line parameters and passes them to a stored procedure call. Batch files are old, and while they still have some uses, there are much better ways of achieving what you are trying to do in an easier way, which gives you more power and flexibility.

There is a way to run sql scripts from the commandline using an exe supplied with sql server. For information on this, check out:

http://msdn2.microsoft.com/en-us/library/ms170207.aspx

HTH

For more T-SQL tips, check out my home page:|||Thanks for the reply :)

RUNNIG A JOB?

I HAVE A PROBLEM,, I``ve just deployed a package into my server..it runs without problems from my SSIS.. but when i set a job with the sql agent and the package to it,. the job just fails .. why? any solution?

please somebody help me!!!

yes, this question comes up a lot, I apologize for the confusion. We are working on a KB article to help explain issues around this. It should be ready within a few days. The short asnwer is that when you execute under agent, you are (miost likely) executing "as" a different user than when you execute in the dev studio or command line with dtexec.exe.

So the reason a different login may fail could be due to the ProtectionLevel property the package is set to AND/OR the user you execute with under agent does not have some permission needed, like writing to a certain file share where the package wants to dump a file.

You should read up on the SQL Server Agent proxy acccounts and the SSIS package protection level. Usual cause is the default protection level is encrypt sensitive with a user key, and if its a different user OR different machine you will not be able to load all of the properties in the package such as connection info. You could therefore create an agent proxy account to 'mimic' the same as the pacake author , or change to a password protection level and suppy the password in the Agent job, or change protection level to server storage which puts the package into SQL and you can control access via SQL DB roles rather than a specific user encryption key.

You may also search the forum for more on this as lots of helpful folks in this forum community have responded to similar questions and collectively there is a lot of info here.

Hope this helps

|||

i`ve tried all this options.. set the protection level to storage server when copying to the target server.. created an agent proxy account =this account has identity of an administrator in my server= after that i set the run as property of the job to this account and still won`t work

thanks for your help

|||This article describes what can go wrong and how to troubleshoot it:
http://support.microsoft.com/kb/918760

Please check it out.|||Ruk, as Micahel noted the KB article I referred to is now published. Please review the article. Based on experience, the article should cover all the possible cases that would result in the issue you seem to have, but if not then we will need a more information such as the messages returned to the SQL Agent Job history and error messages returned via SSIS Logging.|||

i've tried to catch the error by running each task of my dts separately.. then i found the one that is causin troubles.. it looks like by using data flow tasks something unknown happens.. i'm using an OLE DB source to retrieve some data from the db... netx step i use this data conversion task because some of the results of the query aren`t supported by mi destination which is an OLE DB destination that maps to an excel file in the target server.. this is weird cause i run this taks in my bids and runs ok.. i run this again from SSIS in the target server and runs ok.. but when i scheduled it in a job it just fails.... any solution?

thanks for your help!!!!

|||Have you followed the part of the KB article where it describes how to configure logging using CmdExec Agent subsystem, or SSIS logging? What are the errors reported?|||

these are some errors that the log provided

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
--

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXX\Administrator
Source Name: DESIE_CatalogoDeSucursales(Semanal)
Source ID: {D1025489-4A06-4347-9674-0A73A1847829}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Name: OnError
Message: component "OLE DB Destination" (40) failed validation and returned error code 0xC020801C.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1073450985
For more information, see Help and Support Center at

"Sucursales" is the name of the connection manager that maps the xls file destination. the job just fails when trying to validate it

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

|||

ruk_walled wrote:

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

Where is XLS file located: local disk, UNC network drive (\\machine\drive\file), mapped network drive?

When you've tried changing destination to flat file - was the CSV file in the same location as XLS? If not, try placing the CVS to the same folder (to check whether it is related to destination-type or the problem is with access to this location).

|||

the xls file is located in a local disk.... and the flat file is created in the same path with no problems,.. the problem is not at the moment of creating the file.. but at the moment of validate the connection manager that refers to this local path...when using xls

|||

I can't fit some parts together:

my destination which is an OLE DB destination that maps to an excel file in the target server|||

i'm sorry my english is not good enough

here is the thing.. i'm doing a report from some data and try to write it in an xls file.

i have a data flow task that connects to a db in the target server to retrieve the data. this uses an OLE DB connection manager which has the server name.. the db. and the user to connect. --this part parses ok.

since there are some data that doesn′t match with the predefined ones.. i use a data conversion transformation task

then..

i'm using an ole DB destination which uses an OLE DB connection manager that maps to the file that is located in a local disk on the target server.. the file exists and it contains only the headers .

the package is stored in the server.. so is the file destination. i also changed the OLE DB destination for an Excel Destination but i happened the same..

i copied this dts from mi computer to the target server using the option save a copy of. from the bids and setting the protection level to server storage.

when i schedule the job it seems to run but as the log shows.. something happens when trying to validate the connection manager that maps the path of the file.. but the path and the file exists...

|||

i have finally found the problem... all my dts were programmed under 32 bits environment ..<my target server is running under 64 bits so this was the problem.. that i'm using jet 4.0 which is not supported for 64bits so this is the answer:

thanks to michael Entin..

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"

|||

Craig,

Where is the KB Article? Is it at http://support.microsoft.com/kb/918760 b/c this was not helpful for the problem that I face.

|||

Hi,This is dinesh here.I need to create a job and schedule it in sql server 2005.But I dont find the sql server agent option under the database instance in the object explorer. I need to write a simple job and just schedule it. Do i need to instal some extra components of sql server 2005?

I would appreciate if you could help me.

RUNNIG A JOB?

I HAVE A PROBLEM,, I``ve just deployed a package into my server..it runs without problems from my SSIS.. but when i set a job with the sql agent and the package to it,. the job just fails .. why? any solution?

please somebody help me!!!

yes, this question comes up a lot, I apologize for the confusion. We are working on a KB article to help explain issues around this. It should be ready within a few days. The short asnwer is that when you execute under agent, you are (miost likely) executing "as" a different user than when you execute in the dev studio or command line with dtexec.exe.

So the reason a different login may fail could be due to the ProtectionLevel property the package is set to AND/OR the user you execute with under agent does not have some permission needed, like writing to a certain file share where the package wants to dump a file.

You should read up on the SQL Server Agent proxy acccounts and the SSIS package protection level. Usual cause is the default protection level is encrypt sensitive with a user key, and if its a different user OR different machine you will not be able to load all of the properties in the package such as connection info. You could therefore create an agent proxy account to 'mimic' the same as the pacake author , or change to a password protection level and suppy the password in the Agent job, or change protection level to server storage which puts the package into SQL and you can control access via SQL DB roles rather than a specific user encryption key.

You may also search the forum for more on this as lots of helpful folks in this forum community have responded to similar questions and collectively there is a lot of info here.

Hope this helps

|||

i`ve tried all this options.. set the protection level to storage server when copying to the target server.. created an agent proxy account =this account has identity of an administrator in my server= after that i set the run as property of the job to this account and still won`t work

thanks for your help

|||This article describes what can go wrong and how to troubleshoot it:
http://support.microsoft.com/kb/918760

Please check it out.|||Ruk, as Micahel noted the KB article I referred to is now published. Please review the article. Based on experience, the article should cover all the possible cases that would result in the issue you seem to have, but if not then we will need a more information such as the messages returned to the SQL Agent Job history and error messages returned via SSIS Logging.|||

i've tried to catch the error by running each task of my dts separately.. then i found the one that is causin troubles.. it looks like by using data flow tasks something unknown happens.. i'm using an OLE DB source to retrieve some data from the db... netx step i use this data conversion task because some of the results of the query aren`t supported by mi destination which is an OLE DB destination that maps to an excel file in the target server.. this is weird cause i run this taks in my bids and runs ok.. i run this again from SSIS in the target server and runs ok.. but when i scheduled it in a job it just fails.... any solution?

thanks for your help!!!!

|||Have you followed the part of the KB article where it describes how to configure logging using CmdExec Agent subsystem, or SSIS logging? What are the errors reported?|||

these are some errors that the log provided

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
--

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXX\Administrator
Source Name: DESIE_CatalogoDeSucursales(Semanal)
Source ID: {D1025489-4A06-4347-9674-0A73A1847829}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Name: OnError
Message: component "OLE DB Destination" (40) failed validation and returned error code 0xC020801C.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1073450985
For more information, see Help and Support Center at

"Sucursales" is the name of the connection manager that maps the xls file destination. the job just fails when trying to validate it

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

|||

ruk_walled wrote:

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

Where is XLS file located: local disk, UNC network drive (\\machine\drive\file), mapped network drive?

When you've tried changing destination to flat file - was the CSV file in the same location as XLS? If not, try placing the CVS to the same folder (to check whether it is related to destination-type or the problem is with access to this location).

|||

the xls file is located in a local disk.... and the flat file is created in the same path with no problems,.. the problem is not at the moment of creating the file.. but at the moment of validate the connection manager that refers to this local path...when using xls

|||

I can't fit some parts together:

my destination which is an OLE DB destination that maps to an excel file in the target server|||

i'm sorry my english is not good enough

here is the thing.. i'm doing a report from some data and try to write it in an xls file.

i have a data flow task that connects to a db in the target server to retrieve the data. this uses an OLE DB connection manager which has the server name.. the db. and the user to connect. --this part parses ok.

since there are some data that doesn′t match with the predefined ones.. i use a data conversion transformation task

then..

i'm using an ole DB destination which uses an OLE DB connection manager that maps to the file that is located in a local disk on the target server.. the file exists and it contains only the headers .

the package is stored in the server.. so is the file destination. i also changed the OLE DB destination for an Excel Destination but i happened the same..

i copied this dts from mi computer to the target server using the option save a copy of. from the bids and setting the protection level to server storage.

when i schedule the job it seems to run but as the log shows.. something happens when trying to validate the connection manager that maps the path of the file.. but the path and the file exists...

|||

i have finally found the problem... all my dts were programmed under 32 bits environment ..<my target server is running under 64 bits so this was the problem.. that i'm using jet 4.0 which is not supported for 64bits so this is the answer:

thanks to michael Entin..

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"

|||

Craig,

Where is the KB Article? Is it at http://support.microsoft.com/kb/918760 b/c this was not helpful for the problem that I face.

|||

Hi,This is dinesh here.I need to create a job and schedule it in sql server 2005.But I dont find the sql server agent option under the database instance in the object explorer. I need to write a simple job and just schedule it. Do i need to instal some extra components of sql server 2005?

I would appreciate if you could help me.

sql

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
KannanIs this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegroups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ' multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.
> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegroups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
Kannan
Is this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>
|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>
|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan
|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.

> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegro ups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>
|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ? multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan
|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.

> will opening multiple connections from end application help ? multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.

> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegr oups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ? multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
KannanIs this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.

> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegroups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ' multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.

> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.

> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegroups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run SSIS Package problem when using SQL Agent

Maybe someone else has asked similar question. But I still can't solve my problem after looking some threads in this forum.

The package runs fine through VS 2005. After imported into MSDB, it still does well when I start it in Integration service directly. But the packgae never achieved under SQL Agent.

Acturaly,agent can run the package,but always fail in one task. It's an 'Execute Process Task ', I use 'winrar.exe' to extract file from a mapped drive to the local disk. The Agent service account is a domain user who has the read permission on the mapped drive,also has been grant sysadmin on the SQL istance. So I think the right of the Agent account is enough.

Thanks for your help

What's the error?

|||

I am having a similar problem.

I have created an Integration Package in BI and deployed it to the SQL Server. I can run the package fine from Integration Services but when I run it as a job I am getting the following message. I have looked up the error but there is no additional information.

TITLE: Microsoft.SqlServer.SmoEnum

Failed to retrieve data for this request.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

BUTTONS:

OK

|||

In the package log file,there are no information about the failed task in detail. On error message just show that return value is 1 but 0 is expected.

Now,I know the key is authority. I copy the compressed files from the mapped drive to a local disk drive and change the drive name in the expression of the task,then everything is ok under SQL Agent.

So what about the Agent service account, I am still confused. The account I use can do anything on the local system, I even ask the NetworK Admin grant me the write permission on the mapped drive,but the job still failed when execute the "winrar" process task. As I have said, the pre-executed tasks run fine under Agent. In debug mode or in integration services instance of object explorer, there are no error at all.

Run SQL T-SQL Job Step across Different Domains

I have two sql 2005 servers in two different domains. I have a SQL job on
one of the servers that runs a T-SQL script that accesses databases on the
SQL box inthe other domain.
I'm getting a message in the job history that the job won't run because of
the security context.
How can I set this up?
Thanks for your help.
--
MWDWhat Operating Systems are these two servers running?
What trusts, if any, are set up between the two domains?
How are you trying to connect?
What is the exact error number and error message?
-Sue
On Thu, 9 Aug 2007 08:10:09 -0700, MWD
<MWD@.discussions.microsoft.com> wrote:

>I have two sql 2005 servers in two different domains. I have a SQL job on
>one of the servers that runs a T-SQL script that accesses databases on the
>SQL box inthe other domain.
>I'm getting a message in the job history that the job won't run because of
>the security context.
>How can I set this up?
>Thanks for your help.sql

run packages as part of procedure

I have created SQL code that is contained in different procedures. I have a procedure that then runs all of the pieces that I wish to run. My last step in my process is to export the data to my c: drive. During this process I created a package to be rerun. How do I include the rerunning of my package automatically as part of the procedural process?

How about creating a job for the package, and calling sp_start_job from your procedure. Note this will not wait for the package to complete.

A less attactive option is to enable xp_cmdshell, and call DTEXEC through xp_cmdshell, which itself can be called from your procedure.

Run IN PROCESS

Sorry but how do you configure to run IN PROCESS. My task runs like this in a BAT file

dtexec /f "\\ssssagemet\xteurs\Admin\FC\Test\DTSX\Test_Alex\Rename_file_alex.dtsx"

Thanks,

aL.

What is the issue, exactly?

Run Excel macro from SQL Server Agent

Hi,
I have a DTS package that (among other things) runs an Excel macro.
The macro pulls in data from the server, formats it, then saves the
workbook to a network drive. The package runs fine when executed
locally but will not run as a scheduled job since Excel is not
installed on the server. I need to be able to schedule this job
somehow, and as you can imagine, the DBA and network admins are not
about to install Office on the server. Is there a way around this?Without OLE automation (i.e. *.dll) you would not be able to run the macro.
I don't think there is a way without installing the component on the server.
--
-oj
<jono@.mailinator.com> wrote in message
news:1123803060.725514.253790@.z14g2000cwz.googlegroups.com...
> Hi,
> I have a DTS package that (among other things) runs an Excel macro.
> The macro pulls in data from the server, formats it, then saves the
> workbook to a network drive. The package runs fine when executed
> locally but will not run as a scheduled job since Excel is not
> installed on the server. I need to be able to schedule this job
> somehow, and as you can imagine, the DBA and network admins are not
> about to install Office on the server. Is there a way around this?
>