Running bat files in SSIS

Folks -

Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.

The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.

Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.

Thanks.

Does the batch file have any prompts that require user input?|||

What is SQL Agent Service account running with?

Is it the same account that you are your DBA use - when running the package manually?

Because when you run the job - it runs with the credential of SQL Agent account.

|||Phil, thxs 4 your reply. No prompts that will require user input. The bat file will do a ftp. Thanks again.|||

How do I check what is SQL Agent Service account running my job? I know when I run the pkg manually it will run under my ID.

I do own the SQL Agent job in the Development environment so the DBA told me that my ID should be trying to execute the bat file (as I said before my ID has the permissions so there is something very strange happening). She said when I move to MO it will be owned by SA.

I can't go to MO if it is not working in DEV

Thanks for your reply.

|||If you are using the SSIS job step to run the package, it runs under the service account that the Agent is set up to use, not the job owner's account. Try setting up a proxy account - check books online for details.|||

Hi,

It's simple. Use an Execute Process Task.

Double Click the Task, Under Process Tab, set the values for the following properties:

1) Executable = cmd.exe

2) Arguments = /c C:\BatchFolder\MyFile.bat.

Make Sure when you run the job, The Service account that runs your job should have access to this batch file.

Thanks

Subhash Subramanyam

|||

Jwelch -

There is a proxy account set by the DBA already... When we add a step to a job, for type we do "Operating system (CmdExec)" and for run as we do "SQLAgentJobProxy".

So... Do you believe this proxy account is the one with no access to run the bat file?

Thanks.

|||

That would be my guess.

Also, is the bat file located on a local drive, network share with UNC path, or a mapped drive?

|||Network share with UNC path.|||Might try copying it locally and see if that makes a difference.|||

I don't have Integration Services installed locally. I will check with the DBA on the permissions mentioned here and I will let you guys know.

Thanks.

|||

Folks,

The feedback I had from my DBA was that the SQLAgentProxyAccount does have access to execute the bat file in question... This is proved when I run a dts through the same job and the same bat file is fired successfully. She also agreed that the Executable Property in the Execute Process Task step was evaluating correctly.

So, our work-around was to remove the bat file execution from the SSIS pkg and add it to the second step of the job. Dirty and scary if you think that the ftp might not be the last step of a whole process. In my case, thanks to God it is... But since we have lack of time right now, this is the solution we put in place.

I would say the issue is still not clarified but I will mark this as closed and if somebody in the future runs into the same problem a new thread could be opened.

Thanks to all.

Running bat files in SSIS

Folks -

Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.

The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.

Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.

Thanks.

Does the batch file have any prompts that require user input?|||

What is SQL Agent Service account running with?

Is it the same account that you are your DBA use - when running the package manually?

Because when you run the job - it runs with the credential of SQL Agent account.

|||Phil, thxs 4 your reply. No prompts that will require user input. The bat file will do a ftp. Thanks again.|||

How do I check what is SQL Agent Service account running my job? I know when I run the pkg manually it will run under my ID.

I do own the SQL Agent job in the Development environment so the DBA told me that my ID should be trying to execute the bat file (as I said before my ID has the permissions so there is something very strange happening). She said when I move to MO it will be owned by SA.

I can't go to MO if it is not working in DEV

Thanks for your reply.

|||If you are using the SSIS job step to run the package, it runs under the service account that the Agent is set up to use, not the job owner's account. Try setting up a proxy account - check books online for details.|||

Hi,

It's simple. Use an Execute Process Task.

Double Click the Task, Under Process Tab, set the values for the following properties:

1) Executable = cmd.exe

2) Arguments = /c C:\BatchFolder\MyFile.bat.

Make Sure when you run the job, The Service account that runs your job should have access to this batch file.

Thanks

Subhash Subramanyam

|||

Jwelch -

There is a proxy account set by the DBA already... When we add a step to a job, for type we do "Operating system (CmdExec)" and for run as we do "SQLAgentJobProxy".

So... Do you believe this proxy account is the one with no access to run the bat file?

Thanks.

|||

That would be my guess.

Also, is the bat file located on a local drive, network share with UNC path, or a mapped drive?

|||Network share with UNC path.|||Might try copying it locally and see if that makes a difference.|||

I don't have Integration Services installed locally. I will check with the DBA on the permissions mentioned here and I will let you guys know.

Thanks.

|||

Folks,

The feedback I had from my DBA was that the SQLAgentProxyAccount does have access to execute the bat file in question... This is proved when I run a dts through the same job and the same bat file is fired successfully. She also agreed that the Executable Property in the Execute Process Task step was evaluating correctly.

So, our work-around was to remove the bat file execution from the SSIS pkg and add it to the second step of the job. Dirty and scary if you think that the ftp might not be the last step of a whole process. In my case, thanks to God it is... But since we have lack of time right now, this is the solution we put in place.

I would say the issue is still not clarified but I will mark this as closed and if somebody in the future runs into the same problem a new thread could be opened.

Thanks to all.

Running an IS package in SQL Server Agent

Hi,

I have created a package which gets data from SQL server Source to another SQL Server Database and I execute that using SQL Server Agent with the following command:

DTExec /f "C:\Package.dtsx"

According to my logs, I have an error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "cdp1.MSCRM" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

From BIDS, it doesn't have any error. I only get this when running from SQL Agent.

The way I get the .dtsx file is that, I just BUILD the project from BIDS and use the .dtsx from \bin\Deployment folder to assign to SQL Job.

Have I done it the correct way? thanks a lot!

cherriesh

What type of connection is cdp1.MSCRM?

|||

Hi,

This solves the problem:

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

I just assigned a credential and proxy account and used that in the jobstep.

cherriesh

Running an IS package in SQL Server Agent

Hi,

I have created a package which gets data from SQL server Source to another SQL Server Database and I execute that using SQL Server Agent with the following command:

DTExec /f "C:\Package.dtsx"

According to my logs, I have an error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "cdp1.MSCRM" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

From BIDS, it doesn't have any error. I only get this when running from SQL Agent.

The way I get the .dtsx file is that, I just BUILD the project from BIDS and use the .dtsx from \bin\Deployment folder to assign to SQL Job.

Have I done it the correct way? thanks a lot!

cherriesh

What type of connection is cdp1.MSCRM?

|||

Hi,

This solves the problem:

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

I just assigned a credential and proxy account and used that in the jobstep.

cherriesh

running an exe file SQL server agent

Hi ,
I have an exe program that i do not want to be part of
the services however , i do want it to auto-start when the
server is rebooted.
it is possble to configure it as part of SQL server
Agent job ?
thks & rdgs
maxzism,
Whilst it is possible, it may not necessarily be desirable. What exactly
are you trying to do? Using the Startup folder in windows, or using the
'at' command, or some other software may be a better option.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
maxzsim wrote:
> Hi ,
>
> I have an exe program that i do not want to be part of
> the services however , i do want it to auto-start when the
> server is rebooted.
> it is possble to configure it as part of SQL server
> Agent job ?
>
> thks & rdgs

running an exe file SQL server agent

Hi ,
I have an exe program that i do not want to be part of
the services however , i do want it to auto-start when the
server is rebooted.
it is possble to configure it as part of SQL server
Agent job ?
thks & rdgsmaxzism,
Whilst it is possible, it may not necessarily be desirable. What exactly
are you trying to do? Using the Startup folder in windows, or using the
'at' command, or some other software may be a better option.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
maxzsim wrote:
> Hi ,
>
> I have an exe program that i do not want to be part of
> the services however , i do want it to auto-start when the
> server is rebooted.
> it is possble to configure it as part of SQL server
> Agent job ?
>
> thks & rdgs

running an exe file SQL server agent

Hi ,
I have an exe program that i do not want to be part of
the services however , i do want it to auto-start when the
server is rebooted.
it is possble to configure it as part of SQL server
Agent job ?
thks & rdgsYes,
Look up the xp_cmdshell command, and the SQL Agent 'Start
up Automatically when SQL Agent Starts' in schedule.
Peter
>--Original Message--
>Hi ,
>
> I have an exe program that i do not want to be part of
>the services however , i do want it to auto-start when
the
>server is rebooted.
> it is possble to configure it as part of SQL server
>Agent job ?
>
>thks & rdgs
>.
>|||maxzism,
Whilst it is possible, it may not necessarily be desirable. What exactly
are you trying to do? Using the Startup folder in windows, or using the
'at' command, or some other software may be a better option.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
maxzsim wrote:
> Hi ,
>
> I have an exe program that i do not want to be part of
> the services however , i do want it to auto-start when the
> server is rebooted.
> it is possble to configure it as part of SQL server
> Agent job ?
>
> thks & rdgs|||hi,
thks for ur info , i think it has solved my problem
rdgs
>--Original Message--
>Yes,
>Look up the xp_cmdshell command, and the SQL Agent 'Start
>up Automatically when SQL Agent Starts' in schedule.
>Peter
>>--Original Message--
>>Hi ,
>>
>> I have an exe program that i do not want to be part
of
>>the services however , i do want it to auto-start when
>the
>>server is rebooted.
>> it is possble to configure it as part of SQL server
>>Agent job ?
>>
>>thks & rdgs
>>.
>.
>

Running agent jobs from command line

Hi,
How can i run a SQL Server Agent Job from the command line on the same box? I am using SQL Server 2000.
ThanksCheck out the SQL Books Online for Command Line execution of SQL Agent

The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS package can be stored in the Microsoft SQL Server msdb database, a COM-structured storage file, or SQL Server Meta Data Services.

Syntax
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password|||So i have to create a DTS package to run my job and then use dtsrun to run that package?|||Sorry I sent you the wrong script. I'll be back with the correct one shortly. Mark|||You could create a stored procedure to run the job. Example:
(You may be able to pass the name through as a parameter ??)

CREATE PROCEDURE sp_RunJob
AS

BEGIN

EXEC msdb..sp_start_job @.job_name = 'The job name'

END

You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax.

Then from the command line, create a .bat file and put the following isql command synax into the .bat file:

isql /U username -n -E /d "database name" -Q "stored procedure name" /S servername -oC:\returncode.txt

This will actually produce some output into the returncode.txt file, which might be useful. I'm not sure whether you'll have to return a value back out of the stored procedure or not. I've not tried. My sample above it a cut down version. But anyhow there should be enough above to give you the idea in terms of syntax etc.

You would then just execute the .bat file on your server/pc etc, perhaps via a scheduler etc...

Anyhow a search on SQL Books Online will give you all the details. Search for isql , isqlw etc..|||Cheers Mark.

running a vb .net exe as a sql agent job

I'm primarily a vb .net developer, doubling (as many of us have to) as a sql
2000 dba.
I'm having difficulty running a vb.net exe as a sql agent job. I figured
out that sql 2000 can't really run a windows forms exe, as it thinks it
needs user interaction. However, since it can run an activex script, can it
also run a windows service produced in visual studio 2003?
Thanks for any help.
Bernie Yaeger
As I posted within .programming
I thought that windows service ran interactively (without the need to call
them via the command line)? It should be possible to run any exe that does
not have a gui and that does not require user interaction. There are
permissions issues that you will need to consider if you are not calling the
exe as dbo.
Keith
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fETEc.60920$OT6.24612300@.news4.srv.hcvlny.cv. net...
> I'm primarily a vb .net developer, doubling (as many of us have to) as a
sql
> 2000 dba.
> I'm having difficulty running a vb.net exe as a sql agent job. I figured
> out that sql 2000 can't really run a windows forms exe, as it thinks it
> needs user interaction. However, since it can run an activex script, can
it
> also run a windows service produced in visual studio 2003?
> Thanks for any help.
> Bernie Yaeger
>
|||I'm curious, could you use xp_cmdshell and see if you could run it from
Query analyser, before you test it on sqlagent?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

running a vb .net exe as a sql agent job

I'm primarily a vb .net developer, doubling (as many of us have to) as a sql
2000 dba.
I'm having difficulty running a vb.net exe as a sql agent job. I figured
out that sql 2000 can't really run a windows forms exe, as it thinks it
needs user interaction. However, since it can run an activex script, can it
also run a windows service produced in visual studio 2003?
Thanks for any help.
Bernie YaegerAs I posted within .programming
I thought that windows service ran interactively (without the need to call
them via the command line)? It should be possible to run any exe that does
not have a gui and that does not require user interaction. There are
permissions issues that you will need to consider if you are not calling the
exe as dbo.
Keith
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fETEc.60920$OT6.24612300@.news4.srv.hcvlny.cv.net...
> I'm primarily a vb .net developer, doubling (as many of us have to) as a
sql
> 2000 dba.
> I'm having difficulty running a vb.net exe as a sql agent job. I figured
> out that sql 2000 can't really run a windows forms exe, as it thinks it
> needs user interaction. However, since it can run an activex script, can
it
> also run a windows service produced in visual studio 2003?
> Thanks for any help.
> Bernie Yaeger
>|||I'm curious, could you use xp_cmdshell and see if you could run it from
Query analyser, before you test it on sqlagent?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

running a vb .net exe as a sql agent job

I'm primarily a vb .net developer, doubling (as many of us have to) as a sql
2000 dba.
I'm having difficulty running a vb.net exe as a sql agent job. I figured
out that sql 2000 can't really run a windows forms exe, as it thinks it
needs user interaction. However, since it can run an activex script, can it
also run a windows service produced in visual studio 2003?
Thanks for any help.
Bernie YaegerAs I posted within .programming
I thought that windows service ran interactively (without the need to call
them via the command line)? It should be possible to run any exe that does
not have a gui and that does not require user interaction. There are
permissions issues that you will need to consider if you are not calling the
exe as dbo.
Keith
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fETEc.60920$OT6.24612300@.news4.srv.hcvlny.cv.net...
> I'm primarily a vb .net developer, doubling (as many of us have to) as a
sql
> 2000 dba.
> I'm having difficulty running a vb.net exe as a sql agent job. I figured
> out that sql 2000 can't really run a windows forms exe, as it thinks it
> needs user interaction. However, since it can run an activex script, can
it
> also run a windows service produced in visual studio 2003?
> Thanks for any help.
> Bernie Yaeger
>|||I'm curious, could you use xp_cmdshell and see if you could run it from
Query analyser, before you test it on sqlagent?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

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

Runnable jobs

Hi,
Jobs panel of SQL server Agent has columns which show if job is Enabled,
Runnable, Scheduled.
How to disable Runnable value?
Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
such column.
I use SS2000, SP4, Win 2000 Advance, SP4.
Thanks in advance
Nikola Milicif you delete all child job steps from the job, the "runnable" will show
"NO". oh, you cannot do it from within enterprise manager.
here is a quick demo. run the query below to create an empty job then check
EM for the runnable status.
exec msdb.dbo.sp_add_job @.job_name = N'test'
-oj
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:uiHdBpClGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>|||Nikola Milic wrote:
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>
If you right click on the job on select properties you have an "Enabled"
check box on the first pane. By unclicking this you disable the job.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||if you delete all child job steps from the job, the "runnable" will show
"NO". oh, you cannot do it from within enterprise manager.
here is a quick demo. run the query below to create an empty job then check
EM for the runnable status.
exec msdb.dbo.sp_add_job @.job_name = N'test'
-oj
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:uiHdBpClGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>|||Nikola Milic wrote:
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>
If you right click on the job on select properties you have an "Enabled"
check box on the first pane. By unclicking this you disable the job.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

Runnable jobs

Hi,
Jobs panel of SQL server Agent has columns which show if job is Enabled,
Runnable, Scheduled.
How to disable Runnable value?
Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
such column.
I use SS2000, SP4, Win 2000 Advance, SP4.
Thanks in advance
Nikola Milicif you delete all child job steps from the job, the "runnable" will show
"NO". oh, you cannot do it from within enterprise manager.
here is a quick demo. run the query below to create an empty job then check
EM for the runnable status.
exec msdb.dbo.sp_add_job @.job_name = N'test'
-oj
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:uiHdBpClGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>|||Nikola Milic wrote:
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>
If you right click on the job on select properties you have an "Enabled"
check box on the first pane. By unclicking this you disable the job.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator

run SSIS package with ODBC connection via SQL agent

It seems there a lot of problems running SSIS packages under the sql agent. I have read the knowledgebase articles regarding permission issues etc but I still can't get my job to run. I can run any package as a job apart from a package that connects to an external database via an odbc connection. Has anyone had any luck with this and can let me in on the secret.

I had a lot of problems with this too, the way I was able to resolve it was to configure the SQL Agent to run as a domain service account, and then log into the server under the domain service account and create my SSIS package. Save the package to disk and then import it into SSIS, use the SQL Agent to run a job that points to the SSIS package and it then succeeded. In my case it was a security issue when the SQL Agent was running under a different account than the one I had created the package under. Once I used the same account to do everything I was able to get it to work.

|||

Hey Andy

Thanks for the reply, it seems that the issue is that I'm using a 64 bit OS and 32 bit odbc driver and when sql agent runs it uses the 64bit program files as opposed to the 32 bit program files which means that it couldn't find my odbc dsn. So to get around this had to create a batch file which calls the 'x86' program files.

Have also looked at getting a 64 bit odbc for my postgres database but seems that SSIS does point at 32 bit odbc connectiosn so don't see how to include the 64 bit odbc in SSIS.

Ugly and messy!

|||

Hi there,

Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.

Crazy stuff huh?

|||

I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).

When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).

From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first event - packageStart, but the credentials (or operator) changed from domainABC\user123 to NT AUTHORITY\NETWORK SERVICE after packageStart. Then, it complains … either the user, domainABC\serverName$, does not have access to the database, or the database does not exist.

Please help and thank you all!

|||

Jenny,

It sounds like the credentials of your web application pool are being passed to SQL Server, the default account for the application pool is NT AUTHORITY\NETWORK SERVICE. Have you checked that?

|||

I had the same issue: A package using a 32-bit ODBC driver, it ran fine in my laptop but not in my 64-bit server. The work arround was to schedule it through an SQL Agent that used a command line to invoked the 32-bit version of dtsexec. The bottom line: the package runs in a 32-bit mode instead of 64-bit mode

Rafael

|||

Hi Andy, thanks for the response.

I have worked on something else these two days. I just checked the web application pool and both my web app and web service are pointed to the DefaultAppPool and its property setting for Identity as Predefined - Network Service. Should I create a new web application pool for both my web app and web service to use? I do need impersonation. How do I configure the new web application pool? Thanks!!!

|||

Here, are some more info.

In the SSIS package, there are three connection managers –

· A:Microsoft OLE DB Provider for Analysis Services 9.0 à connectionType=OleDbConnection

· B: .Net Providers \ SqlClient Data Provider à connection type=SqlConnection

· C:Native OLE DB \ Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB

After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps which relate to the connection manager B – ADO.Net connection. The remaining steps failed which are related to the connection managers A & C. I think the credentials are passed ok but some setting related to the Analysis services are not correct. Any clues?

|||

Jenny,

There are a few different ways to go about this so why don't we try the easiest way first. According to the error message you are receiving, the domain\server$ does not have access to the database. So what's happening here is because your web application pool is running with the credentials of network service, SQL Server is viewing the account as ServerName$. So what you need to do is go into SQL Server>Security and add a new user with access permissions to the database. When you add the new user, type in the domain name and the server name with a $ at the end of it, for example: mydomain\myserver$

Make sure under the user mappings you check the box of the database that the user needs access to and also check the datareader box. This will give your web server access to read the database. If you need write permissions you can go back and check the datawriter box.

Typically you should run your web application under a domain service account, provide the domain service account access to the database, and use Windows authentication, something like this for your connection string: "server=MySQLServer; Integrated Security=SSPI; database=MyDatabase"

There are probably specific reasons why you want to use Impersonate, so give the mydomain\myserver$ example a try and see if you can get it to work.

|||

Hi Andy,

FYI: The problem was solved. What I did are list as below:

A. We created a new web app pool and pointed the both web application and web service to this web app pool (which was configured not use default identity - Predefined: Network Service but use Configurable - created a new use name).

B. Under SQL server , added this new user and assign the certain rights to it.

C. In the web application and web service, set impersonate=false (instead of true)

D. In the SSIS package, we also have a flat file destination connection which is to write the output to .txt file. I give the read & write rights to this new use. then, it works.

Thank you very much for your kind help!!!

Jenny

|||

Andy_T wrote:

Hi there,

Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.

Crazy stuff huh?

Had a somewhat different problem... BIDS is running 32 bit and connecting to 32-bit ODBC drivers (Paradox DB in this case), but when executed, the server keeps looking for 64-bit drivers. While I wish it would look for ALL possible drivers and automagically drop down from 64 to 32 if necessary, setting Run64BitRuntime to False fixed it for now (which is too bad, IBM has 64 bit drivers for the AS400). Haven't scheduled the package yet, though.|||Hi,

i'm having the same problem (trying to execute a package located on server A and a web service on server B), but i'm using IIS 5.1, so i think i don't have the web application pool feature.

I also have the package's Package source configured as SSIS Package Store so my path refers to "File System" source type.

from a custom application, i call the web service and i returns "failure" when trying to execute the package (this one is retrieved successfully, started but not executed).

Is there any way to solve this problem on a similar way like Jenny_99?

regards.

rodri|||

I had a similar issue and this is the workaround I found.

We installed SQL2005 with SSIS on a 64-bit windows 2003 server. An IBM ISeries for windows (V5R3M with the latest Service Pack) was used for connecting to AS400. There are actually two versions of ODBC drivers on the 64-bit windows (odbcad32.exe). One is located at Windows\System32. Another is at Windows\SysWOW64. Originally I thought the one in System 32 is 32-bit version, and the other one is 64-bit version. But the driver version number under SysWOW64 is actually consistent with the version number in our 32-bit windows 2003 server. So I am not so sure any more. Anyway, what I found out is that DSN under SysWOW64 is actually used when the package is running in BIDS, or SSIS on the server. The one under System32 is used when the package is scheduled as a job. So I created the same DSN on both drivers to cover all bases since I don't know how to force the SQL agent to use a specific driver.

run SSIS package with ODBC connection via SQL agent

It seems there a lot of problems running SSIS packages under the sql agent. I have read the knowledgebase articles regarding permission issues etc but I still can't get my job to run. I can run any package as a job apart from a package that connects to an external database via an odbc connection. Has anyone had any luck with this and can let me in on the secret.

I had a lot of problems with this too, the way I was able to resolve it was to configure the SQL Agent to run as a domain service account, and then log into the server under the domain service account and create my SSIS package. Save the package to disk and then import it into SSIS, use the SQL Agent to run a job that points to the SSIS package and it then succeeded. In my case it was a security issue when the SQL Agent was running under a different account than the one I had created the package under. Once I used the same account to do everything I was able to get it to work.

|||

Hey Andy

Thanks for the reply, it seems that the issue is that I'm using a 64 bit OS and 32 bit odbc driver and when sql agent runs it uses the 64bit program files as opposed to the 32 bit program files which means that it couldn't find my odbc dsn. So to get around this had to create a batch file which calls the 'x86' program files.

Have also looked at getting a 64 bit odbc for my postgres database but seems that SSIS does point at 32 bit odbc connectiosn so don't see how to include the 64 bit odbc in SSIS.

Ugly and messy!

|||

Hi there,

Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.

Crazy stuff huh?

|||

I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).

When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).

From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first event - packageStart, but the credentials (or operator) changed from domainABC\user123 to NT AUTHORITY\NETWORK SERVICE after packageStart. Then, it complains … either the user, domainABC\serverName$, does not have access to the database, or the database does not exist.

Please help and thank you all!

|||

Jenny,

It sounds like the credentials of your web application pool are being passed to SQL Server, the default account for the application pool is NT AUTHORITY\NETWORK SERVICE. Have you checked that?

|||

I had the same issue: A package using a 32-bit ODBC driver, it ran fine in my laptop but not in my 64-bit server. The work arround was to schedule it through an SQL Agent that used a command line to invoked the 32-bit version of dtsexec. The bottom line: the package runs in a 32-bit mode instead of 64-bit mode

Rafael

|||

Hi Andy, thanks for the response.

I have worked on something else these two days. I just checked the web application pool and both my web app and web service are pointed to the DefaultAppPool and its property setting for Identity as Predefined - Network Service. Should I create a new web application pool for both my web app and web service to use? I do need impersonation. How do I configure the new web application pool? Thanks!!!

|||

Here, are some more info.

In the SSIS package, there are three connection managers –

· A:Microsoft OLE DB Provider for Analysis Services 9.0 à connectionType=OleDbConnection

· B: .Net Providers \ SqlClient Data Provider à connection type=SqlConnection

· C:Native OLE DB \ Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB

After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps which relate to the connection manager B – ADO.Net connection. The remaining steps failed which are related to the connection managers A & C. I think the credentials are passed ok but some setting related to the Analysis services are not correct. Any clues?

|||

Jenny,

There are a few different ways to go about this so why don't we try the easiest way first. According to the error message you are receiving, the domain\server$ does not have access to the database. So what's happening here is because your web application pool is running with the credentials of network service, SQL Server is viewing the account as ServerName$. So what you need to do is go into SQL Server>Security and add a new user with access permissions to the database. When you add the new user, type in the domain name and the server name with a $ at the end of it, for example: mydomain\myserver$

Make sure under the user mappings you check the box of the database that the user needs access to and also check the datareader box. This will give your web server access to read the database. If you need write permissions you can go back and check the datawriter box.

Typically you should run your web application under a domain service account, provide the domain service account access to the database, and use Windows authentication, something like this for your connection string: "server=MySQLServer; Integrated Security=SSPI; database=MyDatabase"

There are probably specific reasons why you want to use Impersonate, so give the mydomain\myserver$ example a try and see if you can get it to work.

|||

Hi Andy,

FYI: The problem was solved. What I did are list as below:

A. We created a new web app pool and pointed the both web application and web service to this web app pool (which was configured not use default identity - Predefined: Network Service but use Configurable - created a new use name).

B. Under SQL server , added this new user and assign the certain rights to it.

C. In the web application and web service, set impersonate=false (instead of true)

D. In the SSIS package, we also have a flat file destination connection which is to write the output to .txt file. I give the read & write rights to this new use. then, it works.

Thank you very much for your kind help!!!

Jenny

|||

Andy_T wrote:

Hi there,

Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.

Crazy stuff huh?

Had a somewhat different problem... BIDS is running 32 bit and connecting to 32-bit ODBC drivers (Paradox DB in this case), but when executed, the server keeps looking for 64-bit drivers. While I wish it would look for ALL possible drivers and automagically drop down from 64 to 32 if necessary, setting Run64BitRuntime to False fixed it for now (which is too bad, IBM has 64 bit drivers for the AS400). Haven't scheduled the package yet, though.|||Hi,

i'm having the same problem (trying to execute a package located on server A and a web service on server B), but i'm using IIS 5.1, so i think i don't have the web application pool feature.

I also have the package's Package source configured as SSIS Package Store so my path refers to "File System" source type.

from a custom application, i call the web service and i returns "failure" when trying to execute the package (this one is retrieved successfully, started but not executed).

Is there any way to solve this problem on a similar way like Jenny_99?

regards.

rodri|||

I had a similar issue and this is the workaround I found.

We installed SQL2005 with SSIS on a 64-bit windows 2003 server. An IBM ISeries for windows (V5R3M with the latest Service Pack) was used for connecting to AS400. There are actually two versions of ODBC drivers on the 64-bit windows (odbcad32.exe). One is located at Windows\System32. Another is at Windows\SysWOW64. Originally I thought the one in System 32 is 32-bit version, and the other one is 64-bit version. But the driver version number under SysWOW64 is actually consistent with the version number in our 32-bit windows 2003 server. So I am not so sure any more. Anyway, what I found out is that DSN under SysWOW64 is actually used when the package is running in BIDS, or SSIS on the server. The one under System32 is used when the package is scheduled as a job. So I created the same DSN on both drivers to cover all bases since I don't know how to force the SQL agent to use a specific driver.

sql

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 SSIS Package from SQL 2000

I am looking for a way to execute an SSIS package as the next step in the SQL 2000 Agent, with the first step being replication. Can I reference dtexec.exe on the SSIS box to execute the package located in the file system?

Thanks

JWeil wrote:

... Can I reference dtexec.exe on the SSIS box to execute the package located in the file system?

Yes, should work. Agent in SQL 2000 has a limitation of just one proxy account, but if you are OK with this and the package can be run under this account, it should be fine.

|||

Thanks.

Would I need to install anything on the SQL 2000 server or just reference the executable on the 2005 box?

|||SSIS should be installed on the machine where the package is run, you can't run it by pointing to dtexec on remote share.

If you want to run the package on the same machine, where SQL 2000 is installed - install SSIS on this box.

If you want to run the package on another box where SQL 2005 is installed, you need some other way - e.g. create Agent job on SQL 2005 machine, invoke it remotely using Agent store procedures.

Run SSIS package as an sql server job

Hi There,

I have sqlserver agent running with a windows user account which is member of administrator group. I try to set up the job to run an ssis package and set up to run as sql server agent service account. For some reason, the job fails and it doesn't give much error info if I look at job History. Can somebody plzz help me? I am unable to understand what extra permissions must that user have to run the ssis package as a sql job? And how do I configure the job to give more error details?

Any suggestions are greatly appreciated.

Thanks.

See this KB:
http://support.microsoft.com/kb/918760