BULK INSERT issue

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.
E J PopeAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
SharathData EngineerCommented:
There are no attachments. Attach your scripts.
0
 
E J PopeAuthor Commented:
I meant the first COLUMN!
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
E J PopeAuthor Commented:
I thought they were there.
inv00012--2-.txt
Table-and-insert.docx
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>(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
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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
 
E J PopeAuthor Commented:
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
 
E J PopeAuthor Commented:
Thanks Jim and Sharath.  Your help was invaluable to my project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.