Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Data Import from CSV file Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 11:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,180, Visits: 12,033
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1388755
Posted Monday, November 26, 2012 5:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, 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.
Post #1388902
Posted Monday, November 26, 2012 8:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388913
Posted Monday, November 26, 2012 10:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, 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.
Post #1388941
Posted Tuesday, November 27, 2012 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,180, Visits: 12,033
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1389182
Posted Tuesday, November 27, 2012 10:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, 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.
Post #1389309
Posted Tuesday, November 27, 2012 10:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,180, Visits: 12,033
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1389316
Posted Tuesday, November 27, 2012 3:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1389432
Posted Thursday, November 29, 2012 3:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, 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.
Post #1390899
Posted Monday, December 3, 2012 5:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, 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.
Post #1391861
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse