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.
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
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!'
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
Subscribe to:
Posts
(
Atom
)