Technical Article

SQL to APS Automation

,

-- 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. 

-- 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. 
--*
--******************************************************************************
--VARIABLE DECLARATION 
DECLARE@distributionVARCHAR(25)= 'id'--LEAVE BLANK FOR REPLICATE,HOW DO YOU WANT TO DISTRIBUTE IF IT IS A PDW  
DECLARE@pdwIndexVARCHAR(50)= 'id'--LEAVE BLANK FOR HEAP, COLUMN NAME FOR CI AND CCI FOR CLUSTERED COLUMN STORE
DECLARE@sourceDBVARCHAR(50)= 'TestDB'--DATABASE THAT YOU WANT TO CREATE BCP OUTPUTS FOR    
DECLARE@OverrideCharCHAR(1)         = '$'--CERTAIN SPECIAL CHARACTERS CANNOT BE USED IN THE BCP QUERYOUT TXT FILE (I.E '$')
DECLARE@DirectoryVARCHAR(256)    = 'xx:\BCP\'--DIRECTORY WHERE BCP QUERYOUT WILL EXPORT AND DWLOADER.EXE WILL USE TO IMPORT
DECLARE@ServerVARCHAR(50)= 'SMPSQL'--SMP SQL SERVER INSTANCE NAME OR IP
DECLARE@UsernameVARCHAR(50)= 'TestUser'--USERNAME FOR SMP SQL ENVIRONMENT, LEAVE EMPTY FOR WINDOWS AUTHENTICATE
DECLARE@PasswordVARCHAR(50)= 'Password'--PASSWORD FOR SMP SQL ENVIRONMENT, LEAVE BLANK FOR WINDOWS AUTHENTICATE
DECLARE@PDWNameVARCHAR(100)= 'PDW1-SQLCTL01'--USE PDW DNS NAME, DWLOADER.EXE IS NOT OVERLY FOND OF IP. SHOULD ALREADY BE CONFIGURED FOR IB
DECLARE@PDWFinalTableVARCHAR(150)= 'TestDB'--NAME OF THE FINAL TABLE IN THE APS
DECLARE@ImportMethodVARCHAR(50)= 'reload'--DWLOADER.EXE IMPORT METHOD (RELOAD, APPEND)
DECLARE@PDWUsernameVARCHAR(50)= 'TestUser'
DECLARE@PDWPasswordVARCHAR(50)= 'Password'

;WITH CTE1 
AS
(
SELECT 
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY TABLE_SCHEMA) AS ID,
TABLE_NAME,
TABLE_SCHEMA,
TABLE_CATALOG,
ColumnData_List,
CreateTableSQL = 'USE '+ @PDWFinalTable +' ; CREATE TABLE [' + @PDWFinalTable + '].[' + TABLE_SCHEMA + '].' + CreateTableSQL 
FROM 
(
SELECT  so.name AS TableName, 
id AS ObjectID,
o.list AS ColumnData_List,
CreateTableSQL = '[' + so.name + '] (' 
+ CASE WHEN RIGHT(RTRIM(LTRIM(o.list)),1) = ',' THEN SUBSTRING(o.list,1,LEN(o.list) - 2) ELSE o.list END 
+ ')' 
+ ' WITH (DISTRIBUTION = ' + CASE WHEN @distribution <> '' THEN  + 'HASH(' + @distribution + ')' ELSE 'REPLICATE' END  
+ CASE WHEN @pdwIndex = '' THEN ')'
   WHEN @pdwIndex = 'CCI' THEN ' , CLUSTERED COLUMNSTORE INDEX)' 
   WHEN o.list LIKE '%' + @pdwIndex + '%' THEN ' ,CLUSTERED INDEX(' + @pdwIndex + '))' 
   ELSE ')'  END 
FROM  sysobjects so
CROSS APPLY
    (SELECT 
'[' + COLUMN_NAME + '] ' 
+ DATA_TYPE 
+ CASE DATA_TYPE WHEN 'sql_variant' THEN ''
 WHEN 'text' THEN ''
 WHEN 'ntext' THEN ''
 WHEN 'xml' THEN ''
 WHEN 'decimal' THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
  ELSE COALESCE('(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '8000' ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END +')','') END 
  + ' ' 
  + (CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END) + 'NULL ' 
  + ', ' 
    FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = so.name
    ORDER BY ORDINAL_POSITION 
    FOR XML PATH('')) o (list)
CROSS APPLY
    (SELECT '[' + COLUMN_NAME + '], '
     FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     ORDER BY ORDINAL_POSITION
     FOR XML PATH('')) j (list)
WHERE   xtype = 'U' AND name NOT IN ('dtproperties')
) AS Tablez
LEFT JOIN INFORMATION_SCHEMA.TABLES tblz
ON Tablez.TableName = tblz.TABLE_NAME
),
CTE2
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY TABLE_SCHEMA) AS ID,
TABLE_NAME,
TABLE_SCHEMA,
TABLE_CATALOG,
list AS BCPOut_SelectColumn_List, 
SelectSQL = 'SELECT ' + CASE WHEN RIGHT(RTRIM(LTRIM(list)),1) = ',' THEN SUBSTRING(list,1,LEN(list) - 2) ELSE list END 
+ ' FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM 
(
SELECT  o.list,
name 
FROM  sysobjects so
CROSS APPLY
    (SELECT CASE WHEN DATA_TYPE IN ('datetime2','datetime') THEN 'CONVERT(VARCHAR(8),[' + COLUMN_NAME + '],112) AS [' + COLUMN_NAME + '] , '
 ELSE '[' + COLUMN_NAME + '] , ' END 
    FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = so.name
    ORDER BY ORDINAL_POSITION 
    FOR XML PATH('')) o (list)
WHERE   xtype = 'U' AND name NOT IN ('dtproperties')
) AS Tablez LEFT JOIN INFORMATION_SCHEMA.TABLES tblz 
ON Tablez.name = tblz.TABLE_NAME 
)
SELECT 
TABLE_NAME = CTE1.TABLE_SCHEMA + '.' + CTE1.TABLE_NAME,
BCP_CMD = 'BCP "' + SelectSQL + '" queryout ' + @Directory + REPLACE(CTE1.TABLE_NAME,@OverrideChar,'') + '.txt -S ' + @Server + ' ' + CASE WHEN @Username + @Password = '' THEN '-T ' ELSE ' -U ' + @Username + ' -P ' + @Password  END + ' -e ' + @Directory + 'error.txt -E -k -w -t "~|~" -r \n',
DWL_CMD = 'dwloader.exe -E -M '+ @ImportMethod + ' ' + CASE WHEN @PDWUsername + @PDWPassword = '' THEN '-W' ELSE '-U ' + @PDWUsername + ' -P ' + @PDWPassword END + ' -e UTF16 -i ' + @Directory  + REPLACE(CTE1.TABLE_NAME,@OverrideChar,'') + '.txt -S ' + @PDWName + ' -T [' + @PDWFinalTable + '].[' + CTE1.TABLE_SCHEMA + '].[' + CTE1.TABLE_NAME + '] -R ' + @Directory + 'error.bad -t 0x7E0x7C0x7E -r 0x0D0x0A',
CTE1.CreateTableSQL AS PDWCreateTable
FROM CTE1 INNER JOIN CTE2
ON CTE1.TABLE_SCHEMA + '.' + CTE1.TABLE_NAME = CTE2.TABLE_SCHEMA + '.' + CTE2.TABLE_NAME

/********************************OUTPUT SAMPLE **********************************************/--Table_Name : dbo.database_file

--BCP_CMD : BCP "SELECT [filegroup_id] , [sequence] , [root_path] , [is_add_from_alter] , [percent_allocated_space] FROM [DWConfiguration].[dbo].[database_file]" queryout xx:\BCP\database_file.txt -S SMPSQL  -U TestUser -P Password -e xx:\BCP\error.txt -E -k -w -t "~|~" -r \n

--DWL_CMD : dwloader.exe -E -M reload -U TestUser -P Password -e UTF16 -i xx:\BCP\database_file.txt -S PDW1-SQLCTL01 -T [TestDB].[dbo].[database_file] -R xx:\BCP\error.bad -t 0x7E0x7C0x7E -r 0x0D0x0A

--PDWCreateTable : USE TestDB ; CREATE TABLE [TestDB].[dbo].[database_file] ([filegroup_id] int NOT NULL , [sequence] int NOT NULL , [root_path] nvarchar(1000) NOT NULL , [is_add_from_alter] bit NOT NULL , [percent_allocated_space] float NULL) WITH (DISTRIBUTION = HASH(id) ,CLUSTERED INDEX(id))

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)