Data Flow Task Plus - Derived Column Transformation

[Charles Medcoff]

Trying to use this task to work with csv files with different number of  columns - one as two extra columns the other does not.

I don't want the one with the lesser number of columns to throw errors but rather just not populate the missing columns on the target table.

My target table contains non-nullable columns that will not be populated from the file, but rather come from derived columns so I've added a derived column task to the data flow.  The Data Flow Task Plus task seems to require a recordset type of variable to contain data that configured derived columns.  This is call the DerivedConfigurationVariable.

I'm using this sql in an execute sql task to populate the variable:

select

'feedID' as ResultColumn,

NULL as Expression,

3 as DataType,

4 as Length,

0 as Precision,

0 as Scale,

1252 as CodePage

But at runtime I am getting the following error:

Error: 0x5 at Data Flow Task Plus: System.Runtime.InteropServices.COMException (0xC0204019): Exception from HRESULT: 0xC0204019

   at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumn100.SetDataTypeProperties(DataType eDataType, Int32 lLength, Int32 lPrecision, Int32 lScale, Int32 lCodePage)

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicDerivedColumn.Setup()

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicSetup._Setup(Boolean thunk)

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.Execute(IDTSConnections100 connections, IDTSVariableDispenser100 variableDispenser, IDTSComponentEvents100 events, IDTSLogging100 log, Object transaction)

Task failed: Data Flow Task Plus

any ideas? Examples of how to fill this variable?

--chuck

[Ivan Peev]

Hi Chuck,

Did you review the sample package posted here at the bottom? It demonstrates all transformations, including the Derived Column transformation.

I see two issues in the configuration you have provided:

1. You have specified NULL for Expression. This looks invalid.
2. The DataType configuration value you have provided is incorrect. You have to provide it as a string in the form of : DT_WSTR, DT_I4, ...

[Charles Medcoff]

I have also tried an empty string '' for the expression.  I also tried the data types as strings as you have suggested 'DT_I4'.

I'll take a look at the example.  Much appreciated.

[Ivan Peev]

Chuck,

Have you tried to setup Derived Column transformation this way? NULL or empty string for expression will not work. The question is what are you trying to accomplish with this transformation?

[Charles Medcoff]

Using:

SELECT 'feedID' AS ResultColumn, '' AS Expression, 'DT_I4' AS DataType, 0 AS Length, 0 AS [Precision], 0 AS Scale, 0 AS CodePage

UNION ALL

SELECT 'ResponseData' AS ResultColumn, '' AS Expression, 'DT_TEXT' AS DataType, 0 AS Length, 0 AS [Precision], 0 AS Scale, 0 AS CodePage

I am now getting.

Error: 0x5 at Data Flow Task Plus: System.Exception: Specified configuration variable for 'Derived Column 1' component is incorrect.

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicTransformComponent.GetConfiguration(String variable)

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicDerivedColumn.Setup()

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicSetup._Setup(Boolean thunk)

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.Execute(IDTSConnections100 connections, IDTSVariableDispenser100 variableDispenser, IDTSComponentEvents100 events, IDTSLogging100 log, Object transaction)

Task failed: Data Flow Task Plus

I did look at the example but the Load configuration Task in the 'Dynamic Copy Column' container that has the Data Flow Task Plus component, doesn't appear to have any sql to load a configuration but references a variable User::configSql which is initilaized to 'select 1'.  It see its somehow getting set on the foreloop - still stuyding it.

[Ivan Peev]

Chuck,

What did you specify for the config variable? The error below can only appear if the variable doesn't contain: .NET ICollection, ADODB Recordset or ADO.NET DataSet

Review your setup. Also for DT_TEXT config row below it is mandatory to specify CodePage different from 0.

[Charles Medcoff]

Using this in an 'Execute Sql Task"  with the SqlSoureType set to 'Direct Input', placing the result in a variable of type of type object.

SELECT 

'feedID' AS ResultColumn, 

'' AS Expression, 

'DT_I4' AS DataType, 

0 AS Length, 

0 AS [Precision], 

0 AS Scale, 

1252 AS CodePage

UNION ALL

SELECT 

'ResponseData' AS ResultColumn, 

'' AS Expression, 

'DT_TEXT' AS DataType, 

0 AS Length, 

0 AS [Precision], 

0 AS Scale, 

1252 AS CodePage

[Charles Medcoff]

Oh, I forgot to add the error is now:

Error: 0x5 at Data Flow Task Plus: System.Runtime.InteropServices.COMException (0xC0204019): Exception from HRESULT: 0xC0204019

   at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumn100.SetDataTypeProperties(DataType eDataType, Int32 lLength, Int32 lPrecision, Int32 lScale, Int32 lCodePage)

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicDerivedColumn.Setup()

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicSetup._Setup(Boolean thunk)

   at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.Execute(IDTSConnections100 connections, IDTSVariableDispenser100 variableDispenser, IDTSComponentEvents100 events, IDTSLogging100 log, Object transaction)

Task failed: Data Flow Task Plus

[Ivan Peev]

Chuck,

What is the connection type ?

[Ivan Peev]

Chuck,

This error may happen if you are providing invalid result column parameters. Review your config parameters.

[Charles Medcoff]

The source is a flat file source, the target the OLE DB connection.  Is that what you are asking?

--
You received this message because you are subscribed to a topic in the Google Groups "COZYROC" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cozyroc/kQzOdL8dmUk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to cozyroc+u...@googlegroups.com.
To post to this group, send email to coz...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/cozyroc/926100dc-158c-44ac-9b0f-174515d8e769%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Ivan Peev]

Hi Charles,

What is the connection type you use in the Execute SQL Task ?

To unsubscribe from this group and all its topics, send an email to cozyroc+unsubscribe@googlegroups.com.

To post to this group, send email to coz...@googlegroups.com.