SolvedPrivate

BULK INSERT issue

Posted on 2013-10-23
8
48 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
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 65

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 65

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

680 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