Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

BULK INSERT issue

Posted on 2013-10-23
8
Medium Priority
?
59 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1332 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 1332 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 668 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

722 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