I have created a mapping and at the end I have SQL transformation which executes the SQL query (very simple by taking only 1 column) and created the Target table. If I try to dump the data into Target table from SQL Transformation it is taking too long. Could anybody help me how i can over come this problem?
Here is how i used.
It's a SQL transformation with Dynamic Query option (Query build from the table, so it's very simple query gives about 3.1 million records output, when i try to load them into Target table is taking too much time (may be more than 8 hrs). Please help me how to run this quickly? btw. there is no SQL override for this.
I have some questinos for your case.
1.) Do you want to get one column from SQL Transformation (Like the function of lookup) ?
2.) Do you mean SQL transformation query give about 3.1 million records output ??
3.) How many source row do you have in your ETL?
4.) For this sentense,
**when i try to load them into Target table is taking too much time (may be more than 8 hrs).**
Do you mean your ETL use 8 hrs? or use script or others??
Thanks for your quick answer Felix Ho.
1. No lookup's, I need to dump all the data from SQL transformation into target tables.
2. SQL transformation has a Query (from the table, it's a table driven and bringing the queries from tables into SQL transformation and replaceing with parameters to make correct SQL)... from these Queries, i am writing the output into Target tables.
3. Source and target should have same number of records... no filter.
4. I have multiple transformations before this SQL transformation... doing multiple things, but those are not taking much time... once it comes to SQL transformation Query is building and then excuting the Query and populating the Target tables.