With the most excellent help of Microsoft Excel MVP Frédéric LE GUEN there is now a French language option in the Create Time Dimension App. As expected, it will generate all headings and names in French.
Please visit the following sites for more information on Excel from Frédéric:
Microsoft SQL Server 2012 and 2014 has feature parity for SSIS and from a general/development/deployment point of view there is not a lot of exciting things to write about. A main difference is that for SQL Server 2012 the development environment is based on Visual Studio 2012 and for SQL Server 2014 the development environment is based on Visual Studio 2013.
Even though the feature set is the same the compatibility is limited, as described in this blog post from Microsoft: http://blogs.msdn.com/b/analysisservices/archive/2014/04/03/sql-server-data-tools-business-intelligence-for-visual-studio-2013-ssdt-bi.aspx meaning it is still important to use the right tool and right version for the right version of deployment server.
If you are looking for the SSIS BI tools for SQL Server 2012 they are available here: http://www.microsoft.com/en-us/download/details.aspx?id=36843, and for SQL Server 2014 they are available here: http://www.microsoft.com/en-au/download/details.aspx?id=42313. Remember that the plain SSDT SQL Server data tools are for development of database schemas etc. and not for BI work in SSRS/SSIS/SSAS. for BI work the similarly named SSDTBI tools needs to be installed.
It is possible to develop and run the SSIS packages in Visual Studio 2012 and target a SQL Server 2014 environment but for actual deployment to be able to run it scheduled using DTExec.exe or through the SSIS catalogue the project has to be in Visual Studio 2013/SQL Server 2014 format.
The upgrade process seems straightforward; just open the 2012 solution in Visual Studio 2013 and run though the upgrade wizard. However some times there are issues in the upgrade process. This blog post describes one such scenario where the upgrades failed and error messages indicates all sorts of issues.
It turns out that the code/xml parsing of the existing 2012 packages are making some interesting assumptions and that by simply moving the version tag to the top of the xml document the upgrade will work as expected.
Example walk-through:
The open process will pick up the version difference and suggest an upgrade.
In this case, the upgrades failed for all of the SSIS packages in the solution.
Opening the package will give the following error:
The design view window shows the main error and there is a list of other errors in the error window/list
Error 42 Error loading ‘packagename.dtsx’ : The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails. . C:\path\packagename.dtsx 1 1
Error 41 Error loading packagename.dtsx: Failed to load task “DFT – name”, type “”. The contact information for this task is “Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1″. C:\path\packagename.dtsx 1 1
Error 33 Error loading packagename.dtsx: Element “Package\SCT – Placeholder.EventHandlers[OnPreExecute]\Pre-Initialise Event” does not exist in collection “Executables”. C:\path\packagename.dtsx 1 1
Error 1 Error loading packagename.dtsx: Cannot create a task from XML for task “EST – Set VARIABLE”, type “Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” due to error 0xC001F430 “An error occurred while accessing an internal object. This could indicate a custom extension built for Integration Services 2005 is being used.”. C:\path\packagename.dtsx 1 1
use the view menu or press F7 to open the Code XML view of the SSIS package.
Place the <DTS:Property DTS:Name=”PackageFormatVersion”>6</DTS:Property> on the row after the <DTS:Executable…/> statement
search for the text and remove the existing line that in this case was towards the bottom of the file and place it on the 3rd line.
Once the package is saved the upgrade engine will automatically and properly upgrade the package to the new version and the package will display the new xml structure in the xml view. (SQL Server 2014 and Visual Studio 2013 uses PackageFormatVersion 8 as version definition.
reopening the package in design view will now work as expected.
The following scenario might happen when trying to write to a view after the underlying table has been updated.
The example uses a table that is first created with a column allowing nulls. A view is then created on top of it and then the table is updated to not allow nulls for the column.
Writing to the view using SSIS (with default settings) will fail until the view is refreshed to take the current table definition into account.
This scenario uses SQL Server 2014 and Visual Studio 2013 SSDT BI for SSIS.
To test we can create a Demo Database with a demo table and a demo view:
We create a demo table in a demo database. The table is as simple as possible with an identity column and a default data column allowing nulls
USE [Demo] GO CREATE TABLE [dbo].[DemoTable]( [DemoId] [INT] IDENTITY(1,1) NOT NULL, [DemoString] [NCHAR](10) NULL ) ON [PRIMARY] GO
The view is created on the table when the DemoString column allows nulls
USE [Demo] GO CREATE VIEW [dbo].[DemoView] AS SELECT DemoId, DemoString FROM dbo.DemoTable GO
the DemoString column is updated with NOT NULL
USE [Demo] GO ALTER TABLE [dbo].[DemoTable] ALTER COLUMN DemoString NCHAR(10) NOT NULL GO
Create a sample SSIS project that will transfer information into the view:
Source
Destination
Destination
Run the package:
Error message
Error message
The package will fail to run with the following error messages:
Error message 1 [OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid column type from bcp client for colid 1.".
Error message 2 [OLE DB Destination [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error message 3 [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (2) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Recreate the view, such as with this alter statement.
USE [Demo] GO ALTER VIEW [dbo].[DemoView] AS SELECT DemoId, DemoString FROM dbo.DemoTable GO
Successful run
I’ve been invited to present at the August meeting of the Queensland SQL Server User Group.
I will present on the Analytics8 approach to SSIS and Data Vault Automation and specifically on the meta data requirements for full automation of the creation of an Enterprise Data Warehouse.
With the right combination of modern modelling and automation techniques, there are huge and frequently unrealised opportunities to improve the quality and speed of the enterprise data warehouse solution delivery.
This 30 minute presentation outlines the prerequisites to achieve these ETL automation benefits, and will dive into the capture and use of information that exists in models for automated development in SSIS. During this presentation the necessary steps to develop a full Enterprise Data Warehouse solution ‘from the ground up’ will be explained and shown using a live demonstration of the ETL automation techniques and concepts.
Thanks to Wardy IT for sponsoring the event and Microsoft for hosting us.
Venue: Microsoft Brisbane – Level 28, 400 George Street, Brisbane
Duration: Catering from 5:30PM for a 6:00PM Start (meetings typically finish at 7:30PM)
On: Thursday, 28th August 2014
RSVP: to contact@wardyit.com to help with catering
To Register: https://www.eventbrite.com/e/august-2014-qld-sql-server-user-group-tickets-12542062647
How to solve the error: Load operation failed for query ‘GetAuthenticationInfo’ when deploying a LightSwitch 2012 application to a Windows Server 2008 R2 using IIS.
Scenario: The following error message appears in browser instead of the expected LightSwitch-based web application. The application has been developed using Microsoft LightSwitch in Visual Studio 2012. The application works as expected on the development machine but fails to run in a Microsoft Windows Server 2008 R2 environment even though the LightSwitch server side prerequisites has been installed.
Load operation failed for query ‘GetAuthenticationInfo’. [HttpWebRequest_WebException_RemoteServer]
Arguments: NotFound
Debugging resource strings are unavailable. Often the key and arguments provide sufficient information to diagnose the problem. See http://go.microsoft.com/fwlink/?linkid=106663&Version=5.1.30214.0&File=System.Windows.dll&Key=HttpWebRequest_WebException_RemoteServer
Sample Screenshot:
After some troubleshooting it turns out that it is because the wrong version of WCF Data Services for OData had been installed. The proper version for a Visual Studio 2012 based LightSwitch application is WCF Data Services 5.0 for OData V3 and it is available for download here:
http://www.microsoft.com/en-us/download/details.aspx?id=29306
After installation and a web site restart the application worked as expected.
Today the blog turns 5 years old.
Happy birthday old chap, good sport.
Release 3 of the Create Time Dimension App is now live. This version brings Czech language support and ISO-8601 week numbers.
The update is made on the server so it is available for everyone right away, no update needed on the Excel/Client side.
Big thanks to Petr Havlik (Twitter: https://twitter.com/petrhavlik) for helping with the Czech translation.
Please note that the ISO-8601 week numbers are just one way of expressing week numbers and might not be what you want. The week numbers start on Mondays per definition and does not change when changing the week start day. If you would like to use another week numbering format you can still add a column using the =WEEKNUM(date, week number system) formula in Excel.
When installing a separate 32 bit web application in a SharePoint 2013 Farm the new application might fail to load and give a general 503 Service Unavailable error message. When looking in IIS manager, the Application Pool for the new app would be in the stopped state.
The following error messages might be visible in the Event log…
Event ID 5002 – Error <date and time> WAS 5002 None
Application pool ‘<App Pool Name>’ is being automatically disabled due to a series of failures in the process(es) serving that application pool.
And
Event ID 5139 – Warning <date and time> WAS 5139 None
A listener channel for protocol ‘http’ in worker process ‘<process number>’ serving application pool ‘<App Pool Name>’ reported a listener channel failure. The data field contains the error number.
Event ID 2282 – Error <date and time> IIS-W3SVC-WP 2282 None
The Module DLL ‘C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\isapi\spnativerequestmodule.dll’ could not be loaded due to a configuration problem. The current configuration only supports loading images built for a x86 processor architecture. The data field contains the error number. To learn more about this issue, including how to troubleshooting this kind of processor architecture mismatch error, see http://go.microsoft.com/fwlink/?LinkId=29349.
The Microsoft link for the issue will only redirect you to the www.iis.net web site.
The solution to this issue is to enable 32 bit applications to be loaded together with SharePoint and ask the SharePoint Module to only load in 64 bit mode. This can be done by running the following command, located in the %WINDIR%\System32\inetsrv\ folder:
appcmd.exe set config -section:system.webServer/globalModules /[name=’SPNativeRequestModule’].preCondition:integratedMode,bitness64
More information on configuring IIS can be found here: http://technet.microsoft.com/en-us/library/cc627313.aspx
Hat Tip to my colleague Markus Lind for pointing this out.
I have released my Cube Status project (http://cubestatus.codeplex.com) to http://www.codeplex.com.
This web part for SharePoint provides the possibility to visualize both the current status of a SQL Server Analysis Services (SSAS) cube or tabular model (BISM) and some of its metadata.
This web part can be used in BI portal type pages in SharePoint to show the current status of a cube used for reports and analysis. This means that there is no need to guess the update status or last process time etc. of a cube.
Both the source code and a .wsp solution file for deployment are available for download from the project page: http://cubestatus.codeplex.com
This project is described in more detail here
This project also works quite well together with the SQL Server Agent Job Status project found at http://jobstatus.codeplex.com and with more details here
Please, feel free to suggest updates and new features etc. either here on the blog or on CodePlex.
I have published an update to the Create Time Dimension Application for Excel that includes a new Date Integer Id column (in the format 20130616 for June 16, 2013) and Dutch language support.
The update is made on the server so it is available for everyone right away, no update needed on the Excel/Client side.
Big thanks to Frederik Vandeputte (Web: http://www.vandeputte.org/ Twitter: https://twitter.com/frederikvdp) for suggesting the id column and helping with the Dutch translations.
Screenshot: Updated app UI with the Dutch language choice