asked
Replacing some texts in a text file using SSIS
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.
If only a single row will be returned, then I suggest to use a **`Execute SQL Task`** with `ResultSet` set too `Single Row` and store the result in a variable. Then you put a **`Script Task`** in the control flow, pass the variable with returned value to the scrip task and then do the replacement of the text in the flat file using a C# or
VB.NET. The `Flat File destination` is not designed for such purposes. It can only write new rows to the flat file.
What I would probably do is have 1 Flat File Source, 1 Flat File Destination, and 1 OLEDB Source. The flat file source and destination will be the same file, you would read the file, then join with the OLEDB source (or you could do a lookup) using a merge join with a left outer join. The put a conditional split that moves the NULL joins on a differenet path, and update the appropriate column with a derived column, then do a union all to remerge (is that a word?) the two datasets. Then you can write the results to the flat file destination and voila, you have an updated flat file.
Hello Pals, 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. In Txt Template (Flat File Source): Name: opname Age: opage DOB: opdob (OleDB Source) - Returns the below row with 3 cols Clarke 32 02-12-1979 output txt file should be: (Flat file destination) Name: Clarke Age: 32 DOB: 02-12-1979 Hope now you are clear with my requirement
Are there different templates? do they have more than 3 rows in the template? Something you maybe could do is instead of having an OLEDB source, you could get the data using a lookup. when you read the template, split the string by the colon symbol in the text source then do the lookup(s?) and replace the op values with the data from the lookup in the destination.
From OLEDB source can come more than 1 record? And all the records are written into the same flat file? In that case I would use a Data Flow Task with OLE DB Source and as a destination I would use a Script component. Then in the script Component in the PreExecute method load the template. Then in the ProcessInputRow you can use the template with the data coming from OLE DB to format the final form of the output and finally write the formatted data into the output file. To speedup the process you open/create the destination file in the PreExecute method and close the file in the PostExecute method, so you are not opening and closing the file for each record going through the data flow.
Hello All, I decided to have a template content into a out parameter in a stored procedure. The Stored procedure returns a rowset as well as a output parameter. Now Im going to generate two text file 1. With the rowset returned from SP. 2. With the Output parameter. Is that possible ?
