problem with SSIS package

Hi there,

I have a weird problem with SSIS package. Package is deployed and save on SQLServer2k5. It runs no problem if I start from Integration services. It fails every time when it is scheduled as a job.

The error message is: The package execution failed.The step failed.

I will appreciate any advice.

Thanks a lot.

When it's run from a job it executes under the account sql agent is running as. Are you using a data source that is sql authentication? If so you need to set the protection level when you deploy the package to something other than encrypt with user key or else it probably won't work when run as a job. There's a couple of options but if you just want it to work set it to rely on server.|||

Thank you Brent,

Unfortunately I use only Windows security. But I will try to set protection any way.

Cheers.

Michael

|||Make sure the account sql agent is running under has access to the db's you're trying to connect to. If that's a security concern, you can add a proxy account to execute the job under.|||

how can i check if this sql agent is running?

ihave this error when running my package after deploying it

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237837&SiteID=1

|||

ruk_walled wrote:

how can i check if this sql agent is running?

ihave this error when running my package after deploying it

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

this might help: http://support.microsoft.com/kb/918760/en-us|||

Brent,

I am having the same problem. I have thirteen SSIS packages and each one connects to an Oracle Database that uses SQL Authentication. Could you elaborate on the options you mentioned? And how would I set it to rely on the server, server Storage? (if so, doesn't work for me)

Thanks.

|||

Binh Cao wrote:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237837&SiteID=1

Not sure if that was for me Binh, but that also did not work. My problem persists.

|||

What is the exact error you are receiving? It only happens when using SQL Agent?

Rafael

|||

I am receiving an 'AcquireConnection Error' that it cannot connect to the Oracle db. And yes, it only occurs when I try to schedule a job using SQL Agent, it runs in BIDS just fine.

Exact Error:

OnError,EESDCSDS522, [domain]\[login],Data Flow Task 1,{DF1DCB8E-74E7-4C5D-A639-7BAED849AAD7},{72E82E9B-7620-424E-A4E3-70E601497B54},8/10/2006 10:31:08 AM,8/10/2006 10:31:08 AM,-1071611876,0x,The AcquireConnection method call to the connection manager "[Remote Oracle Database]" failed with error code 0xC0202009.

EESDCSDS522 is the local db

I have taken out my domain\login and the name of the Remote Oracle DB for security reasons.

|||

_Phil_,

Is the box where you are running the package a 64-bit machine? SQL Agent will run the 64-bit version of DTSexec and all your 32-bit drivers won't be available for the package. The Work around is to schedule as command line that will call the 32-bit version of dtsexec.

Other thing to check is the credentials of the account used to run the SQL agent service; make sure it has the required permissions.

I hope this helps.

|||

Dear Rafael Salas,

This is the message we are getting when checking the job run history.

Message
The job failed. The Job was invoked by Schedule 4 (Package packageName). The last step to run was step 1 (Package packageName).

and on expanding it shows:

Executed as user: Servername\SYSTEM. The package execution failed. The step failed.

|||

Still facing the same problem.

The system account as well as the account specified to run the agen also fails.

thanks,

|||Try running DTEXEC from the command line under the account that you are attempting to use. That should give you a better error message.

Problem with snapshot replication

I have a snapshot publication, which does not seem to be moving the data. I
only move data for the previous day. The snapshot agent runs, and creates
the .bcp file. I check the MSsnapshot_history table, and the snapshot
completes successfully, and the commands are posted to the distribution
database. I run sp_browsereplcmds, and I can see the commands in there.
The data is not getting moved to my subscriber. I have no idea why. What
can I check to figure this out? I have dropped the subscription, and
re-subscribed, but it just doesn't work.
Please help!
You have to make sure the distribution agent is also running, and not
failing with errors.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"captainkt" <nothing@.fake.com> wrote in message
news:OLA7eR$TFHA.2940@.TK2MSFTNGP10.phx.gbl...
I have a snapshot publication, which does not seem to be moving the data. I
only move data for the previous day. The snapshot agent runs, and creates
the .bcp file. I check the MSsnapshot_history table, and the snapshot
completes successfully, and the commands are posted to the distribution
database. I run sp_browsereplcmds, and I can see the commands in there.
The data is not getting moved to my subscriber. I have no idea why. What
can I check to figure this out? I have dropped the subscription, and
re-subscribed, but it just doesn't work.
Please help!
sql

Problem with simple SQL

I want to view all companyname that starts with A, the sql runs but does not show the output,
What's wrong with the query?, am I missing any quotes or comma. Pls 'elp
Dim myCharacter = "A"
SQL = "SELECTlogo, EmployerID, CompanyName FROM EmployerDetails wherecompanyName like '"&myCharacter & "%'"



Select seems correct! How you are using this in ASP.NET page? Are you getting data into Reader or not?|||Thanks Sreed
I think the problem is the SQL because when I hard code to this
SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like 'A%' "
It works, PLS help Thanks
|||

If you declared myCharacter,SQL as a local string variable, then it should work!

Just to debug after SQL = "..blabla" statement do,

Response.Write(SQL)
Response.End()
and run the page and see what is the output you are getting from SQL, and see if that is giving correct Select or not!

That should help us to find whether it building currect SQL or not!

|||Hi Sreed
This is the full code below , I did the PRINT STUFF and the output is also below
Sub BindDataToGrid(myCharacter)
Response.write("Testing "&myCharacter)
Dim Conn, Conn1 as SqlConnection
Dim mySqlCommand, mySqlCommand2 as SqlCommand
Dim SQL,SQL1,SQL2 ,GetQuestions As String
Dim myDate as date
Dim pic as ArrayList = new ArrayList()
Dim Job_no as Integer

IF myCharacter <> "" THEN
SQL ="SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL & " ' " & myCharacter & " % ' "
Else
SQL ="SELECT logo, EmployerID FROM EmployerDetails order by newID() "
End If
Conn = New SqlConnection(ConfigurationSettings.AppSettings("connString"))
Dim resultsDataSet as New DataSet()
Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(SQL, Conn)

Conn.Open()
myDataAdapter.Fill(resultsDataSet,"EmployerDetails")
mySqlCommand = New SqlCommand(SQL, Conn)
mySqlCommand.ExecuteNonQuery()

Dim myReader As SqlDataReader = mysqlCommand.ExecuteReader()
Dim content
While myReader.Read()
If not (myReader("Logo") is DBNull.Value) Then
If Len(myReader("Logo")) > 0 Then
Dim LOGO = myReader("Logo")
Dim EmployerID =myReader("EmployerID")
Content = "<br><ahref='/JBoard/EmployerJobs.aspx?EMPID="&EmployerID
Content = Content & " '><img src='/JBoard/CompLogo/"&Logo
Content = Content & " ' width='124' height='50'BORDER='1'></a>"
pic.Add(Content)
End If
End If
End While

LogoGrid.DataSource = pic
LogoGrid.DataBind()
Conn.Close()
End Sub

OUTPUT IS
SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like 'M % '
What is wrong with this tiny code, forum help me pls.


|||

SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL &" ' " & myCharacter &" % ' "

This looks like you have extra spaces in there. If you copied and pasted this directly from your code, you're actually querying for anything that starts with a space and then an 'A' and then has a space and then any character and then a space. Drop the spaces in theRED code above and it should work.

|||Hi PD
How do you drop the space, that's why I thought ?

|||Use your keyboard's Delete key to remove the extra spaces around thesingle quote and percentage characters. (A total of 4 spacesshould be deleted.)
That should leave you with the following statement:
SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL &"'" & myCharacter &"%' "
|||

tmorton wrote:

Use your keyboard's Delete key to remove the extra spaces

In a recent technical journal, it suggested that the Backspace key canalso be used to remove characters. I haven't tried it myself, as I fearthat I'm not advanced enough. But obinna123 seems pretty advanced, sohe may want to try that technique to removed the extra spaces.