Bug with Renaming Connection

I used the wizard to create a package that loads data from Access into SQL Server 2005. I then saved the package to the File System and added to my project.

I had two connections, one for SQL and one for Access. When I rename either one, the source or destination tasks in the Data Flow task reset to the other connection and/or I get an error that the old connection ID cannot be found.

I solved by opening the dtsx in Notepad and doing a global find/replace for the connection ID's. This is a real hassle.

Are you right-clicking on the connection manager and selecting rename? Or how are you renaming them?|||

That was how I tried to rename, by right-clicking. Also tried within the Properties and in the Package Explorer. Same thing.

|||

Do you want to know another bug. In the Import Data wizard that creates the package, I checked the Source box in the upper left which selected all the tables. I then went into Edit Mappings and checked to Delete Rows in Destination Tables.

However, when I execute the package, it doesn't delete the rows and instead appends the data. It might be because I am importing a lot of tables, because when I tried one table only, it worked. I believe it calls sp_executesql to execute the SQL statement and maybe the sql statement string is too long. Unfortunately, it doesn't warn that anything is incorrect, so one doesn't discover this until after testing. Don't mind bugs, but a bug like this without any kind of warning message is kind of dangerous.

|||

When building a package from the designer, renaming connections is fine, because actually the task stores the connection ID not the name.

I did a quick test with a Wizard built package and it varies, for example a snippet of the Exec SQL Task <SQLTask:SqlTaskData SQLTask:Connection="{5545ACCD-6E14-45EF-9112-2AC292C30609}" shows the use of the ID, so renames should keep working just fine, except that other stuff is doing it wrong.

For example an OLE-DB destination shows <connection id="48" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="DestinationConnectionOLEDB"/></connections> which uses the name.

If I then open the package, and open the UI and close it again for the OLE-DB component, the XMl is changed to this <connection id="48" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{5545ACCD-6E14-45EF-9112-2AC292C30609}"/></connections> which clearly will survive a rename.

So why not log it at http://connect.microsoft.com

By the way, stop using notepad if you have added the package to a project. Just right click the package in the Solution Explore and select View Code. You get a much nicer text editor with better find and replace, and it keeps it all nicely liked to the designer window for the package as well. Much easier for when you do want to hack package XML directly.

Browse Cube

Just a question please,

When i browse(query) the cube in analysis services how can i save those queries?Can they be saved anyware?

(By browse i mean explore,drill down data in the Cube Browser of Analysis services 2005.)

Help please !!!

No, you cannot save queries done through the cube browser in the BI Development Studio (BIDS).

You could use Profiler to capture the MDX queries that are sent, but you would then have to use a different tool (like SSMS or Reporting Services) to run those queries. BIDS is not really meant to be a reporting tool. You will need to look at a different tool to do this, Excel pivot tables are probably one of the cheaper, more accessible solutions.

|||

You can capture the queries using SQL Server Profiler:

http://msdn2.microsoft.com/en-us/library/ms174779.aspx

>>

SQL Server 2005 Books Online

Introduction to Monitoring Analysis Services with SQL Server Profiler

You can use SQL Server Profiler to monitor events generated by an instance of Microsoft . By using SQL Server Profiler, you can do the following:

Monitor the performance of an instance of Analysis Services.

Debug Multidimensional Expressions (MDX) statements.

Identify MDX statements that run slowly.

Test MDX statements in the development phase of a project by stepping through statements to confirm that the code works as expected.

Troubleshoot problems in Analysis Services by capturing events on a production system and replaying them on a test system. This approach is useful for testing or debugging purposes and lets users continue to use the production system without interference.

Audit and review activity that occurred on an instance of Analysis Services. A security administrator can review any one of the audited events. This includes the success or failure of a login try and the success or failure of permissions in accessing statements and objects.