SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Import from CSV file


Data Import from CSV file

Author
Message
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50879 Visits: 21152
rhythm.varshney (11/26/2012)
waiting for your valuable input guys........


You still have not asked a single question - there is nothing to respond to.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rhythm.varshney
rhythm.varshney
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 195
Phil Parkin (11/26/2012)
rhythm.varshney (11/26/2012)
waiting for your valuable input guys........


You still have not asked a single question - there is nothing to respond to.


please suggest the way to get the data into my DB table based on above mentioned logic i.e how can I import data from my CSV to DB table considering above logic.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212307 Visits: 41977
rhythm.varshney (11/26/2012)
Phil Parkin (11/26/2012)
rhythm.varshney (11/26/2012)
waiting for your valuable input guys........


You still have not asked a single question - there is nothing to respond to.


please suggest the way to get the data into my DB table based on above mentioned logic i.e how can I import data from my CSV to DB table considering above logic.


Personally, I wouldn't bother with SSIS with such a task. I'd use BULK INSERT into a staging table and write some good old fashioned "upsert" code to get the job done.

"Upsert" in this case (you're using 2005) means a simple conditional INSERT if the key data doesn't already exist and a simple conditional UPDATE if it does.

Using such a staging table would also allow you to do a lot of pre-validation and error checking before you ever allow the external unknown data anywhere near your existing data.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rhythm.varshney
rhythm.varshney
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 195
Jeff Moden (11/26/2012)
rhythm.varshney (11/26/2012)
Phil Parkin (11/26/2012)
rhythm.varshney (11/26/2012)
waiting for your valuable input guys........


You still have not asked a single question - there is nothing to respond to.


please suggest the way to get the data into my DB table based on above mentioned logic i.e how can I import data from my CSV to DB table considering above logic.


Personally, I wouldn't bother with SSIS with such a task. I'd use BULK INSERT into a staging table and write some good old fashioned "upsert" code to get the job done.

"Upsert" in this case (you're using 2005) means a simple conditional INSERT if the key data doesn't already exist and a simple conditional UPDATE if it does.

Using such a staging table would also allow you to do a lot of pre-validation and error checking before you ever allow the external unknown data anywhere near your existing data.


Thanks Jeff.
Actually I did the same and suggested the same even wrote all UPSERT logic but my lead wants it to be implemented using ETL. Don't know why though I asked him that handling it in DB would be more easy to maintaing considering future changes and performance(as I read somewhere tha SSIS is not so good in update) also.

So could you please suggest the approach to get it done via SSIS.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50879 Visits: 21152
There is no good way of doing this in SSIS - I'm with Jeff on this one.

If you want to put an SSIS 'wrapper' round it, something like this would work:

1) Exec SQL: Truncate Staging table
2) Dataflow: Insert to Staging table
3) Exec SQL: UPSERT logic to move from Staging to main data table


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rhythm.varshney
rhythm.varshney
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 195
Phil Parkin (11/27/2012)
There is no good way of doing this in SSIS - I'm with Jeff on this one.

If you want to put an SSIS 'wrapper' round it, something like this would work:

1) Exec SQL: Truncate Staging table
2) Dataflow: Insert to Staging table
3) Exec SQL: UPSERT logic to move from Staging to main data table



Thanks Phil.
However I went through some posts where they have suggested look up transformation for this.
But not sure if it would not hit the performance in future.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50879 Visits: 21152
rhythm.varshney (11/27/2012)
Phil Parkin (11/27/2012)
There is no good way of doing this in SSIS - I'm with Jeff on this one.

If you want to put an SSIS 'wrapper' round it, something like this would work:

1) Exec SQL: Truncate Staging table
2) Dataflow: Insert to Staging table
3) Exec SQL: UPSERT logic to move from Staging to main data table



Thanks Phil.
However I went through some posts where they have suggested look up transformation for this.
But not sure if it would not hit the performance in future.


The cached and partially cached lookups won't work because you have duplicates (by name) in your source data. Maybe the uncached lookup would work, but it is performance suicide when compared with what the SQL Server engine can give you.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212307 Visits: 41977
rhythm.varshney (11/26/2012)
Jeff Moden (11/26/2012)
rhythm.varshney (11/26/2012)
Phil Parkin (11/26/2012)
rhythm.varshney (11/26/2012)
waiting for your valuable input guys........


You still have not asked a single question - there is nothing to respond to.


please suggest the way to get the data into my DB table based on above mentioned logic i.e how can I import data from my CSV to DB table considering above logic.


Personally, I wouldn't bother with SSIS with such a task. I'd use BULK INSERT into a staging table and write some good old fashioned "upsert" code to get the job done.

"Upsert" in this case (you're using 2005) means a simple conditional INSERT if the key data doesn't already exist and a simple conditional UPDATE if it does.

Using such a staging table would also allow you to do a lot of pre-validation and error checking before you ever allow the external unknown data anywhere near your existing data.


Thanks Jeff.
Actually I did the same and suggested the same even wrote all UPSERT logic but my lead wants it to be implemented using ETL. Don't know why though I asked him that handling it in DB would be more easy to maintaing considering future changes and performance(as I read somewhere tha SSIS is not so good in update) also.

So could you please suggest the approach to get it done via SSIS.


Just to be clear, "ETL" doesn't mean "SSIS", which can, of course, do "ETL". To wit, SSIS frequently requires a T_SQL script to do some "ETL". I just cutout the middleman and avoid SSIS so I'm of no help there.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rhythm.varshney
rhythm.varshney
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 195
Phil Parkin (11/27/2012)
rhythm.varshney (11/27/2012)
Phil Parkin (11/27/2012)
There is no good way of doing this in SSIS - I'm with Jeff on this one.

If you want to put an SSIS 'wrapper' round it, something like this would work:

1) Exec SQL: Truncate Staging table
2) Dataflow: Insert to Staging table
3) Exec SQL: UPSERT logic to move from Staging to main data table



Thanks Phil.
However I went through some posts where they have suggested look up transformation for this.
But not sure if it would not hit the performance in future.


The cached and partially cached lookups won't work because you have duplicates (by name) in your source data. Maybe the uncached lookup would work, but it is performance suicide when compared with what the SQL Server engine can give you.


Hi Phil,
Could you please help me out to deal with duplicates using look up as you mentioned.
rhythm.varshney
rhythm.varshney
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 195
I got it by following way.Please let me know if there is any better way of doing it via SSIS transformations.

1.Truncating and filling records from CSV to staging table.
2.Putting look up on staging table and filling records in base table.(matched rows go for update and unmatched rows for insert)
3.To deal with duplicates I put an Execute SQL Task in control flow and there I have built query to remove duplicates rather updating the same record if it is there as per my requirement.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search