Need help on the scenario

[venkataramana_paidi]

Hi ,

I have some source data  and target as shown below.

Input :
2011_01		2011_02		2011_03
col1	col2	col1	col2	col1	col2
10	30	10	80	70	90
20	40	30	40	20	50
Output:
MONTH_ID	col1	col2
2011_01	10	30
2011_01	20	40
2011_02	10	80
2011_02	30	40
2011_03	70	90
2011_03	20	50

I cannot use pivot transform also as input has not in the desired format.

How can i achieve this output.

Please help me in the logic.

Thanks & Regards,

Ramana.

[RiccardoBrogi · Accepted solution]

Hi Ramana,

you can simply use three different queris (one query for every data source) in which you add an additional column in which you "hard-code" the source name.

After this, you can merge the results of the three queries and obtain the desired output.

Best,

Riccardo

[former_member190674 · Accepted solution]

I found another way we could achieve the same.

Read all source files using single flat file format with wildcard character(*) and use the source information property of the flat file format to pull the source file name as below:

Once we are good with reading all the sources connect it to a query where we can trim the file extensions using rtrim function. Below screenshot

Data flow design as:

Cheers/Santhosh