Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 automate table update

Posted on 2011-09-26
6
Medium Priority
?
245 Views
Last Modified: 2012-05-12
I need guidance on how best to automate the daily update of a table containing employee info for our company's intranet.
What happens today:
I have automated the first part of this task by scheduling a nightly extract out of our Peoplesoft system.  The resulting EXCEL file is then manually imported into the target SQL Server 2008 database table using the Import and Export Data (32-bit) utility pgm, which drops the existing data before importing the new stuff.  This is done from my PC, obviously.  I then manually verify the load was successful by using the SQL Server 2008 Management Studio and running a query to display the most recently added employee.  This table is a critical table providing employee reporting relationships on our (SharePoint) intranet.
While the entire process only takes a minute or two to complete, I'm wondering if this can be automated somehow.  Any suggestions?
0
Comment
Question by:OGSan
[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
6 Comments
 
LVL 7

Expert Comment

by:mozcullu
ID: 36707650
on the last step of your import export utility there is an option to save that import job as an SSIS package. You can save its definition to filesystem of SQL Server. the second job is a SQL query. This job can be an scheduled SQL Agent job with 2 steps inside. 1st step type will be SQL Server Integration Services package and the second step will be Transact-SQL Script.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36707761
Its good to create SSIS package to get new or updated rows or create Replication
0
 
LVL 11

Expert Comment

by:SThaya
ID: 36708861
Hi,

  Create a Job sechdule in SQL job Agent and sechdule the package in your convinent timings .
 like 2 times in a day
 
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 1

Author Comment

by:OGSan
ID: 36712580
When I step through that Import/Export Utility, it displays a msg saying:
In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it.  To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.
I'm not sure how to get around this problem.
Any suggestions?
0
 
LVL 7

Accepted Solution

by:
mozcullu earned 2000 total points
ID: 36715515
yes that is true.

to overcome this problem for the first step you can use linked server feature of sql and connect your excel file as a linked server to sql server and run a query to import your data to you database. here is an example how to connect excel as a linked server to SQL Server: http://blogs.msdn.com/b/rdas/archive/2008/08/15/excel-as-a-linked-server.aspx After connecing it you can run a distributed query to get data out of excel to SQL Server.

The second step remains the same.
0
 
LVL 1

Author Comment

by:OGSan
ID: 36719825
Thank you for that suggestion, mozcullu.  I'm working with our IT team now to get this done.  Yeehaw!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

721 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