What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Course Of The Month
8 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Server 2008 automate table update
Posted on 2011-09-26
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?
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?
[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
2-
2
- +1
6 Comments
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.
Active 3 days ago
Hi,
Create a Job sechdule in SQL job Agent and sechdule the package in your convinent timings .
like 2 times in a day
Create a Job sechdule in SQL job Agent and sechdule the package in your convinent timings .
like 2 times in a day
When I step through that Import/Export Utility, it displays a msg saying:
Any suggestions?
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?
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.
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.
Featured Post
What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 5 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.