CSV file to different tables

Question

CSV file to different tables

bharadwaj.manchu

SSC Enthusiast

Points: 171
More actions
November 15, 2010 at 5:01 am

#242895

Hi,
I am very new to SSIS. Very confused to convert a CSV file to different tables.
Still not used to with the terminology.
I have a CSV file with 6 columns customer name,DOB,Country,Adress1,Address2,Postcode,Amount.
I have to copy this into 3 tables Customers,Cust_address,Paymentdetails and customerID and AddressID columns auto generated in first two tables.
And some countries and postcodes are swapped in source files. some have null values.
I have taken a flatfile (source) and OLEDB(destination) connection managers and a dataflow task in control flow.
How to clean the data to get the data in respective fields using SQL code.
How to autogenerate a field that is not in source file.
Plz help me either by an advice or any link to follow.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

The One Approach that i know is to dump the csv file into the temp table and then use the execute sql task, where you can write a Query to insert into your respective tables from the temp table and you can also correct the data insiede the Query.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

To clean your data, you can use the derived column transformation.

You can also use it to generate data (if it is not too complex, otherwise you can use a script component).

If you only want to do it with T-SQL, consider writing the data to a staging table, as sharath.chalamgari suggested.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

bharadwaj.manchu SSC Enthusiast Points: 171 More actions · Answer 3

Thanks sharath.

Now again my doubt is which task is used to dump the source data into temp table.

and do I need to take another dataflow task to send data from temp table to destination tables.

I am not very good at T-SQL, so if possible could you plz post me an example query to cleanse the data or any link to learn such queries.

Thanks again for immediate response.

bharadwaj.manchu SSC Enthusiast Points: 171 More actions · Answer 4

Thanks da-zero.

I didnt understand that how can we clean data using a derived column task.

also If possible suggest me an approach on how to cleanse data in an SQL query.

Thanks again.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

bharadwaj.manchu (11/15/2010)
Thanks da-zero.
I didnt understand that how can we clean data using a derived column task.
also If possible suggest me an approach on how to cleanse data in an SQL query.
Thanks again.

You can use the derived column transformation (not task) to replace the null values and to swap the columns back. You must use expressions for this. If you google on "SSIS Derived Column", you should find plenty of examples.

I suggest you do some reading on the subjects of SSIS and T-SQL. If you want, I can propose some excellent books.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

bharadwaj.manchu SSC Enthusiast Points: 171 More actions · Answer 6

thanks da zero.

I will go through the derived columns and come back to u if any doubts.

Plz tell me the books names.

Pls send links of any e-books or tutorials also.

thanks again.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Integration Services:

Professional Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer)

It's quite a large book, but the first chapters provide a really good overview of SSIS. Case studies are performed through the book.

MSDN Tutorials for SSIS: http://msdn.microsoft.com/en-us/library/bb522499.aspx

An overview of MSDN Tutorials: http://msdn.microsoft.com/en-us/library/ms167593.aspx

(you can find T-SQL tutorials there as well)

Microsoft has a course on T-SQL:

2778A - Writing Queries Using Microsoft SQL Server 2008 Transact-SQL

It comes with a book, so if you could get your hands on that one, that should really help.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP