SQL Agent, credentials, proxies and SSIS

Running SQL Server 2008, on Windows Server 2008.

I'm having a problem trying to execute a job.  The job has executed successfully when I configure the SQL Agent account (domain account with sysadmin permissions in the instance) as the owner/executor of the job.  However, I want to tighten down security so I'm trying to use a proxy/credential.

Here are the steps I've followed:

1) create a non-privileged SQL account called "SQLJobOwner" that owns the job.

2) create a stored credential (domain account) that has an account on the local machine where the instance resides, and has "log on as batch" permissions.

3) created a login to the instance for the domain account that is stored as a credential, and granted sysadmin permissions to the account.

3) create a new proxy called "SSISProxy", and granted access to the SSIS subsystem.

4) Added job owner "SQLJobOwner" as a principal in the SSISProxy configuration window.

5) changed the job step that executes the SSIS package to "Run as" the "SSISProxy".

 

When I execute the job, the job fails with the following error:

Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1).  The step failed.

In addition, I get

Hi: I have a Developer License for MSSQL Server 2008.  Have just upgraded to Windows 7 Professional Edition.  When installing MS SQL Server, the setup shows that I have an instance of SQL Server Express already installed.  I am given a choice between Default Instance and Named Instance.  First time I chose named instance and then used the name of my machine - RBS1.  I received the following message:  The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service. Went back and tried Default instance and got same answer.  Can't proceed to install.  Any help in this greatly appreciated. roger  

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

My SQL Agent was working fine.  Now when I try to run it, it failes when trying to run my SSIS package.  I looked in the logs, and job history, no luck in finding any critical errors. My SSIS package runs fine through VS 2005 in debug mode. Here are the only errors: 01/04/2006 09:53:48,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect01/04/2006 09:53:48,,Warning,[260] Unable to start mail session (reason: No mail profile defined)01/04/2006 09:53:48,,Information,[129] SQLSERVERAGENT starting under Windows NT service control01/04/2006 09:53:48,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent01/04/2006 09:53:30,,Information,[432] There are 11 subsystems in the subsystems cache01/04/2006 09:53:29,,Information,[339] Local computer is BG-SQL2005 running Windows NT 5.2 (3790) Service Pack 101/04/2006 09:53:29,,Information,[310] 1 processor(s) and 512 MB RAM detected01/04/2006 09:53:29,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is01/04/2006 09:53:29,,Information,[102] SQL Server ODBC driver version 9.00.139901/04/2006 09:53:29,,Information,[101] SQL Server BG-SQL2005 version 9.00.1399 (0 connection limit)01/04/2006 09:53:29,,Information,[100] Microsoft SQLServerAgent version 9.00.1399.06 (x86 unicode retail build) : Process ID 1860

Hi All

I have designed a SSIS package to encrypt a file using Cipher (EFS) it works fine from BIDS & Package Execution Utility.

But SQL Server Agent job is failing to do this. I am getting the error as

" In executing C:\Windows\System32\Cipher.exe /e /a C:\Test.txt at "",The Process Exit code was 1 while the expected was 0." 

 

Please help me.

 

Thanks

 

Hi

I have run into a strange problem and hoping someone may have a suggestion.

We have a set of SSIS packages that are developed in BIDS 2005. The packages are all executed by SQL Agent Jobs on a scheduled basis. These packages have been running on numerous SQL 2005 instances for the last year or so with no issues being experienced.

We recently has our first customer start running SQL 2008 R2 and we have internally upgraded to SQL 2008 R2 in our development environment. We are not converting the packages to SQL 2008 as we still need to support customers who are running SQL 2005. The problem is that in both our dev environment and at this particular customer, we have seem a random issue where the SQL Agent job never seems to end. Most of our jobs normally execute in less than 2 minutes, but occasionally (one or twice a week) a random job will just not end until we manually cancel it. We don't know if it is the SSIS package that is not ending or the SQL job.

We never experienced this on SQL 2005.

Anyone have any ideas what may be causing this? Any suggestions for troubleshooting?

Any help will be much appreciated

 

---

Craig Bryden - Please mark correct answers

Which one of them is better to just run a set of Strored Procedures on a daily basis.

Can I have some Adv and Dis advantages. Please.

Thanks

I have developed an SSIS 2005 package but the client was reluctant to install SSIS on their SQL Server and instead installed it (SSIS) on an IIS server which doesn't have SQL Server on it all. Microsoft documentation surprisingly indicated this would work. http://technet.microsoft.com/en-gb/library/cc966389.aspx states "SSIS packages can also be run on systems where SQL Server is not installed. However, the .NET Framework and the SSIS runtime must be installed in order to execute the packages on systems that do not have SQL Server 2005 installed."

The package can be run from within BIDS and by invoking from the SSIS server fine and runs to completion. It fails when invoked by the SQL Server Agent on the actual SQL Server (i.e. NOT the SSIS server) (which IS Enterprise version) and raises a DTS_E_PRODUCTLEVELTOLOW error. This is the third "step" out of three and is a fairly basic data flow task where the results of a SQL Server proceedure are output to a text file.

I have logging on which doesn't tell me anything more than I already knew
OnError,<ServerName>,<SQLServerAgentUser>,<PackageName>,{C88F42DE-E3A8-40CB-A140-F10E270C5F1F},{1D2B421E-E067-4458-A908-23C7F081EF28},9/30/2010 4:52:21 PM,9/30/2010 4:52:21 PM,-1073450754,0x,

We have tried to run a SQL job for SSIS Package. But it failed during the execution with the error "[364] The Messenger service has not been started - NetSend notifications will not be sent". But in the job, no notifications have been configured and in windows server 2008/Windows-7 there is no messenger service at all.

When we search for the the messenger service in windows 7/windows server 2008, we found that service is not in both the OS and it was replaced by msg.exe. 

Any help or workaround would be appreciated.

Thanks in advance.

PS: The normal SQL jobs other than SSIS are working fine without any error.

---

Vijay Pandurangan

SQL Server 2008 SSIS 32-bit using Oracle 11g 32 bit client to connect to Oracle 10gR2

The job actually runs and populates the destination tables correctly, but for some reason the agent reports the job as failing.  there is only one step (the SSIS package itself).

The package execution returned DTSER_SUCCESS (0).  The return value was unknown.  The process exit code was -1073741819.  The step failed.

There are many other packages that are built more or less the same that do not throw an error but for some reason this one does.

I know this isnt a lot of information - mostly hoping for tips on what steps I can take to narrow in on the problem.

I've run the package directly from Integration Services on the server as well as from BIDS on my desktop and it succeeds.

I have an SSIS 2008 package running on Win 2008 64-bit server. The package has an Execute Process Task that runs a net use command using cmd.exe to make a connection.

This runs successfully when tested from the command prompt and runs successfully when the package is run in Visual Studio 2008. However, when the package is called from a SQLAgent job the package fails with the error (actual servernames and passwords removed):

Failing Task Name = EPT Connect mapped drive Error Code = -1073573551 Error Detail = In Executing "C:\WINNT\system32\cmd.exe" "/C net use
\\servername\foldername /USER:userid password" at "", The process exit code was "1" while the expected was "0".

I have determined that a successfull connection should provide a exit code of 0.

The job runs under a proxy account but as explained when the package has run from within VS 2008 it was also tested to run under the proxy account and is successful.

It is not a 64-bit issue as I have tested it by running the package using the 32-bit version of dtexec.exe and the same problem occurs.

Also I have found that by running a simple command process within the Execute Process Task such as dir *.* it is successful when run from the job indicating that it is in fact something related to the net use command itself when run from

Hello,

I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.

I tried all the possible fixes I found on the web but still can't get it to work.

Could you shed some light on it?

Thanks,

Sangita

Greetings,

We have an SSIS package that builds a large analysis services cube by doing a "full process" of the cube.  The SSIS package normally is executed by a scheduled agent job.  This arangement has been in production for a year and had worked well until recently.  We use SQL Server 2008 and Windows Server 2008.

Recently, the SSIS package has been having problems when run by the agent job.  The agent job executes the SSIS package and the agent job reports that the job ended successfully.  However, the cube has not been usable because the measure group partitions are indicated as "unprocessed".  We also have determined that the pre-existing fact files in the ".prt" folder are being deleted by the SSIS package but are not being recreated by the package as they should.

This doesn't happen if we execute the SSIS package directly.  If we open the Integration Services server and run the cube-builder package, everything performs as it always has and the cube is fully processed.  When the SSIS package is run directly, it executes significantly longer than if run through the agent job, and we are convinced this is because the measure groups are being created.  We have repeated our testing enough to know that the measure groups always are proc

Having problems reading an excel file from a networked drive while running a package as a SQL Agent job step as a type "SQL Server Integration Services Package". I have agent running as an AD account, and I can use the execute package utility logged in as this account and it works. When I log into the server as this account, I can see the network drive, read/write/create to folders on the drive but when I try to run the agent job it cant find the file. I tried using the unc path in the config file rather than a mapped drive, still no luck. If I use a local drive (c) in the config file, the job works fine so it has to do with the network drive. Running windows server 2003 standard R2, SQL Server 2005 standard sp3. Any ideas out there?

I have several SSIS jobs that I have created and they operate just fine when running manually or even when the job is scheduled.  Lately, I have been seeing some intermittent failures in these jobs and am having a difficult time in understanding how to resolve this issue.    All Jobs runs consistently overnight with no issues and start to fail intermittently as the load on the server increases.

Attached is the output from a successful job and a failed job.

Any Suggestions?

--Successful Run

Date                      4/8/2011 9:45:01 AM

Log                         Job History (WFM_TCSDATA_EXP_LAS_15)

 

Step ID                 1

Server                   DNC-UCCECUIS04

Job Name                      &nb

We have a job that is using a foreach loop to grab input from a text file, perform some transformations and place it into a SQL Server database using OLE DB providers.  The package works fine from the local environment (within Visual Studio).  Then when you import the package from the file system into the MSDB package store, it works fine from there as well.

The problem is when we try to schedule the job using the SQL Server Agent.  It is a one step job and it says its successful when we run it, but it doesn't put any information into the database itself.  We have other packages running that are working that run from the package store and take information from one database and put it into the same SQL Server DB we are trying to put our text file information into.

Has anyone encountered the problem where the package works from the file system as well as the package store, but doesn't work when you schedule it as a job?

I have a package that I have created that runs locally and as package on the server that has a batch script with in it and it run correctly when manually run. But when I create a sql server agent job it runs the job but doesn't execute the package but returns with a successful run. I have setup a proxy account trying to run it with this. I have given the permission to the batch script to the file that the SQL service account. I have tried everything I can think of even setting the package creator to the same name as the service account in the package, and even doing a dtexec command from the SQL job. If I run the dtexec from a cmd prompt the package runs. Need help please thanks.