SSIS: Variable as "NEW" derived column

0
votes

I am trying to write the SQL task results in a flat file. I have a SQL task, followed by foreach loop that parses the object results to variables. Inside the foreach I have a data flow.

Inside the dataflow I have a Derived Column transformation editor, where I am trying to use the variables as columns. This is because I want to write the column in a flat file. However the Derived column keeps complaining about not having any INPUT columns (and writing 0 rows to flatfile) and I do not know why.

These are the instructions I am trying to follow: Using Variable as expression in Derived column transformation SSIS

1
visual-studiossiscontrol-flowdataflow
Is there anything prior to this derived column? It looks like the "Columns" is empty which explains your error. If you need a dummy input then use a one line text file as input. This would then get around this problem - MiguelH
You are right, I needed a dummy column. - user1054844

1 Answers

1
votes

Derived Column transformation is a part of Data Flow. Data Flow means that you have a set of rows with columns originated from some Data Flow Source, undergoing DFT transformations like Derived Column and then passing rows to Data Flow Destination. Data Flow Transformation needs to have input and output.
In your case - create a OLE DB Source with some dummy query like `select 0 as dummy' and direct this data flow to your Derived Column. Later you can drop this dummy column.