Tag Archives: BI

Issues upgrading an SSIS solution/package from Visual Studio 2012 to VS 2013

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:

1, open existing SSIS Visual Studio 2012 solution in Visual Studio 2013.

The open process will pick up the version difference and suggest an upgrade.

image

 

2, run through the upgrade process.

In this case the upgrades failed for all of the SSIS packages in the solution.

image

3, try to open a failed package and read through the error list.

Opening the package will give the following error:

image

 

The design view window shows the main error and there is a list of other errors in the error window/list

image

Main error:

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

Some other errors in the list:

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

 

 

4, Open the package using the code view to see the underlying xml document.

image

use the view menu or press F7 to open the Code XML view of the SSIS package.

 

5, Locate and move the version tag (PackageFormatVersion) to the top.

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.

 

6, Save and reopen the design view and the package will work as expected.

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.

 

image

Error: Invalid column type from bcp client for colid 1 when writing to view in SSIS

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.

Reproduction

To test we can create a Demo Database with a demo table and a demo view:

1, Create Demo Table:

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

 2, Create View

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

 

3, alter the table to not allow nulls

the DemoString column is updated with NOT NULL

USE [Demo]
GO

ALTER TABLE [dbo].[DemoTable]
    ALTER COLUMN DemoString NCHAR(10) NOT NULL
GO

3, Create a SSIS package that writes to the view

Create a sample SSIS project that will transfer information into the view:

image

Source

image

Destination

image

Destination

Run the package:

image

Error message

image

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.

 

4, Update the view to take the new table definition into account:

Recreate the view, such as with this alter statement.

USE [Demo]
GO

ALTER VIEW [dbo].[DemoView]
AS
SELECT DemoId, DemoString
FROM   dbo.DemoTable

GO

 

5, Rerun the SSIS package and it will run successfully:

image

Successful run

Cube Status – a new open source project for BI in SharePoint

I have released my Cube Status project (http://cubestatus.codeplex.com) to http://www.codeplex.com.

cubestatusscreenshot

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.

Unable to create folder in SharePoint 2013 library

1-aspx_debug_error

This error, “Sorry, something went wrong. The aspx_debug attribute on the page directive is not allowed in this page” occurs when trying to create a folder in some types of document libraries, such as the Report Document Library used for SQL Server Reporting Services report documents in SharePoint 2013.

The error comes from some bad /Upload.aspx files that are used by SharePoint 2013.

identifying the upload file for a Report Document Library gives us this standard file path:

C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\TEMPLATE\FEATURES\ReportsAndDataListTemplates\ReportDocumentLibrary\Upload.aspx

The standard contents:

3-upload_aspxfile

By updating the contents with something that makes more sense we can get the folder creation feature to work again:

workingfilecontents

Do note that all page directives needs to go, and we need to update the UIVersion property from 15 to 4 otherwise we just get new exciting errors.

Continue reading

Lönar sig en investering i Business Intelligence?

Den 17de maj pratar jag om och demonstrerar verksamhetsnyttan i en investering i Microsofts BI plattform under ett seminarie på Microsoft.

In English: On May 17th I will present and demonstrate the business value of an investment in the Microsoft BI platform.

Mer information på Solvers hemsida: http://sj.la/12JLaiH

Mer information på Microsofts CloudFacts-sida: http://sj.la/YTDYfG

Mer information:

Solver demonstrerar verksamhetsnyttig BI med Microsoft Office 2013 och Sharepoint 2013

Fråga: Hur får min personal tillgång till och möjlighet att förstå och analysera både företagets och sina personliga nyckeltal (KPI)
Svar: – Vi visar effektiva sätt att få tillgång till och analysera de viktigaste nyckeltalen genom intranätets BI-portal

Fråga: Som ansvarig för IT har jag tillstyrkt stora investeringar i avtal med Microsoft, hur drar jag nytta av dessa?
Svar: – Vi visar upp olika exempel hur dessa investeringar kan utvecklas till en verksamhetsanpassad informations- och BI-lösning med standardprodukter från Microsoft

Agenda:
– Kort om Solver
– Microsoft BI, en översikt
– Demonstrationer

Seminarieinformation
När? – Fredagen den 17 maj 8.00 – 10.00 (registrering och kaffe 08.00 – 08.30)
Var? – Microsoft, Finlandsgatan 36, Kista
Anmälan? – Maila din anmälan till bjorn.nylander@solver.se eller ring Björn Nylander på 08-24 88 00 för att boka ditt deltagande. O.S.A senast 10 maj. Seminariet är kostnadsfritt men begränsat till 40 deltagare.
Välkommen!