SolvedPrivate

BULK INSERT issue

Posted on 2013-10-23
8
49 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

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!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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