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 SP installation (report server)

Hi all
I face a weird problem with the installation of SP1/SP2. The scenario
is that the report designer gets updated with the SPs that i apply. But
the problem is with the report server. The installation is successful
but the version number in my report server is still 8.00.743.00 even
after applying the SPs.
I have windows 2003 server with latest SP and SQL server (i'm using
MSDE) with latest SP. I do have local admin rights in my machine.
Am i missing something '
Please suggest some solutions.
Thanks in advance.
Regards,
Prathima.CYes, I don't see how Reports are working for you at all since MSDE is not
supported. It should never have installed in the first place with it. MSDE
is not supported and Personal Edition is not supported.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<prathima.chandramouli@.gmail.com> wrote in message
news:1115571565.489275.126090@.z14g2000cwz.googlegroups.com...
> Hi all
> I face a weird problem with the installation of SP1/SP2. The scenario
> is that the report designer gets updated with the SPs that i apply. But
> the problem is with the report server. The installation is successful
> but the version number in my report server is still 8.00.743.00 even
> after applying the SPs.
> I have windows 2003 server with latest SP and SQL server (i'm using
> MSDE) with latest SP. I do have local admin rights in my machine.
> Am i missing something '
> Please suggest some solutions.
> Thanks in advance.
> Regards,
> Prathima.C
>

Problem with Report Server Authentication.

Hi there,
I'm having this weird issue here on the report server. I am trying to
configure security on the Report Server but right now all users have access
to all folder on the report server even though the only group that I have
access to the report manager and it's contents is the DomainName\
Administrator which is me.
On the IIS, I have disable the anomymous access box and I have enabled the
Integrated windows Authentication option in there. now with all this I'm
amazed that all users that click on the Report Server link are able to do
everything on the report server. even when I assigned that user to a Role,
let's say Browser, this user is still able to see every single folder I have
created there.
Please shed some light on this issue.
Thanks,
Manny
--
Message posted via http://www.sqlmonster.comHi Manny
Try to click on the folder where you put all reports (i.e. MyReports) then
click on Properties Tab and then on Securty link button. Add users do you
want to see the reports by clicking on "New Role Assigment" button, ask "ok"
to next window. Type a valid domain user and check the roles do you want to
grant to, usually "Explorer". Repeat the same for all users do you want to
grant permissions.
I hope I helped you.
Sincerely,
Arturo Carrión
"Manny123 via SQLMonster.com" <u30518@.uwe> escribió en el mensaje
news:6d5c702f49cb3@.uwe...
> Hi there,
> I'm having this weird issue here on the report server. I am trying to
> configure security on the Report Server but right now all users have
> access
> to all folder on the report server even though the only group that I have
> access to the report manager and it's contents is the DomainName\
> Administrator which is me.
> On the IIS, I have disable the anomymous access box and I have enabled the
> Integrated windows Authentication option in there. now with all this I'm
> amazed that all users that click on the Report Server link are able to do
> everything on the report server. even when I assigned that user to a Role,
> let's say Browser, this user is still able to see every single folder I
> have
> created there.
> Please shed some light on this issue.
> Thanks,
> Manny
> --
> Message posted via http://www.sqlmonster.com
>

Problem with profiling datetime

Hi,
SQL Server 2005 x64
I have a weird problem when analysing a trace log with DTA.
The problem is that the trace log include syntax error for datetime!!!
Ex. A date time parameter would appear like this in the trace window.
@.ClaimDate=''2006-03-27 00:00:00:000''
-- Note the two single quotes at the end and start...
Whe analysing, DTA give me this error
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
I can't figue out how to change this so I can't profile any statement that
include date.
Please help!!!!
I will add a sample...since my initial post is not that clear...
That simple .NET code
SqlCommand cmd = new SqlCommand("select @.TestDate");
cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
cmd.Connection = conn;
DateTime dt = (DateTime) cmd.ExecuteScalar();
The resulting trace log item is as follow
exec sp_executesql N'select @.TestDate',N'@.TestDate
datetime',@.TestDate=''2006-04-02 18:44:22:687''
Wich will fail when analyzed since there is a syntax error in the command
e.g. two single quote at the end and start of the date.
Please help!
"Martin Masse" wrote:

> Hi,
> SQL Server 2005 x64
> I have a weird problem when analysing a trace log with DTA.
> The problem is that the trace log include syntax error for datetime!!!
> Ex. A date time parameter would appear like this in the trace window.
> @.ClaimDate=''2006-03-27 00:00:00:000''
> -- Note the two single quotes at the end and start...
> Whe analysing, DTA give me this error
> [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
> I can't figue out how to change this so I can't profile any statement that
> include date.
> Please help!!!!
>
>
>
|||Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I will add a sample...since my initial post is not that clear...
> That simple .NET code
> SqlCommand cmd = new SqlCommand("select @.TestDate");
> cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
> cmd.Connection = conn;
> DateTime dt = (DateTime) cmd.ExecuteScalar();
> The resulting trace log item is as follow
> exec sp_executesql N'select @.TestDate',N'@.TestDate
> datetime',@.TestDate=''2006-04-02 18:44:22:687''
> Wich will fail when analyzed since there is a syntax error in the command
> e.g. two single quote at the end and start of the date.
> Please help!
I don't really have a suggestion how to work around this, as I have not
worked much with the DTA.
However, it is obviously a bug somewhere, although I can't tell whether
it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
DBK48153
What I noticed was that if I changed DateTime.Now to "DateTime.Now",
that is a string, I got the correct output in Profiler.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks Erland.
"Erland Sommarskog" wrote:

> Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I don't really have a suggestion how to work around this, as I have not
> worked much with the DTA.
> However, it is obviously a bug somewhere, although I can't tell whether
> it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
> the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
> http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
> DBK48153
> What I noticed was that if I changed DateTime.Now to "DateTime.Now",
> that is a string, I got the correct output in Profiler.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>