0

I am creating an SSIS package that will write in the header of a text file

STARTDATE|ENDDATE|ROWCOUNT

followed by the results of a stored procedure. ROWCOUNT is the number of rows in the data set from the stored procedure. The data set is written to the body of the flat file.

My package is successful. It has three data flow tasks:
DetermineRowCount, WriteHeader, and WriteData.

DetermineRowCount and WriteData both hit a copy of the same OLE DB Source - Data item.

DetermineRowCount data flow is OLE DB Source - Data ---> Row Count where the RowCount variable is set.

I would like to eliminate one copy of the OLE DB Source - Data.

Is there a way to obtain the RowCount and the data, write the header, then write the body, without calling the stored procedure twice?

unholymackerel
  • 89
  • 16

2 Answers2

1

The short answer to the question "Is there a way to obtain the RowCount and the data, write the header, then write the body, without calling the stored procedure twice?" is, alas, "No." At least, not in a single data flow.

However, it is possible to eliminate the second call to the stored procedure. Add a Row Count transformation to the original data flow and save the count to an appropriately named variable. Then add a Script Component to the package flow to prepend the desired header row to the text file.

Edmund Schweppe
  • 4,992
  • 1
  • 20
  • 26
-1

You can write the header and the body in the same data flow. Just put the RowCount component in there, right after the source component.

Metaphor
  • 6,157
  • 10
  • 54
  • 77
  • Am I not locked in on DataSource-->FlatFile? I think the columns in the DataSource must map to the FlatFile columns. I don't know how to write a header AND data to the flatfile in one data flow. – unholymackerel Aug 19 '13 at 14:32
  • You can determine the count the number of rows that pass through from your Source to Destination but the count won't be available until after the data flow's PostExecute event has fired. – billinkc Aug 19 '13 at 14:34
  • billinkc - is it possible to do what Metaphor suggested? – unholymackerel Aug 19 '13 at 14:38
  • MSDN is pretty specific: [the value of the variable is not updated in time to use the updated value in the data flow that contains the Row Count transformation](http://msdn.microsoft.com/en-us/library/ms141136(v=sql.110).aspx/css). – Edmund Schweppe Aug 19 '13 at 21:29