SolvedPrivate

BULK INSERT issue

Posted on 2013-10-23
8
50 Views
Last Modified: 2016-02-11
I am trying to bring a group of .txt files into my SQL database.
I have been able to get 1 file to load, but have the following issues.
    1) the first row of the file is being skipped by the insert
    2)  I need the name of the .txt file to be a field
Can anyone help me or point me in the right direction?
Cindy
(please be gentle, I am a novice.)

Table creation, Insert and Results attached.
Test file attached.
0
Comment
Question by:vdixon79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39595377
There are no attachments. Attach your scripts.
0
 

Author Comment

by:vdixon79
ID: 39595390
I meant the first COLUMN!
0
 

Author Comment

by:vdixon79
ID: 39595397
I thought they were there.
inv00012--2-.txt
Table-and-insert.docx
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 333 total points
ID: 39595442
>(please be gentle, I am a novice.)
Bwwaaahhh ha ha.  SSIS has somewhat of a steep learning curve, as you are currently experiencing, so good luck with that.

>1) the first row of the file is being skipped by the insert
In the Connection Managers tab double-click on the text file connection we're talking about, and eyeballeth thy 'Header rows to skip' textbox.

>2)  I need the name of the .txt file to be a field
Better to pump the contents of the text file into a 'staging' table, where once all the data is loaded you can slice-dice-perform data validations such as uniqueness, numbers are numbers, dates are dates, no one was born yesterday, products don't cost a brazillion dollars, Cleveland is not in Indiana, etc. are performed.

Then you can do add columns such as file name, preferably in the same function that pumps all your data to its final destination (aka target).
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 333 total points
ID: 39595457
Getting off the soapbox on 2., to add a column with the file name would be to go to the Data Flow Task that moves data, and in between the source and destination components add a Derived Column task that adds the file name as a new column.

How to get the file name?  Depends on how you're building the package, but most packages I've seen file names are variables, and getting into the wiring of making this happen is well above beginner.
0
 

Author Comment

by:vdixon79
ID: 39595513
Such enthusiam for a newbie...thanks

1.  Did you see that I miswrote?  I am not getting the first column.  The first row is coming in fine.

2.  I thought of that.  I also thought about building my own txt file by using the one I have  and copying in the parts I want and adding the file name.  There are several fields I don't care about, but I don't want to ALTER the table after the INSERT since I am going to be inserting hundreds of files to this table daily.  What do you think of me building my own txt file and them inserting it?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 167 total points
ID: 39595601
You have defined the first column WaterTableKey as IDENTITY column. Hence the data in the first column is missing. Remove the IDENITY for that columm and BULK INSERT the data.
If you really want an IDENTITY column, create another table with IDENTITY column and insert data into that table from EJP_Water_Table table.
0
 

Author Closing Comment

by:vdixon79
ID: 39596985
Thanks Jim and Sharath.  Your help was invaluable to my project.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

632 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