Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Integration Services

Getting Flatfile col data into a variable

I have a flat file with several columns and maybe half a dozen rows
I want the value of the first column of the last row captured in a package variable for other processing.

In the old DTS I ran the data through a transformation from the flat file to a dummy table in tempdb and captured the value for that field via the Active X code inside the transform:

DTSGlobalVariables("DistrictVersion").Value = DTSSource("Col001")

by the time the transform was done I had the last value of 'DTSSource("Col001")' stored in my package variable.

I am stumbling round trying to find an equivalant in SSIS

Vanna Can I Buy a Clue?

[641 byte] By [PaulPisarek] at [2008-2-7]
«« Missing Text in MessageBox when called from DLL
»» Unhooking Parent events
# 1
Paul,
The best way I can think of doing this is to read the file into a variable of type "Object" using a data-flow with a recordset destination.

Once the recordset is in a variable you can shred it using a Foreach loop using a technique similar to what is described here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/03/27/9722.aspx

Or you could shred it using a script task as described here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx
Regards
Jamie

JamieThomson at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 2
Thanks Jamie,
the FOR Each shredder was just what I needed. And many thanks to Kirk for all the direct and indirect input he has had on this solution.
PaulPisarek at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
SQL Server
SQL Server Integration Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified