SQL Server 2005 Flat File Import

I know this should be really simple but I am stumped.  I am trying to import a text file into a SQL table and I'm having problems.  

The original flat file is 38 columns of which I only need to import about 25.  On the advanced tab of the wizard, I delete out the columns I don't need and then finish the rest of the steps.  All of the column widths are set to NVARCHAR 50 except for six columns that I need to set to NVARCHAR 500.  The problem is that every time I try to finish the import it bombs out on those columns with truncation errors.  When I preview the text file after the deleted columns, it also looks like it kept the data but just shifted the column headers over.  

Any thoughts?

Thanks,

N
stretch73Asked:
Who is Participating?
 
NetminderConnect With a Mentor Commented:
Closed, 200 points refunded.
Netminder
Site Admin
0
 
Anthony PerkinsCommented:
Can you make the file or at least some sample data from the file available somewhere?
0
 
stretch73Author Commented:
Here is a sample of the data.  It's delimited by row by CRLF and by column by |.  Let me know if that works.


RBKZI1Q |33331111|10557866|JASHISFF                         |smith        |  |ASSISTANT I                                                                              |318|0005492|5492-GREATER NORTH BAY PREMIER|TMDABD..AA|415-451-2720      |415-151-2716      |Ø                     |Ø         |CA4-124-22-05   |sodabeh.kashi@yahoo.com                                   |SAN RAFAEL MAIN OFFICE                  |1000 4TH ST%SAN RAFAEL CA 94901-3121                                                                                                                                                                                            |SAN RAFAEL                    |CA|94901-3121  |smith, Brandi L.                               |SVP; PREMIER MARKET MANAGER                                                                             |b.campbell@yahoo.com                                 |            |     |Ø                 |A|5|TSXYM|2224        |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|AA600 |Ø
KLK918N |77778888|10557755|LUU                           |amy         |  |OPERATIONS REP                                                                               |318|0035122|MAILROOM WEEKEND              |FTOKLLN.AB|Ø                 |Ø                 |Ø                     |Ø         |CA9-725-21-13   |Ø                                                                 |Ø                                       |LOS ANGELES CA 90012-1514                                                                                                                                                                                                                     |LOS ANGELES                   |CA|90012-1514  |jones, Wendy L.                                   |OPERATIONS TEAM MANAGER                                                                                 |w.T.Gaona@yahoo.com                                   |            |     |Ø                 |A|5|64VXG|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|OA601 |Ø
UC19198 |55554444|22455666|LINCOLN                       |THOMAS         |J |CASH SERVICES REP II                                                                                    |736|0022153|0202 HARTFORD MONEY CENTER CT |FTVVFCH.AC|160-716-1283      |Ø                 |Ø                     |Ø         |CT2-511-B2-01   |Ø                                                                 |Ø                                       |HARTFORD CT 06115                                                                                                                                                                                                                           |HARTFORD                      |CT|06115       |LeGe, Paul V.                                   |OPERATIONS MANAGER                                                                                      |v.legeytt@yahoo.com                                   |            |     |Ø                 |A|5|6D3BK|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DF|Y|OC601 |Ø
NB181MN |45454545|Ø       |FELTIONCAROLYN A.             |Ø              |Ø |CONTRACTOR                                                                                    |110|0007933|PHOTO ID                      |AFSHHS..ES|404-607-3604      |414-617-3386      |Ø                     |Ø         |GA2-002-01-01   |carofelton@yahoo.com                                |MIDTOWN CENTER                          | ST NE%ATLANTA GA 30308-1215                                                                                                                                                                                                       |ATLANTA                       |GA|30308-1215  |Ø                                                 |Ø                                                                                                       |Ø                                                                 |Ø           |     |Ø                 |A|Ø|D6YH6|Ø           |A.                  |UNITED STATES OF AMERICA                |O|WK|Y|Ø     |C
NBD3432 |65656565|11227000|BURKS                         |jones         |I |CNSLT - APPS PROG - CA                                                                              |318|0010323|ECS SYSTEMS                   |FKSBBG..AA|925-692-9356      |125-615-2979      |Ø                     |Ø         |CA4-701-09-05   |martha.burks@yahoo.com                                    |BUILDING A                              | GRANT ST%CONCORD CA 94520-2445                                                                                                                                                                                                       |CONCORD                       |CA|94520-2445  |gnalr, Kelley K.                                |VP; TECH MGR - APPS PROG                                                                                |Ihagerty@yahoo.com                                  |            |     |Ø                 |A|3|FYWQD|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|TA018 |Ø
XBKW98C |14141414|10555628|DURAN                     |ann      |A |SERVICE REP                                                                                 |794|0000076|RIO CONSUMER TEAM 8           |FHRABCBDAH|505-462-4099      |501-412-4146      |Ø                     |Ø         |NM1-502-01-42   |patricia.anceau@yahoo.com                              |RIO RANCHO CONSUMER CALL CENTER         |4330 PICABO ST%RIO RANCHO NM 87144                                                                                                                                                                                             |RIO RANCHO                    |NM|87144       |Gamble, Lisa M.                                 |CUSTOMER SERVICE TEAM MANAGER                                                                           |Mgabaldon@yahoo.com                                 |            |     |Ø                 |A|5|YRCD2|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|CA600 |Ø
NBy9414 |48484848|24134444|HENDERS                     |SHANE         |J |IMPROVEMENT CONSULTANT                                                                        |001|5790027|CRE LEARNING SALES - RETAIL AE|AEEFEJ..WC|518-512-1814      |818-512-8110      |Ø                     |Ø         |CA2-129-22-02   |sheyla.j.henderson@yahoo.com                              |GLENDALE MAIN OFFICE                    |GLENDALE MAIN OFFICE%345 N BRAND BLVD%GLENDALE CA 91203-2368                                                                                                                                                                                                   |GLENDALE                      |CA|91203-2368  |Ryyyd, Cynthia                                  |VP; PROGRAM DVLPMT MGR                                                                                  |CRErudolph@yahoo.com                                 |            |     |Ø                 |A|1|FXV2H|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|HC018 |Ø
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Einstine98Commented:
I'm not sure what do you mean by " I delete out the columns I don't need " what you need to do is unmap the columsn that you do not need...

The failure is occuring because there is a field that is more than 50 in size... so some truncation will occur... try increasing the size (just for test sake) to 100...
0
 
Anthony PerkinsCommented:
>>Here is a sample of the data. <<
Please post the structure of your target table and I will try and duplicate and suggest a solution.
0
 
stretch73Author Commented:
The table structure is pretty simple.  All fields are NVARCHAR(50) except columns 6, 16, 18, 22, and 24.  

I tried not mapping the fields but it threw an error saying that the columns were not part of the data flow and should be deleted.  
0
 
stretch73Author Commented:
Here's the error I get when I don't map the fields I don't want:

Warning 0x80047076: Data Flow Task: The output column "Column 0" (10) on output "Flat File Source Output" (2) and component "Source - CorpDir_txt" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
 (SQL Server Import and Export Wizard)



I get that for every field.
0
 
stretch73Author Commented:
I get that for every field I don't map
0
 
imran_fastCommented:
Can you try it in Data Transformation services (DTS) sql 2000 to import this data?

and which version of sql 2005 you are using .

one more thing  create a temporary empty table and try to import data in that table make all the fields in that table nvarchar(1000) and see if you are getting any error.

if not check len(columnname) for each column and see which column is exceeding the limit.

Hope you understood.
0
 
stretch73Author Commented:
Okay, field 23 also needed to be blown out to NVARCHAR(500) and I needed to put the output in a new table.  It's running now but I'm pulling over 250k records.  I'm still getting the validation error, more of a suggestion really

Warning 0x80047076: Data Flow Task: The output column "Column 2" (16) on output "Flat File Source Output" (2) and component "Source - CorpDir_txt" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
 (SQL Server Import and Export Wizard)

so I'd like to know how to delete those columns.  I'm trying to schedule this to run weekly so the more streamlined the process the better.
0
 
stretch73Author Commented:
Here's the info on the SQL version I am using:

Microsoft SQL Server Management Studio      9.00.1399.00
Microsoft Analysis Services Client Tools      2005.090.1399.00
Microsoft Data Access Components (MDAC)      2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML                  2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer            6.0.2900.2180
Microsoft .NET Framework            2.0.50727.42
Operating System                  5.1.2600

0
 
stretch73Author Commented:
Couldn't get it to work.  I had to import the entire file and then just delete the columns out of the SQL table.  
0
 
imran_fastCommented:
well if you are doing it frequently create a job with two steps
step 1 is the dts
step 2 is deletion
0
All Courses

From novice to tech pro — start learning today.