0

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 csv 4. Map that variable in the table output step mappings with the table column

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
sachin vaidya
  • 55
  • 10
  • 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.. Jul 07 '18 at 14:14
  • 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 Jul 07 '18 at 14:25
  • @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 Jul 07 '18 at 14:50
  • 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.. Jul 09 '18 at 05:53
  • @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 Jul 09 '18 at 06:32
  • please learn the condepts first.. first you have to set the variable and then you have to get it. learn the set variable and get variable concept. – Helping Hand.. Jul 09 '18 at 06:35
  • @WorkingHard..Sir, please do not get me wrong.. I am indeed setting a variable first and then I am getting it just as you said. But after that, I am setting a different variable which I will again get in next transformation. There is no issue with the concept. However, I have finally got a solution, which I will try to explain to the best of my capability. Regards. – sachin vaidya Jul 10 '18 at 16:21

1 Answers1

0

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.

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
sachin vaidya
  • 55
  • 10