Database Reference
In-Depth Information
44,Emma Grace,15
55,Riley Cooper,16
You can read header rows a few different ways. In this solution, we utilize one Flat
File connection manager and one data flow to parse the header row of the data. We rely
heavily on Script component logic for parsing and buffering operations.
Begin by creating a new SSIS package. I named mine
ParseFileHead-
er.dtsx
.
Add a Data Flow task and open the Data Flow Task Editor. Add a Flat File source
adapter and open its editor. Use the New button to create a new Flat File connection
manager aimed at
MyFileHeaderSource.csv
. Uncheck the Column Names in the
First Data Row checkbox. Be sure to click the Advanced page of the Connection Man-
ager Editor and change the names of
Column 0
and
Column 1
to
ID
and
Name
, re-
spectively.
Close the Connection Manager and Source Adapter Editors and drag a Script com-
ponent onto the data flow canvas. When prompted, select Transformation as the use of
this Script component. Open the Script Component Editor and change the
Name
prop-
erty to
scr Parse Header and Data
. Click the Input Columns page and select
both columns (
ID
and
Name
). Click on the Inputs and Outputs page. Rename Output 0
to
Header
and change the
SynchronousInputID
property to
None
. Expand the
Header output and click the Output Columns virtual folder. Click the Add Column but-
ton, name it
ExtractDateTime
, and change the data type to
database timestamp
[DT_DBTIMESTAMP]
. Click the Add Column button again, name this new column
RowCount
, and leave the data type set to the default (four-byte signed integer
[DT_I4]).
Click the Add Output button and name this new output
Data
. Expand the output
virtual folder and select the Output Columns virtual folder. As you did for the header
output, create two columns with the following properties:
•
ID, four-byte signed integer [DT_I4]
•
Name, string [DT_STR]
Return to the Script page and set the
ScriptLanguage
property to
Microsoft
Visual Basic 2012
. Click the Edit Script button. When the editor opens, add a
variable declaration at the top of the class (see
Listing 7-4
)
.
Listing 7-4
.
Adding iRowNum Integer Variable
Search WWH ::
Custom Search