Post reply

Newbie: How to Import data into table?

  • 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

  • January 9, 2004 at 4:50 am

    #489322

    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

  • January 9, 2004 at 8:15 am

    #489386

    Can i automate the Import process?

  • January 9, 2004 at 8:44 am

    #489389

    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

  • January 9, 2004 at 8:52 am

    #489391

    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

  • January 9, 2004 at 9:12 am

    #489399

    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.

  • January 9, 2004 at 10:02 am

    #489420

    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

  • January 9, 2004 at 11:52 am

    #489435

    Manny, it's a security issue.

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

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



    Once you understand the BITs, all the pieces come together

  • January 9, 2004 at 12:58 pm

    #489452

    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!

  • January 10, 2004 at 6:56 am

    #489513

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

     

    Manny

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

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