Need help with SSIS

[poffle]

Hi guys,

Im brand brand new to SSIS, been working with it the past 3 days.
I need help with the following:

I have multiple text files that need to be imported into a table (oracle) along with the file names.

Table structure:
FileName string
Text CLOB

So far what i've done is for importing the text files is:
Set up Foreach Loop Container
Inside the foreach is my FlatFile Source + Ole DB Source. I have the 2 connection managers. -- This works lekker 100%, imports no probs.

FileName what i've done is added an expression (under collection) within the foreach -
Property = FileNameRetrieval, Value = User:Filename.
I set up an Execute SQL task -> Insert into Table (FileName) Values = ?

This works as well, but adds it as a new line. Now what i need is for the filename to correspond with the text file info.

Basically this is how my table ends up:
FileName Text
Null Hello this is in the text file
Hello.txt Null

This has to be completely dynamic so i cant hardcode anything.

I hope i explained myself correctly
Any help would be appreciated

 

[*SynergyX*]

so if i understand basically you want one row and not multple
i.e FileName Text | Null Hello this is in the text file |Hello.txt Null
??

anyway solution ---
- now you have a variable User::FileName that stores the file name - add a derived column task - create a column and assign the varible as the value like so
@[User::FileName] in the Expression column of the derived colum task. in your final oledb destination, now map the new derived column to the column in your table --
pretty straightforward.

 

[dabean]

If I'm reading this correctly you're doing 1 insert statement for the contents and 1 for the filename.

Do them both at the same time, eg. "INSERT INTO Table (FileName, Text) ..."

 

[poffle]

Synergy - ye i want it all in 1 line - Hello.txt Hello this is a textfile

dabean - Well, the foreach inserts the data of the text file (its not an insert statement, its a built in function of ssis) and the SQL task inserts the filename using a SQL statement


Edit : nvm

 

[*SynergyX*]

use the derived column method - i use it to store flatfile names when loading. see my first post.

 

[poffle]

Yussus, thank you kindly synergy, works like a bomb with a bit of data conversion thanks man really helped me alot

 

[*SynergyX*]

you are most welcome