September 21, 2009 at 1:51 pm
Hi,
I have a requirement to export header row and detail of each sales order in a text file to make a invoice. I have created a derived column that includes data from 3 columns. However, the new(derived) column needs to be seperated by the Tab delimiter.
For example:
OrderID, ProductName, OrderQuantity
000001, Bike,300
My expected derived column is
OrderDetail
000001 Bike 300 (This row should be seperated by Tab Delimiter.
I am not able to include tab delimiter inside derived column expression.
Any link or a piece of code would be great.
Ganesh
September 21, 2009 at 3:50 pm
I'm not sure you can do that there. But I know that you could manipulate it with a Script Component in the dataflow component. It would be in the role as Transform. You could take the input rows and transform them that way. The derived column would probably be faster.
Maybe you could force the TAB character into a variable and use that variable in the assembly of the column..
How is that?
CEWII
September 21, 2009 at 4:00 pm
Elliott W (9/21/2009)
--edit--Maybe you could force the TAB character into a variable and use that variable in the assembly of the column..
--
CEWII
Or put some other unused character there and then run a find/replace across the entire file at the end of processing.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 22, 2009 at 7:41 am
Phil Parkin (9/21/2009)
Elliott W (9/21/2009)
--edit--Maybe you could force the TAB character into a variable and use that variable in the assembly of the column..
--
CEWII
Or put some other unused character there and then run a find/replace across the entire file at the end of processing.
The problem I see with that is that you have to pay to assemble the string and then again to do the replace option.. Getting it to be the TAB character isn't hard, it is just more difficult than it should be..
CEWII
September 22, 2009 at 8:12 am
Thank you so much for your reply.
My case is
I am exporting rows from OrderHeader and Orderdetail tables into a text file to create the invoice. I want to display all non-sort key columns of as a string seperated by Tab delimiter in derived column transformation so that I can apply Merge transformation to merge two query.
Any suggestion would be great.
September 22, 2009 at 8:35 am
An example of your input data would paint the picture much more completely than just words, no matter how detailed your verbal explanation ... So far I have a picture of impossibility in my mind (I do not believe that a merge join will do what you want). Some sample data would, perhaps, allow us to suggest alternatives.
Elliott - your point is well taken - my idea would only be acceptable with fairly small data volumes - though I have used a find/replace routine written in VB.NET that worked fast for text files up to around 1GB.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 22, 2009 at 9:53 am
Phil Parkin (9/22/2009)
An example of your input data would paint the picture much more completely than just words, no matter how detailed your verbal explanation ... So far I have a picture of impossibility in my mind (I do not believe that a merge join will do what you want). Some sample data would, perhaps, allow us to suggest alternatives.Elliott - your point is well taken - my idea would only be acceptable with fairly small data volumes - though I have used a find/replace routine written in VB.NET that worked fast for text files up to around 1GB.
Phil
Phil,
I completely understand your point, the only thing I come back with is that in VB.NET I could stream through the file in a single thread and process all 1GB without a lot of overhead. Unfortunately we have to deal with each record individually in the context of the pipeline. And although I've done something similar in VB.NET in SSIS it has strong performance limitations. So again you are exactly right, small data sets..
I think he can accomplish it by creating a variable, using an EXEC SQL task and returning a single character using the CHAR(9) function. Then using a derived column assemble what he wants.
CEWII
September 22, 2009 at 9:55 am
ganeshlohani (9/22/2009)
Thank you so much for your reply.My case is
I am exporting rows from OrderHeader and Orderdetail tables into a text file to create the invoice. I want to display all non-sort key columns of as a string seperated by Tab delimiter in derived column transformation so that I can apply Merge transformation to merge two query.
Any suggestion would be great.
Hold on..
You are taking the two tables, dropping them out to disk and then wanting to suck them back in so you can do a merge? Is that right? Are they from the same physical system (I would assume so). Please describe the problem you are trying to solve as well as your proposed solution, I think we might be able to refine it for you.
CEWII
September 22, 2009 at 10:04 am
Elliott W (9/22/2009)
Phil Parkin (9/22/2009)
An example of your input data would paint the picture much more completely than just words, no matter how detailed your verbal explanation ... So far I have a picture of impossibility in my mind (I do not believe that a merge join will do what you want). Some sample data would, perhaps, allow us to suggest alternatives.Elliott - your point is well taken - my idea would only be acceptable with fairly small data volumes - though I have used a find/replace routine written in VB.NET that worked fast for text files up to around 1GB.
Phil
Phil,
I completely understand your point, the only thing I come back with is that in VB.NET I could stream through the file in a single thread and process all 1GB without a lot of overhead. Unfortunately we have to deal with each record individually in the context of the pipeline. And although I've done something similar in VB.NET in SSIS it has strong performance limitations. So again you are exactly right, small data sets..
I think he can accomplish it by creating a variable, using an EXEC SQL task and returning a single character using the CHAR(9) function. Then using a derived column assemble what he wants.
CEWII
Just to make things clear, I did mean VB.NET within a Script Task (not a Script Component, nor a standalone exe) - to be run once per package execution, at the end of file creation, not once per record or anything silly like that. It is not a huge overhead in such a situation, though not a solution for the purists among us who like to achieve everything in one pass (of which I am one!)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 22, 2009 at 11:18 am
Why not add the TAB to you source SQL using the CHAR function then concatenate it to your new column in the Derived Column component?
Source SQL ex:
SELECT
OrderID,
ProductName,
OrderQuantity,
CHAR(9) AS Tab
FROM
YourTable
Derived Column ex:
OrderDetail: [Tab] + [OrderID] + " " + [ProductName] + " " + [OrderQuantity]
September 22, 2009 at 11:19 am
Just to make things clear, I did mean VB.NET within a Script Task (not a Script Component, nor a standalone exe) - to be run once per package execution, at the end of file creation, not once per record or anything silly like that. It is not a huge overhead in such a situation, though not a solution for the purists among us who like to achieve everything in one pass (of which I am one!)
Ok.. I hate to do multipass as well when there is a better way..
CEWII
September 22, 2009 at 11:27 am
Also a good idea Erik!
CEWII
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply