Newbie: How to Import data into table?

Question

Newbie: How to Import data into table?

Manny Singh

Valued Member

Points: 51
More actions
January 8, 2004 at 8:09 pm

#162939

Hi Guys,
I am a newbie to SQL Server. I will appreciate any feedback regarding
the following question:
Ques: Everday i get a pipe delimited file from company. I need to schedule job to import the file to the table in the database. I want the SQL to email me whenever the job fails and completes. The server does not have any Outlook profile set up. I can configure Outlook Express to connect to the Exchange Server.
Thanks
Manny

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Definitely two distinct tasks. To get the data in, the easiest way is DTS. If you right click on the table or database, you'll get an option to import data. Follow the wizard. Would recommend trying in a test environment first and don't forget that you'll need to verify the quality of the data.

For email, the easiest/standard way is to install Outlook. SQL doesn't work with OE.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Manny Singh Valued Member Points: 51 More actions · Answer 2

Manny Singh

Valued Member

Points: 51

January 9, 2004 at 8:15 am

#489386

Can i automate the Import process?

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

Little "Import" Survey (let's here from alot of you, feel free to add or edit) :

Maybe we'll do another for "Exporting"

1. Do you understand (choose any / all):

A. DTS B. BULK INSERT C. Linked Servers D. OPENROWSET|QUERY E. TextCopy

2. Which of the above do you use? (Order by source, preference):

A. DTS B. BULK INSERT C. Linked Servers D. OPENROWSET|QUERY E. TextCopy

3. Preference of implementation? (Free form answer) (Create, Maintain, Automate Execution):

Enterprise Mngr., Q/A, OSQL, Jobs, SPs etc.

4. Why? (purely optional)

Once you understand the BITs, all the pieces come together

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

1. Do you understand (choose any / all): B,C,D,E, little a

2. Which of the above do you use? (Order by source, preference):

Usually into temp tables, then manipulate.

Text - Bulk Insert, Blobs use TextCopy

SQL to SQL - OpenRowset, Linked Servers

other DB (OLEDB, ODBC) - OpenRowset, Linked Servers

3. Preference of implementation? (Free form answer) (Create, Maintain, Automate Execution):

Write SP in Q/A to perform action, then create job to execute SP as needed.

4. Why? (purely optional)

Like the control of T-SQL with witch I'm most familiar & comfortable.

Once you understand the BITs, all the pieces come together

cross SSCommitted Points: 1735 More actions · Answer 5

You could do a a DTS package using a connection to your database and the file as source with a Transform Data Task between them, then schedule the package to run as a job.

Manny Singh Valued Member Points: 51 More actions · Answer 6

Hi Cross,

i was able to create a DTS package and schedule the job to run every day. However when the job ran, it failed and gave the following error.

Executed as user: LHWDEV\Administrator. ...: Copy Data from 01Apr03 to [Hotel Master].[dbo].[HotelBrochuresXREF] Step DTSRun OnError: Copy Data from 01Apr03 to [Hotel Master].[dbo].[HotelBrochuresXREF] Step, Error = -2147467259 (80004005) Error string: Error opening datafile: The system cannot find the path specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 3 (3); Provider Error: 3 (3) Error string: Error opening datafile: The system cannot find the path specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: Copy Data from 01Apr03 to [Hotel Master].[dbo].[HotelBrochuresXREF] Step Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'Copy Da... Process Exit Code 1. The step failed.

The source is a text file on a networked drive. Security is set to allow everyone Full Control..

Any Help on this...

Thanks

Manny

ThomasH SSCertifiable Points: 7437 More actions · Answer 7

Manny, it's a security issue.

I know it's not exactly the same, but chack out

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=93674#bm93678

Once you understand the BITs, all the pieces come together

cross SSCommitted Points: 1735 More actions · Answer 8

Many people run in to security-types when running DTS packages as jobs, as ThomasH says. I've had it happen to me a couple times. I have jobs owned by SA that fail when owned by another user. There may a better practice than having the jobs owned by SA though. Maybe a user with SA privileges but a different name or something.

Also, maybe you can post in the Data Warehousing/DTS forums to get answers from people who are much more knowledgable about DTS than I am!

Manny Singh Valued Member Points: 51 More actions · Answer 9

Thank you all for your reply. This indeed was a great help.

Manny