Import CSV file into SQL table

I am developing a package to import a flat CSV file into a SQL table.  It does work, but I am unhappy with the performance of over an hour to import an average of 50,000 records.  I first tried the Data Flow Task to map the columns and define the output colun datatypes/lengths to match the SQL destination columns.  Some output columns are to be ignored and are selected as such in the transformation.  I tried initially with the CSV source file location on my local drive, being imported over to the server.  With the poor performance, I then tried the CSV source file location on the same server as the destination, but performance was still poor.
So I then tried a Bulk Insert Task, which I'm not very familiar with.  This does not seem to work since the source columns are larger than the destinationon and/or I have some source columns that need to be ignored.  

Any ideas how I can improve performance of this import?  
VDannerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLCommented:
I worked w/several very similar projects -- unfortunately, I never got the runtime I needed out of DTS when importing the csv.  I got MUCH better runtimes using bulk insert.

You need only to use a format file.  see BOL:  'Using Format Files'
This will tell you exactly how to workaround the table and file variances.  Let me know if you need an example.
0
VDannerAuthor Commented:
Since I am so new to Bulk Insert, I would very much appreciate an example.  
0
dbaSQLCommented:
sure, gimme one sec
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

dbaSQLCommented:
this is a genericised version of a proc i use to load files.  i actually am loading a LOT more than this, but, i pulled it all out except for one, just to keep things simple:

CREATE PROC dbo.usp_loadFiles
AS
SET NOCOUNT ON

BULK INSERT database.dbo.table
 FROM '\\servername\directoryname\fileA.csv'
WITH(FORMATFILE = 'E:\MSSQL\formatfile.fmt',FIELDTERMINATOR = ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH = 100000)

SET NOCOUNT OFF
GO

You will see, I have the format file in there,k as well as the field and row terminators, FIRSTROW and ROWS_PER_BATCH.  

FIRSTROW is necessary because the files have headers (column names).  i need to skip that row before starting to load the data

here is a format file:

8.0
6
1      SQLCHAR      0      50      ","      2      field1      SQL_Latin1_General_CP1_CI_AS
2      SQLCHAR      0      1      ","      3      field2      SQL_Latin1_General_CP1_CI_AS
3      SQLCHAR      0      1      ","      4      field3      SQL_Latin1_General_CP1_CI_AS
4      SQLCHAR      0      4      ","      5      field4      SQL_Latin1_General_CP1_CI_AS
5      SQLCHAR      0      4      ","      6      field5      SQL_Latin1_General_CP1_CI_AS
6      SQLCHAR      0      4      "\n"      7      field6      SQL_Latin1_General_CP1_CI_AS

(i've just cut/pasted... hopefully it prints out ok)

do you have BOL, and if so, you really need to read 'using format files'.  it is exactly what you need to know - and it explains the format file in detail

0
VDannerAuthor Commented:
Thank you for this example.  I will try out this morning and if all goes well close this question.  I'm not sure what you mean by BOL?  I am working in Business Intelligence Development Studio (BIDS) and have now opened their Help engine, searching on format files.  Between your example and their narratives this should resolve my problem.
0
dbaSQLCommented:
BOL =  Books Online
http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

basically, it's one of your sql bibles.  or at least it is one of my sql bibles
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VDannerAuthor Commented:
Thank you.  Regarding BOL (duh...should have guess that).  I am downloading that version now and feel confident this and your example will do the trick.  
0
dbaSQLCommented:
Excellent.  Good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.