This post was authored by Jos de Bruijn, Senior Program Manager, SQL Server. A release the magnitude of SQL Server 2016 deserves a new sample. AdventureWorks, which has been around since the SQL Server 2005 days, has had a good run, but it is time for an upgrade. Wide World Importers is the new sample for SQL Server.
It illustrates how the rich SQL Server feature set can be used in a realistic database. It does not attempt to use every single SQL Server feature, as that would not be realistic. It also showcases the key SQL Server 2016 capabilities and performance enhancements. Latest release of the sample: Documentation for the sample: The sample is structured as follows:. WideWorldImporters is the main database for transaction processing (OLTP – OnLine Transaction Processing) and operational analytics (HTAP – Hybrid Transactional/Analytics Processing). Here are some examples of the use of SQL Server capabilities with this database:.
Download SSIS 2012 – Sample Package (Process Locked file) Conclusion. Processing and detecting locked files in SSIS can be tricky but using small C# script it can save you some headache. Download Advanced File System Task to try many options not available in native File System Task.
Real-time operational analytics of sales data is enabled through the use of. Archive tables can be for long-term retention, reducing storage cost and improving manageability. is used to keep track of query performance. are used to conveniently keep track of the history of reference data, as well as some of the main entities. is used to enable AJAX calls to some of the key tables, and also to extend the relational schema to record such things as application settings and user preferences. Advanced security features like, and are used to secure data. is used to optimize the performance of and to optimize ingestion of sensor data.
are used to reduce the storage footprint of large tables with insert-only workload. is used to improve the manageability of large tables. For more detail, see. WideWorldImportersDW is the main database for data warehousing and analytics (OLAP – OnLine Analytics Processing). The data in this database is derived from the transactional database WideWorldImporters, but it uses a schema that is specifically optimized for analytics.
Here are some examples of the use of SQL Server capabilities with this database:. are used to reduce the storage footprint and improve query performance for the fact tables. is used to correlate data in the local database with a public data set in Azure Blog storage. is used to improve the performance of the ETL process. is used to improve manageability of the fact tables, which can grow very large in a data warehouse. For more detail, see. A package, Daily ETL.ispac, is used to move data from the OLTP database WideWorldImporters to the OLAP database WideWorldImportersDW.
The package is designed to use bulk T-SQL statements wherever possible to enhance performance. For details about the ETL workflow, see the. The sample also includes a number of scripts that can be used to explore some of the features used in the sample database. In addition, the sample includes two workload drivers, which are small applications that simulate a workload running against the database. The databases contain sample sales and purchases data starting January 1st, 2013, until May 31st, 2016. The sample includes procedures to generate more sample data, so you can always bring the sample up to the current date.
For details, see. The data size is limited to keep the download size reasonable. If you desire a sample database with a larger data size, use the to create a new sample database, and tweak the parameters in step 6 to increase the data size. Get started. Download and install a free trial of or configure a test environment using an. The is free if you sign up for the free Visual Studio Dev Essentials program. and review the.
![Sample Sample](/uploads/1/2/5/6/125629902/399210961.png)
Send any feedback on the sample to:.
Recently I wanted to download historical stock information from the internet and store this information into a data warehouse. Using the Task Factory Download File Task I was able to dynamically download thousands of csv files with historical stock information and trends for my data warehouse. The Download file task allows you to download files over an HTTP connection from inside an SSIS Package and it also has support for HTTPS, I will show you where to enter credentials. If you want to walk through this example and don’t currently have Task Factory you can download a free 14 day trial here: Demo Time: So in this example I am going to download a CSV File from Yahoo Finance with historical price information for Fed Ex.
Create a new package in SSIS and pull the Task Factory Download File Task into the control flow. Now we need to create our HTTP Connection manager. Inside the Download File Task select “Create New HTTP Connection”. Creating the connection manager is very easy, especially if you are not using HTTPS.
Server URL: (This is the domain that is hosting the file). Use Credentials: (Select this box and proceed with your username and password if credentials are required. Test Connection: (Test Connection before proceeding.). For Server URL I used:.
Click Ok. Connection manager has now been created successfully. Now we need the exact location of the file we want to download. If you follow the URL below you will see the stock history for Fed Ex. At the bottom of the page there is a download button. (See next screenshot).
Now that we have the exact location of the file we can finish setting up the Download File Task. Paste the file location into the section “Enter Path of File To Download:”. Note I am simply specifying a Text Value for this walkthrough. You can use variables and expressions with this task to make it as dynamic as you need. The final step in this process is to specify the location where the file will be stored once downloaded. Once again I am using “Text Value”. “Enter File Path For Local File:” Enter the specific file location.
(Screenshot) Now execute the package and there you go! Thanks for Looking.