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:
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.
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:
Successful run
