Reading Package Variables From Custom Log Provider

Oct 1, 2007

I am interested in determining if package variables can be read from a custom log provider. I am able to pass the values into the custom log provider using an expression in a custom connection manager. My intention is to used the values of these variables upon package failure so I can capture the context of the failure. Unfortunately, if the value of the variable is changed at runtime, this update is not reflected in the custom log provider.

Is it possible to get direct access to the package variables from the custom log provider?

Thanks,
Marc

View 1 Replies

Accessing A Package's Variables From Within A Custom Log Provider...or Not...

Feb 21, 2007

Hi,

Given that Task.Validate() exposes the package's VariableDispenser, but LogProviderBase.Validate() doesn't...

http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.task.validate.aspx

http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.logproviderbase.validate.aspx

...I guess that simply means that I can't access a package's variables within a custom log provider? Can anyone comment/confirm? Any other options/routes to achieving the same..?

We live in hope,

Tamim.

View 3 Replies View Related

Access To Variables In Custom Log Provider?

Jun 29, 2006

Hello,

I found one post from the past asking this question, but didn't see an answer, so I am hoping someone can shed some more light on it:

I am writing a custom log provider in C#. Is it possible give this custom log provider access to the variables collection (via a variabledispenser object)? First glance at the log provider classes would tell me that this is not possible, though I would like confirmation. Also, if I am correct in thinking it is not possible, what was the reason for leaving this out?

Thanks!

View 3 Replies View Related

Accessing Variables Collection From A Custom Log Provider

Oct 1, 2007

As far as I can make out it is not possible to access the variables collection from a custom log provider.

I'm interested to know what the rationale for this is. Why aren't the variables available for logging purposes?

Thanks
Jamie

[Microsoft follow-up]

View 6 Replies View Related

Custom Task And Reading Package Configuration

Oct 27, 2006

Hello All,

I have searched on this forum for a similar question but couldn't find it so I apologize if this has been asked. If so, I'd greatly appreciate a link to the question. I have created a custom task and am trying to read an xml package configuration file within my custom task. To be more specific, I have added an ADO.Net Connection to my database onto the package and have generated the appropriate tags within my package's configuration xml file. I'm not seeing the classes I should use to access the configuration file of the package I've created. Does anybody have any ideas how I can accomplish this or a link to a document that might cover the material? Thanks!

Jay_G

View 3 Replies View Related

How To Access Package Variables In A Custom Task

Jul 3, 2006

Hi Everyone,

I created a script task that uploads a file to a HTTPs site. The script task works well and now I want to turn this script task into a custom component...

I'm having problems on how to call the variables I have in the package, I already locked them for reading, but how am I suppose to access them and how can I put their values into strings so I can easily call them in the WebClient.UploadFile method?

Also, I am very new to custom tasks in SSIS.. and would appreciate if anyone of you can send me something (code, link, etc.) of a very simple custom task without any form or properties, that I can try to study.

Basically, what I want my custom task to do is,

1. Read the varialbes in the package.
2. use the variables values as input in my WebClient.UploadFile("varURLstring", "PUT", "varFilePath")

as you can see, my custom task is really simple... but I'm a noob here and I don't know where to go.

Thanks in advance,
Kervy

View 1 Replies View Related

Package With A Custom Component (log Provider) Runs In BIDS, But Doesn't Run Where Deployed.

Feb 14, 2007

Hi,

I have a package with a custom log provider, which runs in BIDS. However when I deploy the package onto SQL Server and run it on the deployed machine, if fails:

"failed to decrypt protected XML node DTS:Password...key not valid for use in specified state..."

Now this is definately to do with the custom log, as if I take it out & redeploy, I can run it on the deplyed server + also run it within a job. I have entered the custom log provider library (+ other required DLLs) in the GAC on the deployed machine, but I'm clearly missing something.

Any ideas pls??? I'm really stuck.

Many thanks in advance,

Tamim.

View 3 Replies View Related

How To Read The Names Of Package Variables In Custom Task

Nov 22, 2005

Hi there,

View 4 Replies View Related

Get The List Of Variables In A Package Inside A Custom Component

Oct 26, 2006

Hi

I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?

Thanks

Mani

View 6 Replies View Related

Custom Task: How To Access/modify The Variables Of A Package

Feb 1, 2007

Hi,

I'm trying to simplify the deployment process of my project. I already had some troubles with the config files but lets say I solved that issue. I'm going to read a flat file and set the variables of my packages from this file. I was thinking to use a Script Task to do that but I will need to copy this task in every package (I have at least 30). So if I want to make some change this will be painful.

Then, I came up with the idea of creating a Custom Task called Config File Task. I'm working on this but I got stuck trying to get the variables from the package that is running my Config File Task.

This is the code I had in the Script Task:

Dim streamReader As New StreamReader(Dts.Variables("ConfigFilePath").Value.ToString)
Dim line As String
Dim lineArray As String()
Dim variableName As String
Dim variableValue As String
Dim readConfigurations As Boolean = False

While (streamReader.Peek() <> -1)
line = streamReader.ReadLine()
If line = "[CONFIGURATIONS]" Then
readConfigurations = True
ElseIf line = "[/CONFIGURATIONS]" Then
readConfigurations = False
Else
If readConfigurations And line <> "" Then
lineArray = line.Split("|".ToCharArray())
variableName = lineArray(0).Trim()
variableValue = lineArray(1).Trim()

If Dts.Variables.Contains(variableName) Then
Dts.Variables(variableName).Value = variableValue
End If
End If
End If
End While
Dts.TaskResult = Dts.Results.Success

All I want to do is set the variables that exists in each package from the config file. In my UI Class (ConfigFileTaskUI.cs) I can have access to the variables via the TaskHost which is passed as an argument of Initialize() method.

Any thoughts? I'd really appreciate some help!

P.S. I've been working on this for 2 days!

View 11 Replies View Related

HowTo: Read/write Package Level Variables In Custom Task

Oct 13, 2006

Hello all,

I have been struggling trying to read and/or write package level variables from within my custom task. I'd like to be able to get and set values from within the Execute method of my custom task. I have searched this forum and the books online and can't seem to find the answer. I thought maybe I could use an expression on my task (mapping the package variable to a custom task public property) but that doesn't seem to be working for me. I also would have thought I could use the VariableDispenser object from within my task but the collection is empty. I have 3 package level variables configured and can't seem to find a way to access them (with intentions of getting/setting). Could someone point me to a good doc or provide an example that may accomplish this? Thanks!

(I'm using package level variables as a means of passing simple information between tasks that are not using a DB, if there is a better way I'm open to suggestions.)

Jay_G

View 3 Replies View Related

Custom Log Provider Type Not Recognized As A Valid Log Provider

Mar 13, 2006

I am new to SSIS but I am learning fast. Any help is greatly appreciated.

I have written a custom log provider derived from LogProviderBase. I want to programmtically add the custom log provider to my package, but when I try this:

myPackage.LogProviders.Add("ACustomLogProvider");

I get the following exception:

"The log provider type "ACustomLogProvider" specified for log provider "{..GUID..}" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name".

I have followed the instructions in BOL and as suggested in this forum, adding the DtsLogProvider attribute:

[DtsLogProvider(DisplayName="ACustomLogProvider", LogProviderType="ACustomLogProvider", Description="a description")]

Another way I tried is that I added a GUID attribute to my custom log provider class and then tried this:

Type aType = typeof(ACustomLogProvider);

Guid aGUID = (Guid)aType.GUID;

myPackage.LogProviders.Add("{"+ aGUID.ToString() +"}");

This also gives me the same exception "The log provider type ""{..the actual GUID for ACustomLogProvider..}" specified for log provider "{..a different GUID..}" is not recognized as a valid log provider type....

My questions are: Is it possible to add a custom log provider programmtically or must it be done through the SSIS Designer?

Must I deploy my custom log provider class for it to work programmatically? (I had perhaps wrongly assumed I would not have to deploy if I don't want it to show up in the SSIS Designer).

I would prefer to use a custom log provider instead of just logging within the SSIS event handlers because I can override the OnPostExecute, etc. but I cannot (can I?) override the specific event handlers like PackageStart, PackageEnd, etc because they are not exposed through DTSEvents.

Any help I would be very grateful for. Thanks.

View 8 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

João

View 8 Replies View Related

Custom Assembly Reading From Sql Server 2005

Mar 29, 2008

I designed dll file to get data from sql server, i am calling the dll from ssrs 2005
in preview , everything is ok but after deploying and put the dll file in C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerin and add this to rssrvpolicy.config

<CodeGroup
class="SqlClientPermission"
version="1"
Name="MySecurityExtensionCodeGroup"
Description="Code Group for Don's test security extension"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:Program FilesMicrosoft SQL ServerMSSQLReporting
ServicesReportServerinMyAssembly.dll"
/>
</CodeGroup>

i still get #Error

i try everything , i don't know what i can do.

pls help

View 1 Replies View Related

SSIS Script Task Reading Variables In Vb.net DTS

Dec 15, 2006

I'm trying this code in a script task inside a foreach loop that gets a file in a foreach file enumerator and maps it to a variable FileName.
In the script task I'm setting the ReadOnlyVariables to @[User::FileName]
: Imports System.IO Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main() Dim fname As String = CType(Dts.Variables("FileName").Value, String) File.Move(fname, fname + ".processed") Dts.TaskResult = Dts.Results.Success End Sub
End Class
But now getting this error:
Error: Failed to lock variable "\xxxxDataf1.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

As you can see the variable does appear to be resolving.

View 1 Replies View Related

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'

thanks,

Guangming

View 2 Replies View Related

.NET Provider For SAP And Variables

Jul 7, 2006

Hi,

I am using .NET Provider for SAP to import data from SAP BW to the SQL-Server 2005.

The OLE-DB source has an sql command like this

"exec Z_SQL_EXPORT @I_WEEK_FROM='200601', @I_WEEK_TO='200602'

I want to make this command more flexible by using a variable strWEEK_FROM and strWEEK_TO.

How can I do this?

Thank you for yout help.

Gerd

View 7 Replies View Related

.NET Provider For SAP And Variables

Nov 22, 2007

I'm using the Microsoft .NET Data Provider for SAP and it works fine.
But I want to do dynamic selects. I think it's not possible within the Dataflow-source to use variables in the sql command line.
So I created a scriptcomponent, but I don't konw how to declare the SAPParameter.
The MS Whitepaper doesn't give an answer.
I tried it with the sqlparameter but it doesn't work

Dim sqlParam As SqlParameter
sqlParam = New SqlParameter("@mandt", SqlDbType.Int)
sqlParam.Value = mandt

Does somebody can help me?

View 3 Replies View Related

Custom Log Provider - Not Recognised.

Feb 15, 2007

Hi All,

I have a tricky one for you...

1. I create a custom log provider on machine A.

2. I deploy the signed assembly to the../../DTS/90/LogProviders directory & the GAC on both machine A & machine B.

3. I create a new package on machine A & go to enable logging for this new custom provider: I find it in the drop-down list of providers, and I can add it successfully.

4. I create a new package on machine B & go to enable logging for this new custom provider: I find it in the drop-down list of providers, but I can't add it. I get the following error:

'...failed to create log provider...the log provider type "_" specified for log provider "_" is not recognized as a valid log provider type. This occurs when an attempt is made to create a log provider for an unknown log provider type. Verify the spelling in the log provider type name (package)."

Any ideas anyone??

Tamim.

View 5 Replies View Related

Create A Custom SSIS Log Provider

Jan 18, 2008

I created a SSIS log provider derived from LogProviderBase, it's appear in my VS2005 and work fine, the only one problem is, there is not a dropdown list of connections in the SSIS Log Configuration column, how can I add it?

http://technet.microsoft.com/en-us/library/ms403361.aspx

thanks

View 6 Replies View Related

Custom Log Provider - Notification Services

Jan 20, 2006

Hi...

I am trying to do custom log provider that will post events to Notification Services...The idea is that if I could post the "SSIS events" to Notification Services, with a Custom Delivery Channel in NS, I would be able to do NS Subscriptions with real time monitoring of SSIS packages..

I have tried to use the sample custom log provider code in MSDN. The problem is after I added the custom log provider dll to the C:Program FilesMicrosoft SQL Server90DTSLogProviders folder and install to global assembly using gacutil...

The custom log provider is not showing up in the Provider Type under Configure SSIS logs...

View 7 Replies View Related

SqlException Unhandled In Custom Membership Provider??

Dec 14, 2006

can someone help me in this???? this function is throwing exception "sqlException unhandled in user code"."incorrect syntax near ?"

Public Overrides Sub UpdateUser(ByVal us As System.Web.Security.MembershipUser)
Dim conn As New SqlConnection(connStr)
Dim cmd As New SqlCommand("UPDATE users SET firstname = ?, lastname = ?, company= ?, address= ?, state= ? WHERE Username = ?", conn)
Dim u As User = CType(us, User)
cmd.Parameters.Add("@firstname", SqlDbType.VarChar, 50).Value = u.FirstName
cmd.Parameters.Add("@lastname", SqlDbType.VarChar, 50).Value = u.LastName
cmd.Parameters.Add("@company", SqlDbType.VarChar, 50).Value = u.Company
cmd.Parameters.Add("@address", SqlDbType.NVarChar, 200).Value = u.Address
cmd.Parameters.Add("@state", SqlDbType.VarChar, 50).Value = u.State
cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 64).Value = u.UserName
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch e As SqlException
Throw e
'Throw New Exception("Some Error occured during updation...")
Finally
conn.Close()
End Try
End Sub

View 11 Replies View Related

Custom Role Provider Everthing Working Except Roles???

Apr 25, 2007

Hello Everyone,
I am trying to use a custom role provider, the main purpose so that I don't have to use a database file. My web.config file is posted below. My problem is that I think I have followed all the steps to create a custom role provider using both articles on MSDN and some written by Scott Gu. After Modifying my web.config file I went to the asp.net configuration to test the connection. Users are created in the remote database but roles are being picked up and created in the App_Data folder. Can anyone give me a hint at what I am doing wrong? thanks in advance.
<?xml version="1.0"?>

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=10.10.10.10;Database=MyDataBase;Persist Security Info=True;User ID=user;Password=password" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>

<roleManager enabled="true"/>
<membership defaultProvider="CustomSQLRoleManager">
<providers>
<clear/>
<add name="CustomSQLRoleManager"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="MyConnectionString"
applicationName="MyAppllicationName"
minRequiredPasswordLength="6"
minRequiredNonalphanumericCharacters="0"
requiresQuestionAndAnswer="false"
requiresUniqueEmail="true"/>
</providers>
</membership>
<compilation debug="true">
<assemblies>
<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies></compilation>

<authentication mode="Forms"/>

</system.web>
</configuration>

View 2 Replies View Related

Proper Way To Rename ASPNETDB.MDF? Custom Provider The Solution?

Jan 28, 2006

ASP.NET 2.0....
What is the proper way to rename the ASPNETDB.MDF to another name, such as TEST_ASPNETDB.MDF?
I know you can rename the db in the IDE, BUT, there are other places you need to change so that ASPNETDB.MDF isn't recreated. Do I have to make a custom SQLExpress provider or something to that effect?
Any examples? Thanks...

View 2 Replies View Related

Using Connection Managers Inside Custom Log Provider Failing!

Apr 3, 2006

Hi,

I'm trying to use an OLE DB connection manager from inside a custom log provider, but can't get the code to work - I get an exception "A first chance exception of type 'System.InvalidCastException' occurred in SSEALogProvider.dll" on the last line of the code below.

I know the connection is OLE DB from looking at .CreationName.

I've tried both with & without ToConnectionManager90(), but appearently not in the right way, both cases gives the same exception.

Any tips on what I'm missing? All the examples I've seen are either creating (instead of using) connection managers, or are file based connection managers. Any and all help much appreciated!

Thanks/Kristian

if (this.ConfigString.Length == 0 || connections.Contains(ConfigString) == false ||

connections[ConfigString].CreationName != "OLEDB")

{

infoEvents.FireError(0, "SSEALogProvider", "The ConnectionManager " + ConfigString + " specified in the ConfigString property cannot be found in the collection, or is of the wrong type (expected CreationName='OLEDB'.)", "", 0);

return Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure;

}

else

[...]

public override void OpenLog()

{

if (!connections.Contains(this.ConfigString))

throw new Exception("The ConnectionManager " + this.ConfigString + " does not exist in the Connections collection.");

ConnectionManager connectionManager = connections[ConfigString];

IDTSConnectionManager90 connectionManager90 = DtsConvert.ToConnectionManager90(connectionManager);

object connObj = connectionManager90.AcquireConnection(null);

//ConnectionManagerOleDb connectionManagerOleDb = (ConnectionManagerOleDb)(connectionManager.InnerObject);

//object connObj = connectionManagerOleDb.AcquireConnection(null);

if (connObj == null)

{

events.FireError(0, "SSEALogProvider", "Could not connect to the log database with connection string " + ConfigString + ".", "", 0);

return;

}

// Exception on next line: "A first chance exception of type 'System.InvalidCastException' occurred in SSEALogProvider.dll"

OleDbConnection oledbConnection = (OleDbConnection)connObj;

View 3 Replies View Related

How To Reach Report Variables From Custom Code

Jun 19, 2007

Hello,

I'd like to use Globals!ReportName and User!Language in custom code like this:

Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?

Thanks!

Werner

View 8 Replies View Related

Reporting Services :: How To Set Custom Variables In Report Builder 3.0

Oct 19, 2011

i am using Microsoft Sql Server 2008 Report buider 3.0. and i generated report and that report how to use custom variables.

View 2 Replies View Related

Problem Setting Package Variables In SSIS Package

Sep 8, 2006

Hi,

I am making use of the DtUtil tool to deploy my package to SQL Server.
Following is my configuration:
32-bit machine and 32-bit named instance of Yukon.

I have some package variables which need to be set in the code.

Previously I did it as follows:

Set the package variables in the code. For example:

pkgFile.Variables["User::DestinationServerName"].Value = <myvalue>

Deploy the package as follows:

applnObj.SaveToSqlServer(pkgFile, null,
destinationServer, null, null);

Here the package was successfully deployed and when i open those packages using BIDS, I am able to see that the variables are set to the values as doen in teh code.

Because of oen problem I am not using SaveToSQLServer method. So I switched to DTUtil tool.
Now I am doing it this way:

Set the package variables as before.
Deploy the package to SQL Server using DTUtil tool.

Now is the problem:
The package is successfully deployed. But the variables are not set to the value that I have specified in the code.

I also tried DTexec utility to set the package variable. Even that does n't work.
Can anyone help me out? Is there any alternate method to set package variables?

Thanks,
Sandhya

View 8 Replies View Related

Parent Package Variables And SQL Package Store

Aug 23, 2007

Hello All,
I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.

i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?

Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string

Thanks for your time.

View 13 Replies View Related

SSIS Package : Reading XML Data In VB Script

Oct 15, 2007

Hi Guys,

I have to read a database column in my VB script which basically contains XML data. My requirement is to read that column and then retrieve couple of nodes within that dataset and then based on those values ....i have to execute a procedure....

Can anyone please let me know how to traverse a XML document in VB Script

Thanks......

View 2 Replies View Related

Package Goes Into Loop While Reading Paradox File

Apr 30, 2007

I have an SSIS package which is suppose to read this db file and insert it into the SQL 2005 table. But when I execute this package it goes into a loop, reading the file over and over again. The package fails after inserting some 10 million rows ( actual rows are not more then 270,000) giving the error msg 'Not enough space on temporary disk'.

On examing the data transfered into SQL there are duplicate rows.

I also used the import export wizard (thinking there might be some error in Package code) provided by SQL to transfer the data from .db to SQL but it has the same result(goes in a loop).

I would appreciate any help in this problem. Let me know if you have any other questions.

Thanks

View 3 Replies View Related

Reading A .dtsx File's Package Configuration In VB.NET

Jan 10, 2008

So I've seen articles outlining how to execute a package in VB and how to pass variables along to the package, which is great.

But I'm wanting write a VB.NET app that will read the xml in a .dtsx package, looking for the available variables (and datatypes) that need to be passed to the package to configure it properly. Anyone done this? Is it best to use parent package variables? Or another type of configuration?

When looking at the xml of a .dtsx file, I can see a variables section that could be parsed... is there anything letting me know what is required versus optional? How do I know the datatype that I need to pass? Any help would be appreciated...

View 22 Replies View Related

ASP.NET Reading Package List From Remote SSIS Server

Feb 14, 2008

Hi everyone!

I have a question regarding ASP.NET 2.0 reading available packages (calling "GetDtsServerPackageInfos" on the "Application"-Object which returns "Microsoft.SqlServer.Dts.Runtime.PackageInfos") from a remote SSIS server (not running them). It seems to me that I am experiencing either a security/delegation issue or that I am trying to do something that is not possible...

The setup is the following.

I have two machines, A and B. A runs IIS 6/ASP.NET 2.0 and B runs SQL Server and SSIS. They belong to the same domain. IIS runs the application pool with the webapplication in question under the "Network Service" account and A is trusted for delegation to B. At the moment its trusted when using Kerberos and for MSSQLSrvc and rpcss; but I tried "all" and many more as well.

I configured IE to use Windows Authentication and set the URL to belog to the Intranet (to enable kerberos authentication).

My ASP.NET webapplication now tries to read (in the sense explained above) the available packages on the SSIS machine (B). The packages are stored on the filesystem, not in mdsb. But I feel that this does not matter, since ASP.NET is accessing them through "the SSIS service" which can access packages in msdb or/and on the filesystem?

What happens now is that

a) everything works, when I fire up IE on A and access the webapplication. The user I have on A is a local admin.
b) I get "E_ACCESSDENIED" when I start IE as regular User on A
c) I get "E_ACCESSDENIED" when I access the webapplication from any other machine than A (IE6 or IE7)

My main question is: is it possible to do what I want to do? The other question is, why does everything work when I access the webapplication from A, but not from any other machine? The second thing makes me think it could be a delegation issue (double hop and SSIS don't seem to go togethe too well). But still, I could not find any resource telling me that this is impossible And frankly, I do not fully understand delegation and/or Kerberos.

I would greatly appreciate if anybody had a pointer or something for me.
Regards
scherand

Btw: Thanks to Caddre for his (her?) first help and pointing me to this forum.

View 20 Replies View Related