Passing variable in column mapping of CSV file input

0
votes

I have a CSV file Input step using which I am populating the CSV file data into my final_result table successfully. However, there is a ID column in my final_result table which will show that this feed belongs to which batch.

The value of this ID column should come from a variable. For this what I have done is,

  1. At the start of each batch, an entry is made into a separate batch_info table which has an identity column that denotes the batch ID.
  2. This batch ID is stored into a variable using a set variable step and then accessed in the next transformation using the get variable step.
  3. In this next transformation, which contains a CSV input step and a table output step as well, I am manually writing this variable into database field mapping of table output step to match it with my ID column in final_result table.

However, though the variable has a value, it is not getting into the final_result table and the ID columns is showing null.

Is there any other better way to do this? Please let me know if I need to provide more information.

I am trying to explain this with images as well.

1.) Set the variable p_index which is out variable of the db stored proc:

1. Set the variable p_index which is out variable of the db stored proc 2.) Get the value of variable set in previous step:

2. Get the value of variable set in previous step 3.) Manually add the variable p_index in the columns list of the csv:

3. Manually add the variable p_index in the columns list of the csv4. Map that variable in the table output step mappings with the table column

1
pentahopentaho-spoon
What you have kept in 1st transformation? Attract the .kjb file along with .ktr in question itself. You can also put the write to log step and check that after which point you are not getting values ? At the time of defining variable did you set the variable as a valid in root job ? - Helping Hand..
thanks for the reply.. yes, I am getting the value in the variable right till the point I am trying to use it as a field in the csv input (manually adding in the columns list). I checked it by showing its value through display messagebox info step. I will attach the files at the earliest. - sachin vaidya
@WorkingHard..I tried to add the images but they are not directly visible. When you click on each statements (in blue) in the end , you will see images. I am not sure why it's happened like this. - sachin vaidya
It's hard to understand by looking at screen-shot. but you can-not use set variable and get variable in single transformation, it is a basic rule. - Helping Hand..
@WorkingHard.. In a single transformation, I am Getting variable set in previous transformation and Setting a different variable for the next transformation. It does works fine. However, coming to my problem, in a simplest form.. I want to pass a variable in the table column and stream column mapping, for one of the columns. Not sure how to do this. Regards. - sachin vaidya

1 Answers

0
votes

I finally got the solution I was looking for, after a lot of head banging to the wall and some ideas from friends :-)

I got my data from the CSV file and stored it in "Copy Rows To Result" task and Set the batch ID in a variable. In next transformation, I used get rows from result and also did the get variable and combined these two into a cartesian product step and the output of this cartesian product step is directed into the table output step. This way, my batch_ID is merged with each row of the current batch.

It was a nice learning experience though.