Showing posts with label generate. Show all posts
Showing posts with label generate. Show all posts

Thursday, March 8, 2012

"login failed for user" message when executing a copy of a working package

I notice when I copy an SSIS package 'A' to a new package 'B', the new package 'B' will generate a "login failed for user" message in the data flow components. To copy I "save copy of Package 'A' as."

Some config info:

    Package ProtectionLevel = EncryptSensitiveWithPassword

    Connections are Data Sources

    Connection strings with password are stored using PackageConfigurations to an SQLServer table. I've verified Package 'A' is in fact using the config table (e.g. it is not using a password or user stored in the package)

    Data connectios are all SQLServer Native OLE DB Client

    The account is an SQLServer account (not integrated security)

The original Package 'A' works flawlessly and I get success when I test the connections in Package 'B'.

But executing package 'B' I get: The error message I get is: [Connection manager "MyConnection"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'dwuser'.".

Does anyone know why this would occur and/or how to work around it? I saw another thread where a potential workaround is to create a new data flow task and copy all the data flow components to that task. That won't work well for us because the data flow is moderately complex and when you copy and paste it, SSIS completely re-orders the layout.

This is a typical data warehouse ETL setup where there is a master package that executes child packages (e.g. 'A', and 'B' mentioned above) that each perform the ETL for a specific dimension or fact table.

Thanks for any help,

Lee Cascio

Some more info and revelation.

Both child packages will run fine if I execute them independently (outside the parent package that calls them). The problem seems to occur when I try to execute them from a parent package. The OLE Db Connections will then fail on validation on both packages with the "login failed" message listed above in addition to the following: [MyTable [1]] Error: The AcquireConnection method call to the connection manager "MyDataSourceConnection" failed with error code 0xC0202009.

An interesting observation is if I disable Package 'B' in the parent package, Package 'A' will execute without flaw with the parent package. If I disable Package 'A' and enable Package 'B', Package 'B' will still have the problem. Another note, the parent package also uses the data source connections.

It appears we have some kind of problem with multiple packages using the same data source. Does anyone know if there are any limitations to this or if it requires special transaction settings?

Thanks,

Lee Cascio

|||I resolved this issue by switching to XML configurations instead of SQL Server table configurations. I'm still not sure of the exact cause but would have been a problem with credentials getting to the SQL Server Table for the configurations (which include connection strings).

"Invalid authorization specification"

I use SQL Server 2005 Analysis Services Deployment Wizard to generate an xmla deployment script. I get the following error when I try to run it:

<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
<Error ErrorCode="3238395904" Description="OLE DB error: OLE DB or ODBC error: Invalid authorization specification; 28000." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3239182436" Description="Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Max Min Sales DM 1', Name of 'Max Min Sales DM 1'." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Product', Name of 'Product' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Errors in the OLAP storage engine: An error occurred while the 'Brand Name' attribute of the 'Product' dimension from the 'MaxMinSalesDM' database was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
</Messages>

In the script, I see the following data sources:

<DataSources>
<DataSource xsi:type="RelationalDataSource">
<ID>Max Min Sales DM</ID>
<Name>Max Min Sales DM</Name>
<ConnectionString>Provider=SQLNCLI.1;Data Source=localhost;Persist Security Info=True;Password=;User ID=;Initial Catalog=MaxMinSalesDM;DataTypeCompatibility=80</ConnectionString>
<ImpersonationInfo>
<ImpersonationMode>Default</ImpersonationMode>
</ImpersonationInfo>
<Timeout>PT0S</Timeout>
</DataSource>
<DataSource xsi:type="RelationalDataSource">
<ID>Max Min Sales DM 1</ID>
<Name>Max Min Sales DM 1</Name>
<ConnectionString>Provider=SQLNCLI.1;Data Source=localhost;Persist Security Info=True;Password=;User ID=;Initial Catalog=MaxMinSalesDM;DataTypeCompatibility=80</ConnectionString>
<ImpersonationInfo>
<ImpersonationMode>Default</ImpersonationMode>
</ImpersonationInfo>
<Timeout>PT0S</Timeout>
</DataSource>
</DataSources>

I am surprised the connection strings don't contain any username/password info since the data source objects have user/password info saved with them when I access the project through Visual Studio. I try manually adding in User ID and Password, rerun, and get a different error:

<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
<Error ErrorCode="3238395904" Description="OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01; Shared Memory Provider: I/O Error detected in read/write operation [4]. ; 08S01; Login failed for user 'MinMaxUser'.; 28000." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3239182436" Description="Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Max Min Sales DM 1', Name of 'Max Min Sales DM 1'." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Product', Name of 'Product' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Errors in the OLAP storage engine: An error occurred while the 'Brand Name' attribute of the 'Product' dimension from the 'MaxMinSalesDM' database was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
</Messages>
</root>

Any ideas? Thanks in advance!

This is quite common situation you are having: Analysis Server doesnt have permission to access SQL Server relational database.
It takes little while to troubleshoot connectivity problems. You need to make sure you set correctly security credentials required for Anlaysis Server to access relational database.

For one, try and make sure the account Analysis Server runs under can access SQL Server using windows authentication.

There is quite a bit information out there on how to setup connectivity and connection security.

HTH.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 6, 2012

"Group" property is not displayed.

I've a simple question.
I was trying to generate a List Report. I created a List Object on the pane and placed a database field
something like "=Fields!ProductName.Value".

I want to display the report group by the database field selected above. and subsequntly use this feature to place some more fields.

In the Properties window (select the database field object not the list object and use F4 key to display properties window), when I try to locate Grouping property I could not locate it.

if any body has worked out this already, please let me know how to locate that property?

--Thanks
Ravs

as far as i know, you got to click on the list and choose grouping and not on the database field object and you wont find grouping option from there..

and by clicking on the list window and grouping by the field should work...

I am not sure if this is what you are looking for?

Correct me if i am wrong...

Thanks,
Sankar

|||hey, that works. thanks Sankar.

Thursday, February 16, 2012

<asp:SqlDataSource> generate SQL for GridView?

Hi guys.

Im using a gridview to show some training data on my website which is populated by:

<asp:SqlDataSource ID="ARENATraining" runat="server" ConnectionString="<%$ ConnectionStrings:ARConnection %>"
SelectCommand="SELECT [EventType], [CourseLink], [EventDate], [EventTitle], [EventLocation], [EventWebsite] FROM [qry_FutureEvents] WHERE ([EventPrivate] = @.EventPrivate) ORDER BY [EventDate]">
<SelectParameters>
<asp:Parameter DefaultValue="FALSE" Name="EventPrivate" Type="Boolean" />
</SelectParameters>

So far so good..

However I want to be able to filter the data.

A) Show everything

B) Show individual EventType i.e. Seminar, Training etc etc

Is it possible to generate the Select Command via a function?

(im not using stored procedures for this part of the site, nor really want to at the moment).

Id appreciate any help, otherwise ill be forced to curse and swear and use the good ol repeater and figureing out how to page it!

Cheers guysBig Smile

Well, repeaters work the exact same way as gridviews when it comes to databinding.

In any case, the sqldatasource control does expose its selectcommand property. So really, just programmatically set the property, then call the databind method of the gridview, and you're set.

Another possibility is to generate a SQL statement that uses parameters (like I beleive you're currently doing). Then on the "Selecting" event of the sqldatasource, you can set the parameter like: e.InputParameters["paramName"] = "whatever" (so if your query ends with "where somefield LIKE @.someparam", you can use % to show everything as a wildcard, or specify a parameter.

Another option is to set the parameter declaratively, and bind it to Session or a control or something (when you create the sqldatasource, thats what the parameter part of the wizard is for), but I tend to find that a little annoying to paintain, so I suggest the above methods.

|||

Thanks very much for your help!

I didnt know you could do define SqlDataSource outwith the aspx page.

Incase anyone found this thread trying to do the same thing I found this URL with an example

http://geekswithblogs.net/azamsharp/archive/2005/07/19/47711.aspx

Thanks again Shados!

|||

Sorry guys,

Im having real problems with this still :(

Ive been trawling through google all morning and im not quite getting there.

So far ive came up with

Public Sub AddDataSource()Dim dsAs SqlDataSource =New SqlDataSource() ds.ID ="mySqlSourceControl" Page.Controls.Add(ds) ds.ConnectionString = ConfigurationManager.ConnectionStrings("ARENAConnection").ConnectionString ds.SelectCommand ="SELECT [EventType], [CourseLink], [EventDate], [EventTitle], [EventLocation], [EventWebsite] FROM [qry_FutureEvents] WHERE ([EventPrivate] = @.EventPrivate AND [EventType] = @.EventType) ORDER BY [EventDate]"Dim cpAs Parameter cp =New Parameter("@.EventPrivate", TypeCode.Boolean,"False") cp =New Parameter("@.EventType", TypeCode.String,"Training") ds.SelectParameters.Add(cp)Dim paramAs ControlParameter =New ControlParameterEnd Sub
This leaves me the error:
System.Data.SqlClient.SqlException: Must declare the variable '@.EventPrivate'.
I'm that close i can smell it... 
Have pity someone [:'(]
Cheers 
|||

Sorry for taking so long to reply to you. I just woke up, AND I needed to test this by myself before replying to you. It is actualy much simpler than what you're trying to do.

First, make your SQLDatasource normally (you know, like with the wizard, or manually, etc).

Then all what you have to do is, in the designer, double click on the datasource. This will register the Selecting event. Now, one of the parameters is "e", and it has a member, called "command". This is the readonly property that contains the SqlCommand object. Now, the sqlcommand itself is readonly...however, its methods can still be used!

so you can do this:

e.Command.Parameters.Add(newSqlParameter("@.EventPrivate",false)); //note how it will take objects, so you don't need to specify a type

and you're set. No need to do anything else.

Your issue in your case, is that onload, the grid will try to bind on the datasource, and you didn't set its parameters yet, thus it cannot find them.
If your way had worked, you would have ended up with a datasource that binds twice on every load =P Anyway, so just register the Selecting
event of the datasource: this is called -everytime- the datasource is called. And you can do all the work there, as shown above ^_^

|||

Hi Shados

I really appreciate you taking your time helping me with this, it worked perfectly Big Smile

Sorry i didnt reply sooner, I just moved house and my ISP are being lazy!

It is indeed much simpler!

Thanks again

|||Don't worrie about, most people never even reply once they found their solutions :)