January 15, 2009 at 8:21 am
Hmmm so many choices..............
In order to load a csv file into a SQL 2005 Database using SSIS..........
Method 1: Use a Bulk Insert Task.
or
Method 2: Use a Data Flow Task comprising of a Flat file source to either
a) A SQL Server destination object
or
b) An OLE DB Destination object
What are the pros and cons of each Method, as both seem equally valid ways of doing the work required?
I would greatly appreciate anybody else's input.
--Shaun (unfotunate soul doing SSIS implementation work)
Hiding under a desk from SSIS Implemenation Work :crazy:
January 15, 2009 at 8:32 am
Done a bit of research
Microsoft SQL Server 2005 Integration Services By Kirk Haselden, Trey Johnson
These guys state that the prefered method to use should be data flow as it has richer functionality, and there is no difference to the execution time as both use the same API calls.
As I am targeting a SQL Server DB on the same box as the task is running using the OLE DB destination is not necessary, the job I'm doing will always run this way (security reasons).
I'm happy with the answer I've found. 😀
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 15, 2009 at 8:49 am
One thing to note. If you use the SQL Server destination component, the SSIS package must be running on the SQL Server that is the target. It must be the local server. This limits your configuration options.
The OLEDB Destination component can point to a remote server.
January 15, 2009 at 8:52 am
Michael Earl (1/15/2009)
One thing to note. If you use the SQL Server destination component, the SSIS package must be running on the SQL Server that is the target. It must be the local server. This limits your configuration options.The OLEDB Destination component can point to a remote server.
I am well aware of this as implied by my second post, but thanks for stating it plain and simple.
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 15, 2009 at 8:53 am
That point gets missed by a lot of people.
January 15, 2009 at 9:00 am
I would use SSIS, as this seems like a simple project to give you exposure to it. Also it is becoming a requirement for a lot of SQL jobs to know SSIS, so it may help your career.
January 15, 2009 at 9:17 am
steveb (1/15/2009)
I would use SSIS, as this seems like a simple project to give you exposure to it. Also it is becoming a requirement for a lot of SQL jobs to know SSIS, so it may help your career.
Sorry I do not understand your post.............are you being serious?
I am using SSIS, this is a SSIS forum, and my thread is about which method to use in SSIS to import a csv file into a SQL server 2005 database.
Can you explain your post?
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 15, 2009 at 9:21 am
Shaun McGuile (1/15/2009)
steveb (1/15/2009)
I would use SSIS, as this seems like a simple project to give you exposure to it. Also it is becoming a requirement for a lot of SQL jobs to know SSIS, so it may help your career.Sorry I do not understand your post.............are you being serious?
I am using SSIS, this is a SSIS forum, and my thread is about which method to use in SSIS to import a csv file into a SQL server 2005 database.
Can you explain your post?
--Shaun
Your question asked about doing a very simple thing in SSIS, so I assumed that you had no eperience in it, and was suggesting that it is a useful skill to learn..
January 15, 2009 at 9:27 am
My question was not
'how do you import a csv file using SSIS?'
but rather
'What specific objects from the tool box are the 'right' ones' to use to do the job?'
Maybe it was not clear.
'An adjustable wrench can be used to tighten any nut, but the right size spanner is the correct tool.'
--Shaun T'Zu's Art of Education
Hiding under a desk from SSIS Implemenation Work :crazy:
January 15, 2009 at 9:36 am
Shaun McGuile (1/15/2009)
'What specific objects from the tool box are the 'right' ones' to use to do the job?'
That's also how I read it, and it made me think about why I use DataFlow and OLEDB Destination all the time.
Allways good to rethink once in a while.
BTW: Most sources I have seen state there is little performancegain from SQL Dest over OLEDB Dest. As my ClientTools are on my laptop, and the dev db is usually on a server, I stick to the OLEDB Destination.
'An adjustable wrench can be used to tighten any nut, but the right size spanner is the correct tool.'
--Shaun T'Zu's Art of Education
😀
Peter Rijs
BI Consultant, The Netherlands
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply