Tag Archives: SQL Server

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

Presenting at the Queensland SQL Server User Group

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.

SSIS Automation

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.

More information

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

Free ebook: Introducing Microsoft SQL Server 2012

Ross Mistry and Stacia Misner’s book “Introducing Microsoft SQL Server 2012” is available from Microsoft:

http://blogs.msdn.com/b/microsoft_press/archive/2012/03/15/free-ebook-introducing-microsoft-sql-server-2012.aspx

Stacia Misner has a whole part of the book dedicated to BI.

 

[googleplusauthor]

What is the cost of cloudspamming?

Being of curious nature, I wanted to test if spam traffic would affect cloud services in a typical “pay as you go” scenario.

I ordered an introductory SQL Azure package from Microsoft with a pay as you go pricing plan.

image

By creating a plain, empty, SQL server instance with a random public access name I managed to get a monthly $10 cost in just 2 months without any advertising or mentions anywhere.

It will be very interesting to se how the cloud services will develop in the future in regards to this aspect.

I

How to: Enable larger reports in Reporting Services

When deploying large reports to SharePoint or Reporting Services from BIDS (Visual Studio) something like the following message might appear:

Error    1    System.Web.Services.Protocols.SoapException: There was an exception running the extensions specified in the config file. —> System.Web.HttpException: Maximum request length exceeded.    at System.Web.HttpRequest.GetEntireRawContent()    at System.Web.HttpRequest.get_InputStream()    at System.Web.Services.Protocols.SoapServerProtocol.Initialize()    — End of inner exception stack trace —    at System.Web.Services.Protocols.SoapServerProtocol.Initialize()    at System.Web.Services.Protocols.ServerProtocol.SetContext(Type type, HttpContext context, HttpRequest request, HttpResponse response)    at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)        0    0

This happens when the report is bigger than the maxRequestLength specified in the Reporting Services web.config file. The default is 4 MB so if the Report is bigger than that it will fail to deploy. it will frequently happen if the report contains map reports using embedded esri shape file data…

The solution, apart from making the report smaller, is to increase the maxRequestLength attribute of the httpRuntime Element in the web.config file of the Reporting Services instance. This file might be found in a directory like this for SQL Server 2008 r2: C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServer

Change the element to the following:

<httpRuntime executionTimeout="9000" maxRequestLength = "16384" />

where the 16384 equals 16 MB max sixe for the reports. Change the value to suit your report size needs…

 

more information about the element in this article: http://msdn.microsoft.com/en-us/library/e1f13641.aspx

 

Updated 2012-12-08: The above link is outdated. Here are some alternative resources:

Report and Snapshot Size Limits (SQL Server 2008)

 Modify Reporting Services configuration files [AX 2012]