Solved

SQL Server 2005 Flat File Import

Posted on 2006-07-10
14
381 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 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