Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2005 Flat File Import

Posted on 2006-07-10
14
Medium Priority
?
397 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
13 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

824 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