What's the second number for in the SSAS query result?

If you run the following MDX:

SELECT [Customer].[Customer].[Aaron Adams] ON 0
FROM [Adventure Works]

You will get the result:

Aaron Adams
4

What's the number '4' for?

It's not the key for Aaron Adams, it does not mean 4 Aaron Adams either. It indicates that Aaron Adams has 4 entries in the FactInternetSales table in the cube.

Run the following queries in SSMS:

-- Return the key 28866 for Aaron Adams
SELECT *
FROM [dbo].[DimCustomer]
WHERE FirstName = 'Aaron' AND LastName = 'Adams'

/*
Return 4 rows for Aaron Adams in the FactInternetSales table. He ordered 4 different products on a single order - order number: SO56918

ProductKey OrderDateKey CustomerKey SalesOrderNumber SalesOrderLineNumber
529 20071030 28866 SO56918 1
539 20071030 28866 SO56918 2
214 20071030 28866 SO56918 3
489 20071030 28866 SO56918 4
*/

SELECT ProductKey, OrderDateKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber
FROM [dbo].[FactInternetSales]
WHERE CustomerKey=28866


Solved - MdxScript(Adventure Works) (6, 11) Parser: The syntax for ';' is incorrect.

Problem:

When I try to deploy a project with a KPI defined, I get an error: MdxScript(Adventure Works) (6, 11) Parser: The syntax for ';' is incorrect.

Initially, I thought maybe there is a syntax error on the defined KPI value, goal, status, or trend. 


Go to the Calculation tab, I see the double ';;'. That's why. Delete the extra one.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;;

Solved - Login failed for user 'NT SERVICE\SQLAgent$SQL2012 when executing an SSIS job

Problem:

I have created an SSIS job in SQL Server 2012. Initially it runs fine with the SQL Server Agent account, which is NT SERVICE\SQLAgent$SQL2012.

For the package, I have two connection managers connecting to AdventureWorks2008 and AdventureWorksDW2008, respectively, using the Windows Authentication.

The job fails:
===================================================
Executed as user: NT Service\SQLAgent$SQL2012.
Code: 0xC0202009    
Source: Dimcustomer Connection manager "AdventureWorks2008"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred.
Error code: 0x80040E4D.  An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D Description: "Login failed for user 'NT SERVICE\SQLAgent$SQL2012'.
End Error
........................
error code: 0xC0202009  Description: "SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D. ................

The AcquireConnection method call to the connection manager "AdventureWorksDW2008" failed with error code 0xC0202009.
..................
The package execution failed.  The step failed.
==================================================

Reason:

The SQL Server 2012 Agent Service Account,  which is the virtual account - NT SERVICE\SQLAgent$SQL2012, does not have permission to connect the AdventureWorks2008 and the AdventureWorksDW2008 databases.

Solution:

Create a credential from the windows login account (the one connects to the AdventureWorks2008 and the AdventureWorksDW2008 databases), then create a proxy account from the credential, and run the package under the proxy account.

Solved - There was an exception while loading Script Task from XML: Syste m.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.VisualStu dio.Tools.Applications.Core

Problem Scenario:

I have used DTExecUI configuring and executing the package, it works fine. But when I copy the command, and execute the package with DTExec, I get the following error:

=========================
......
Error: 2015-05-13 08:26:27.53
   Code: 0x00000003
   Source: Script Task Script Task
   Description: There was an exception while loading Script Task from XML: Syste
m.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.VisualStu
dio.Tools.Applications.Core, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b
03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file spe
cified.
File name: 'Microsoft.VisualStudio.Tools.Applications.Core, Version=10.0.0.0, Cu
lture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
   at Microsoft.SqlServer.IntegrationServices.VSTA.VstaHelper.<CleanUp>b__1a()
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.DisposeVstaHelper()
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.MigrateVSTADenaliScrip
tProject(XmlElement elemProj, IDTSInfoEvents events)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement
 elemProj, IDTSInfoEvents events)
.......
=========================

Solution:
Use the SQL Server 2012 Installation Disk, go to \redist\VSTA\runtime\x64, find and run VSTA_RT30.msi


Solved - Storing or modifying packages in SQL Server requires the SSIS runtime and database to be the same version. Storing packages in earlier versions is not supported.

Problem:

I have SQL Server 2014, 2012, 2008R2, and 2008 installed on the same machine. The Integration Service Catalogs/SSDB are associated with SQL Server 2012.

I have developed some packages in BIDS 2008. When I use the "old" package deployment method to generate the SSISDeploymentManifest file, and choose the SQL Server 2008 instance (i.e., (local)\SQL2008 in my case) to store the packages, I get the message:

Storing or modifying packages in SQL Server requires the SSIS runtime and database to be the same version. Storing packages in earlier versions is not supported.

When I connect to Integration Services using SSMS, the packages are not deployed under MSDB.

Reason: 

Although I developed the packages in BIDS 2008, but there is only one instance of SSIS per machine. The SSIS runtime on my machine is associated with SQL Server 2012.

Solution:

On the target server, use (local)\SQL2012, not (local)\SQL2008, not (local) - the default instance for SQL Server 2014. Now the SSIS runtime and the storing database are the same version, both are SQL Server 2012.



Now I can see the packages developed in BIDS 2008 are deployment to the MSDB of SQL Server 2012. All of the them are aligned.


Upgrading the package is not required in this approach. The version of the package is still 3, meaning BIDS 2008 (You open the package with Notepad or XML, and then search for PackageFormat. Version 2 is 2005, 3 is 2008, 6 is 2012, 8 is 2014).

So the lesson learned: using the single and compatible of SQL Server and Visual Studio only on a machine. The design of the single instance of SSIS per machine sometimes makes you "Ouch!'

When to use the fast-load option for data access mode in OLE DB destination?

Generally the fast load option is the preferred method from the performance stand point. But this option commits rows in batches. If you want to capture only the offending rows when an error occurs in an OLE DB destination, then you should not use this option. 

By default, any constraint failure at the destination causes the entire batch of rows defined by FastLoadMaxInsertCommitSize to fail. The FastLoadMaxInsertCommitSize property of the OLE DB Destination is used to determine how many rows should be committed as a single transaction when using the FastLoad option. In SQL Server Integration Services (SSIS) 2005 the default value for this property was zero which meant “regardless of the number of rows, commit all of them under a single transaction”. In SSIS 2008 that default value changed to 2147483647 (see below), meaning that SSIS would commit rows after approximately 2.15 billion.




Example:

I have a constraint on the dbo.Sales_Summary table to restrict the ProductLine to R, M, or T only. But some rows have 'S'. When I use the Fast Option, all of the 2894 rows in the batches are re-directed to the error destination, even for those with the valid productline code 'M', 'R' or 'T'.



For the same package, if I do not use the Fast Load option (i.e., use the "Table or view" option), the 421 invalid rows with 'S' as the productline are routed to the error destination, whereas the valid ones are inserted into the destination table