Asked By
andybarteit
10 points
N/A
Posted on - 06/27/2011
Hi,
I have a comma separated data file (csv). I have to load this file to a SQL table using Data Flow task in SSIS. Below is the sample data from the source file.
Id, name, city
1, A, NY
2, B, LA
3, C, NY
4, D, NY
5, E, LA
I want the data where the city is equal to “NY” only to be inserted. Like this,
Id, name, city
1, A, NY
3, C, NY
4, D, NY
I have tried writing a SQL query, but I can’t. One way is dumping all the data into one table and again inserting into a new table using conditions. But the data size is around 20 GB. So it takes more time and large space. Is there any alternate way?
Thanks.
Need to dump the required source data into SQL table
I don’t think that you can do this using SQL queries directly. You can use SSIS tasks to accomplish this result. You can go with the Data Flow task and select the Flat File Source component. Then configure Flat File Source task to the csv source file using the connection manager. It will fetch all the data from the source file.
Then, you can use the Conditional Split Transform Task to insert the data according to the given condition. In the Conditional Split Transform task, you can give the condition to the column that city=’NY’ which will allow only the data that satisfies the condition. Then, redirect this conditional split to an OLEDB Destination component.
Now, execute the SSIS package and see the results that would be expected data. This process will not take more space in your computer and the process speed will be high. You can also get the expected results without writing any SQL queries. SSIS could do lot of things and would make the process very simple i.e. just by dragging and dropping the tasks. Try SSIS.
