Replacing texts in a text file using SSIS

Question

Replacing texts in a text file using SSIS

mdkmohan

Right there with Babe

Points: 778
More actions
August 3, 2011 at 1:20 am

#241686

Hi pals,
I have a scenario where I need to replace some text in a text file(flat file destination) with a output from OleDB source.
The text file contains:
Name: opname
Age: opage
DOB: opdob
Now I need to replace the texts(opname, opage, opdob) with the output row returned from OleDB source.
NB: Only one row will be returned.
Kindly guide me.

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

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

sdvoranchik SSCrazy Points: 2800 More actions · Answer 1

Can you clarify something? Your post says your text file has three columns, your ole db source (query) has three columns and there is only one row. Are you looking to replace data (because there are other columns/rows that you have not mentioned) or just create a new (or replace an exisiting) file?

To start drag a Data Flow task into the Control Flow then open the Data Flow Task then...

If you truly need to replace data in an Excel file, with the low volume of data, you could use an Excel source to open the file and read out the exisiting row(s), then use the Lookup task to match the record to data in your table and replace exisiting data OR you could have two sources Excel and OLE DB and use the Merge Join task to push the two together and select the appropriate colunms from the Excel and OLE DB sources to push to the destination.

If you are just looking to create/replace the entire file because it's only three columns and one row then you only need the OLE DB source which will pull the record and the Excel Destination which will write the record to Excel.

Hope that makes sense,

Steve

mdkmohan Right there with Babe Points: 778 More actions · Answer 2

Hello,

Thanks for your reply

I need to generate a txt file with a output returned from OleDB source. Now the problem is Im using a template, so I can't just generate a txt file with the output returned from OleDB source rather I need to replace certain texts which is in the txt file.

The output will be like:(from OleDB Source)

Clarke 32 02-12-1979

In Txt Template:

Name: opname

Age: opage

DOB: opdob

txt file should be: (Flat file destination)

Name: Clarke

Age: 32

DOB: 02-12-1979

Hope now you are clear with my requirement.

sdvoranchik SSCrazy Points: 2800 More actions · Answer 3

Sorry for the delay getting back to you. I'm still not sure if this will be helpful or not, it's kind of hacky, but based on the info here is what I was able to get to work.

I created a template.txt file in note pad with the three rows you specified (name, age, and dob).

In my SSIS package I added a data flow task and then a Source OLE DB Connection Manager.

The query for the source connection manager is (you will notice I included the Name/Age/DOB text and colons as text, the colorder field just allows us to maintain the order you specified in the template file):

SELECT

'Name: ' + opname as Result,

1 as colorder

UNION

SELECT

'Age: ' + opage,

2

UNION

SELECT

'DOB: ' + opdob,

3

ORDER BY colorder

Then I created a flat file destination, browsed to the template.txt file. I had to click the Reset Column Definition button to get it to read properly, but the general idea is your the text file connection manager should be only one column and lableled column0 and will be a String type with length as large as your longest name plus some extra space for the label.

Then when you connect the source to the destination your source column will be Result and the destination will be Column0 in this example (all the names are customizable). I also selected the checkbox in the destnation to "Overwrite Data in Destination File". Then when the package is run, the file and formatting should be just like you are trying to achieve.

So as you can see, we're still not "replacing" the text fields in the template, we are buidling the whole file, labels and data, from scratch.

mdkmohan Right there with Babe Points: 778 More actions · Answer 4

Thanks Buddy,

So from my understanding you are trying to say that we need to generate all the contents of the template in the OleDB source and moving it to a txt file ?

Its a good idea but the actual template has few lines and lots of indentations(tab space, new lines) so how do I achieve this ?

sdvoranchik SSCrazy Points: 2800 More actions · Answer 5

Yes, I am suggesting that one way of accomplishing your task is to build the template in the OLE DB source (there could certainly be other ways). What makes your situation a little more challenging is that the data is not in columns but rows. As for your formatting question, you can embed tabs and other ascii characters into the SQL statement as follows:

SELECT 'Name: ' + CHAR(9) + CHAR(9) + 'Clarke' as Result, ...

And you could get a blank line by UNION and SELECTing an empty string:

UNION

SELECT

'',

3

Hope it's coming together, Steve

mdkmohan Right there with Babe Points: 778 More actions · Answer 6

Hi Pals,

Thanks for your replies. Finally I have created a SP with a out parameter which holds the output template and values from the table. Generated a txt file(with out param) using Script Task in my SSIS.