SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

SQL to APS Automation

By Alain Dormehl,

-- SMP SQL Server to APS Import Automation
-- Alain Dormehl
-- July 2015
-- Twitter: APSolutely
-- These scripts have been tested on all major releases of SQL Server from 2008 R2 and up. 
-- They can also be automated to loop through all tables and execute the master..xp_cmdshell
-- Destination APS Appliance AU3, running over INFINIBAND 
-- This is hands down the quickest way to transfer large amounts of tables as quickly as possible 
-- from any SMP sql environment into the APS. 
--******************************************************************************
--*   Copyright (C) 2015 Alain Dormehl
--*   All rights reserved. 
--*
--*
--*   You may alter this code for your own *non-commercial* purposes. You may
--*   republish altered code as long as you include this copyright and give due credit. 
--*
--*
--*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
--*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
--*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--*   PARTICULAR PURPOSE. 
--*
--******************************************************************************
Script has been tested on SQL 2008, SQL 2008 R2, SQL 2012, SQL2014 and SQL2016 CTP2.1
Complete the variables in the variable declaration section. Script will out put a table with the BCP queryout statement for all tables, PDW Create Table Statement and DWLoader.exe statement to import the data. 
  1. Either using master..xp_cmdshell loop through all the BCP statements to create flat text files. 
  2. Run the Create Table Statement on the PDW. 
  3. Using Powershell or CMD execute the DWLoader.exe scripts to import data in to appliance. 

Hope this query works as well for you as it has for me, saved me hour and hours of work when trying to import databases with 300+ tables. Much quicker and easier than creating SSIS package. 

Total article views: 800 | Views in the last 30 days: 2
 
Related Articles
SCRIPT

Missing Index Script with Create Statement

standard missing index script but has a create statement to copy and past

SCRIPT

Scripting CREATE INDEX Statements Automatically From Your Tables

Stored proc for scripting out full CREATE INDEX statements from your table indexes with all filegrou...

SCRIPT

Create MERGE statements with data!

A script to create MERGE statements with data for specific tables.

FORUM

Need Help scripting an UPDATE statement

Need help creating a script for an UPDATE statement w/multiple tables GROUP BY condition

FORUM

Create Login Script

Error with Create Login Script

Tags
aps    
automation    
bcp    
create table    
ctas    
dwloader    
dwloader.exe    
infiniband    
pdw    
 
Contribute