How to wire a for loop to output values to a flatfile

Question

Post reply

How to wire a for loop to output values to a flatfile

wsolum

Grasshopper

Points: 23
More actions
January 29, 2015 at 9:35 am

#314188

I'm new to SSIS - I typically just write code to do these types of tasks and it's much quicker for me but I really need to learn this technology.
I want to scan a table which has a row which contains the name of a folder to create and start and end index numbers which represent file names. What I'm doing is probably stupid but it's just a learning project. I want to read the table rows in the outer foreach loop and within a nested for loop write to a batch file a DOS copy statement that copies the PICTnnn.jpg file to the new folder and rename it to start with an index number of 1.
I have the outer loop set up and assign it's rows to an object but within the for loop I don't know how to write data to a flatfile -- what is my datasource? I have variables that contain values but my dataflow wants a data source. How do I tap into my values and write them to the file?
I apologize how dumb this makes me sound. It would be so simple in vba.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Have a look at "Export Column Transformation"

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

wsolum Grasshopper Points: 23 More actions · Answer 2

Okay, I tried that but I still need a column source. I have a for loop that takes the row from the for-each and itterates between start index and stop index. Within this I need to write to a flat file. I put a dataflow task in the for loop and the Export Column transformation within that but it wants an input column and nothing shows up in the configuration for the transformation.

I don't know how to wire it in.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

You can use a "Derived column transformation" to add in an additional column.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

wsolum Grasshopper Points: 23 More actions · Answer 4

I still don't know how to wire in the outer loop data into the inner loop. The inner loop in SSIS requires input columns but I'm not using records, I want to use memory variables as input.

Pseudo code

--OuterLoop

ForEach Row in DataTbl

StartIndex = DataTbl.StartNo

StopIndex = DataTbl.EndNo

SourceIndex = StartIndex

IterationCnt = StopIndex - StartIndex + 1

FullName = DataTbl.FullName

-- Inner Loop / Uses memory variables (no input column source so how to connect this in SSIS?)

for x = 1 to IterationCnt

writeflatfile "COPY \IMG\PICT" + PAD(SourceIndex,4,"0") + ".jpg \target\"+FullName+"\"+FullName+"_"+pad(x,4,"0")+".jpg"

SourceINdex = SourceIndex + 1

next x

Next Row

I'm at that point in the learning curve where it's frustrating and hard to see why they even went to a click and drag component model when code is just a matter of saying what you want.

Thanks for helping -- I know this must seem obvious to you.

Nevyn SSChampion Points: 14210 More actions · Answer 5

Could be missing something but is there a reason you aren't doing this all in one data flow?

Seems to me your outer "loop" is a deconstructed data flow

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

What you can try is to use the table as your source in the data flow (using an OLE DB source).

I do not know the structure of your table, but you said it contains one row with the ranges of the to be imported files.

Suppose it's something like this:

FolderA, FileName, 1, 5

which means you have to import FileName1 to FileName5 from FolderA.

Then you need to write a query thas has the following output:

FolderA, FileName1

FolderA, FileName2

FolderA, FileName3

FolderA, FileName4

FolderA, FileName5

Then you can use your export column.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 7

Koen Verbeeck (1/30/2015)
What you can try is to use the table as your source in the data flow (using an OLE DB source).
I do not know the structure of your table, but you said it contains one row with the ranges of the to be imported files.
Suppose it's something like this:
FolderA, FileName, 1, 5
which means you have to import FileName1 to FileName5 from FolderA.
Then you need to write a query thas has the following output:
FolderA, FileName1
FolderA, FileName2
FolderA, FileName3
FolderA, FileName4
FolderA, FileName5
Then you can use your export column.

+1 on this. Depending on how many rows you may end up with, you could do Data Flow to Execute T-SQL to Data Flow OR you could do a OLEDB Command Transformation task (which is RBAR intensive, so I don't recommend it).

There are several ways you can do this, though. If you really wanted to, you could use a script task to do it all. Or you could use the Execute T-SQL Task to query off the table, stuff the results into a variable, use the variable to populate your FOR EACH container collection.

Without knowing more about your setup, I can't make an argument for one approach over another.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.