Solved

SQL Server 2005 Flat File Import

Posted on 2006-07-10
14
377 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:stretch73
  • 7
  • 2
  • 2
  • +2
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17075277
Can you make the file or at least some sample data from the file available somewhere?
0
 

Author Comment

by:stretch73
ID: 17075772
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17076934
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17078472
>>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
 

Author Comment

by:stretch73
ID: 17080502
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
 

Author Comment

by:stretch73
ID: 17080542
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:stretch73
ID: 17080701
I get that for every field I don't map
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17080712
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
 

Author Comment

by:stretch73
ID: 17080763
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
 

Author Comment

by:stretch73
ID: 17080807
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
 

Author Comment

by:stretch73
ID: 17089599
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 17089984
well if you are doing it frequently create a job with two steps
step 1 is the dts
step 2 is deletion
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 17125281
Closed, 200 points refunded.
Netminder
Site Admin
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now