execute ssis and loggin with sqlserver provider cause error

Hi,

I execute my ssis from BI without problem (debug mode), but when I import into SQL server and try to run with logging on sqlserver provider always my package failure.

If option of logging is empty all works fine (No one logging method is active).

There is some permission to set for this option?

I use into SiSS ole db native client connection.

Hope some one can help me.

Alen Italy

This belove is the error...

An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "La transazione distribuita è stata
completata. Integrare questa sessione in una nuova transazione o nella
transazione Null.".

How exactly are you running the package, and what security is the connection manager used by the log provider set to use?

|||

I create the package on server log-in with the administrator user anda connect the Sql Server management with a windows autentication...so are the same level user.

With no looggin method works fine...with no one error.

I running my package from SQL server management studio...under the folder STORED PACKAGES > MSDB > MYFOLDER > MyPack and right clic on "run package".

Below the connection string:

DB1
Data Source=SQLTEST;Initial Catalog=NEW_LISTINI;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

DB2
Data Source=SQLTEST;Initial Catalog=NEW_PANGEA;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

EXCELL FILE
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\manny-slave\Fornitori\Listini\Altri\\Ready_4_Import\ePRICE.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1;MAXSCANROWS=8;";

I translate in english the error...

An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "The distributed transaction has been completed. To integrate this session in one new transaction or the Null transaction"

The comand line -

/DTS "\MSDB\DTS to SSIS\NEW_LISTINI_IMPORTA_XLS" /SERVER SQLTEST /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"SQLTEST.NEW_LISTINI" /SET "\Package.Variables[arrIDsources].Value";5

_ after many test __

I try many combinations of transaction option.

If I set package "required" works with logging on DB server, but If active "required" on two containers alwails falls.

Seems been not another way for loogging into SQLSERVER.

Or SET all transactions of containers to "supported" or set "Required" at packege level...

If somone heve another vision...here I'm to hear.
Alen

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
RonRon,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/d...>
ols_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
Ron
Ron,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/de...tools_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
RonRon,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron

Execute Package Logon Failed for User....

Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......

I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!

Here is the error message

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".

I suffered a similar problem with ado .net connection. I changed ProtectionLevel property to ServerStorage and it ran fine.

BTW, what about the log?

|||

I guess i can try that, but i like the ease of deployment having the packages file based.

What log?

|||log for that dtsx. By default ssis packages haven't. You can create one from Logging menu option|||Gotcha! thanks for that tip! Is there a package level security i can use for file based packages that is basically no security? We are not concerned about encrypting the sensitive information for this package.|||

OK I logged it to event viewer. It looks like the userid i am using to logon to the sourceconnection (SQL server native client) is also the userid that the package is failing to connect to the local (CLIENT) machine. Why is it trying to logon onto the local PC? Does the user that logs onto this local server USERID1 have to be the same as the userid we are using to logon to the SQL server database USERID2? That doesnt make sense? or does it?

|||Thanks for your help. I was able to figure this out. I was using Package configuration and in it i had selected to override the userid for the sourceconnection (SQL native client). After I took that out, and made the sourceconnection use windows authentication now i am able to execute the package. BTW i also added my local user to the SQL database users. Thanks! I am learning....

Execute Package Logon Failed for User....

Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......

I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!

Here is the error message

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".

I suffered a similar problem with ado .net connection. I changed ProtectionLevel property to ServerStorage and it ran fine.

BTW, what about the log?

|||

I guess i can try that, but i like the ease of deployment having the packages file based.

What log?

|||log for that dtsx. By default ssis packages haven't. You can create one from Logging menu option|||Gotcha! thanks for that tip! Is there a package level security i can use for file based packages that is basically no security? We are not concerned about encrypting the sensitive information for this package.|||

OK I logged it to event viewer. It looks like the userid i am using to logon to the sourceconnection (SQL server native client) is also the userid that the package is failing to connect to the local (CLIENT) machine. Why is it trying to logon onto the local PC? Does the user that logs onto this local server USERID1 have to be the same as the userid we are using to logon to the SQL server database USERID2? That doesnt make sense? or does it?

|||Thanks for your help. I was able to figure this out. I was using Package configuration and in it i had selected to override the userid for the sourceconnection (SQL native client). After I took that out, and made the sourceconnection use windows authentication now i am able to execute the package. BTW i also added my local user to the SQL database users. Thanks! I am learning....

Execute Mutiple Tasks In Debug Mode

Maybe I'm missing something, but I can't find how to run multiple tasks in sequence while in Visual Studio debug mode. In DTS design mode I grew accustomed to right-clicking tasks one-at-a time, but in SSIS I find the additional step of having to exit Debugging mode after every task gets old after a while.

There must be a way to start execution at a certain task and have the package continue all the way to some other specified task. It would also be nice to have every task in a Group execute in sequence and stop (even if connections continue beyond the group). I could even settle for repeatedly clicking the Continue button in Debug mode, but it's always grayed out when the current task is finished!

Can this be achieved by setting breakpoints?

I have previously requested "Execute from here" functionality in the control flow. You can vote for this request here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a2548612-b602-42ab-9ff9-563d927674f8

Adding a comment would help as well.

In the meantime, you can place all required tasks into a sequence container, right click on the sequence container and select "Execute task". All tasks in the container will be executed.

-Jamie

|||

Sequence container appears to be what I was looking for. Thanks. It seems obvious now that you've pointed it out, but features are easy to miss with so much new functionality.

I added some comments on your request.