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 SQL and c#

i have a very weird thing going on in my application program..

you see..

my c# application program has an sql connection..

and it can save records..

using stored procedures

but as soon as i stop debugging it

and then re-run it again..

all the records saved in my Database

literally disappears.!!

i need help..

please help out a young kid here...T_T

im very confused now..

*sigh*

Is the insert within a transaction that you are possibly not completing during your debugging session?

You also have to be careful when debugging C# or any .NET program. The line of code that you are on when you stop debugging is not necessarily the last line of code that executes. .NET programs complete the current thread that they are running before exiting. This means that although you think you have not hit a certain line of code that you actually have.

|||

It is possible that something is going wrong with your transaction, and so it is being rolled back when you stop debugging. There is a neat utility called "Sql Profiler" that we package free with SQL server that will enable you to see what is going on to debug this problem.

I hope this helps,

John (MSFT)

|||

can you please kindly discuss to me

this SQL profiler..

to tell you the truth..

i'm just a newbie in doing this stuff

you see..

my stored procedures follows

the program flow..

after executing that..

uhm..i close the sql connection..

is that right?

after that..

i have this line of code..

here's my actual sample program..

try

{

SqlConnection connect = new SqlConnection();

connect.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True";

connect.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "AddMembers";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = connect;

cmd.Parameters.Clear();

cmd.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);

cmd.Parameters["@.lastName"].Value = txtLastName.Text;

cmd.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);

cmd.Parameters["@.FirstName"].Value = txtFirstName.Text;

cmd.Parameters.Add("@.Address", SqlDbType.VarChar, 50);

cmd.Parameters["@.Address"].Value = txtAddress.Text;

cmd.Parameters.Add("@.MobileNo", SqlDbType.VarChar, 50);

cmd.Parameters["@.MobileNo"].Value = txtMobileNo.Text;

cmd.Parameters.Add("@.EmailAdd", SqlDbType.VarChar, 50);

cmd.Parameters["@.EmailAdd"].Value = txtEmailAdd.Text;

cmd.ExecuteNonQuery();

MessageBox.Show("Membership successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);

connect.Close();

this.membersTableAdapter1.Fill(this.database1DataSet2.Members);

txtLastName.Text = string.Empty;

txtFirstName.Text = string.Empty;

txtAddress.Text = string.Empty;

txtMobileNo.Text = string.Empty;

txtEmailAdd.Text = string.Empty;

txtLastName.ReadOnly = true;

txtFirstName.ReadOnly = true;

txtAddress.ReadOnly = true;

txtMobileNo.ReadOnly = true;

txtEmailAdd.ReadOnly = true;

btnAdd.Text = "&Add";

}

catch (Exception ex)

{

MessageBox.Show(ex.GetBaseException().ToString(), "Connection Status");

}

plase tell me if there's sumthin wrong bout my code..

as i've said earlier im just beginning to

understand the ropes of c# connected with SQL

thanks a whole lot to the help!!!

|||

to tell you the truth..

i'm just a newbie in doing this stuff

you see..

my stored procedures follows

the program flow..

after executing that..

uhm..i close the sql connection..

is that right?

after that..

i have this line of code..

here's my actual sample program..

try

{

SqlConnection connect = new SqlConnection();

connect.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True";

connect.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "AddMembers";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = connect;

cmd.Parameters.Clear();

cmd.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);

cmd.Parameters["@.lastName"].Value = txtLastName.Text;

cmd.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);

cmd.Parameters["@.FirstName"].Value = txtFirstName.Text;

cmd.Parameters.Add("@.Address", SqlDbType.VarChar, 50);

cmd.Parameters["@.Address"].Value = txtAddress.Text;

cmd.Parameters.Add("@.MobileNo", SqlDbType.VarChar, 50);

cmd.Parameters["@.MobileNo"].Value = txtMobileNo.Text;

cmd.Parameters.Add("@.EmailAdd", SqlDbType.VarChar, 50);

cmd.Parameters["@.EmailAdd"].Value = txtEmailAdd.Text;

cmd.ExecuteNonQuery();

MessageBox.Show("Membership successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);

connect.Close();

this.membersTableAdapter1.Fill(this.database1DataSet2.Members);

txtLastName.Text = string.Empty;

txtFirstName.Text = string.Empty;

txtAddress.Text = string.Empty;

txtMobileNo.Text = string.Empty;

txtEmailAdd.Text = string.Empty;

txtLastName.ReadOnly = true;

txtFirstName.ReadOnly = true;

txtAddress.ReadOnly = true;

txtMobileNo.ReadOnly = true;

txtEmailAdd.ReadOnly = true;

btnAdd.Text = "&Add";

}

catch (Exception ex)

{

MessageBox.Show(ex.GetBaseException().ToString(), "Connection Status");

}

plase tell me if there's sumthin wrong bout my code..

as i've said earlier im just beginning to

understand the ropes of c# connected with SQL

thanks a whole lot to the help!!!

|||See this blog here, that is purely a development setting problem as you are using a user instance.

http://blogs.msdn.com/sqlexpress/archive/2006/07/17/668971.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks a lot for helping me out..

that weblink just made it for me..

i havent tried yet using a deployment class as of yet..

since the copy to directory property modifications worked out

just as well for me..

at least for the time being..^_^

jeez..i owe it to you jens..

you sure know this huh?!

well..

my sincerest thanks to all the help i've got here..

problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

Problem with saving Chinese.

Hi.. I'm trying to save text into SQL 2000 database.

When user enter text in text box , the text save into variable , and show it in confirm page , after save the text to database, all the text turned into "??"

I try to view the data in SQL enterprise Manager / Web Matrix / ASP web page gridview , all of them showing the text fields in "??"

Then I try to add record which come with SQL 2000 enterprise Manager.After save the record , the chinese also turned into "??"

Is there something I need to set for database or server?

Is your datatype VARCHAR, TEXT or CHAR as the datatype on the table columns?

If VARCHAR change to NVARCHAR. If TEXT change to NTEXT. If CHAR change to NCHAR.

If you are using stored procedures (and you should be), you will to amend the parameter declaration on them.

You might want to change the collating sequence on your database to one more appropriate to Chinese (if collating sequence is meaningful for that language), but otherwise you should not need to change anything at the database level.

If this reply provides the anser to your question, please mark as such.

|||all the text are stored in varchar... I'll try to change it to nvarchar|||

yea... it works when I change all the text field type to nnvarchar..