Export Large SQL Tables into Multiple Text Files

A requirement has been arisen to export large SQL tables into a cloud storage. Nowadays, there are many ETL tools and cloud services in the market that support this process. However, in this particular scenario, the requirement was to develop a custom proc to export a given table into multiple CSV files based on a batch size (i.e. specific number of rows per file)

Following proc generates a dynamic BCP command and then execute it using xp_cmdshell system proc.

CREATE PROCEDURE [dbo].[sp_Export_Large_SQL_Tables]
 @Server  VARCHAR(200)
 , @DBName VARCHAR(200)  
 , @TableName VARCHAR(200)  
 , @Delimiter VARCHAR(200) = '|'  -- Output File Delimiter
 , @OutputExt VARCHAR(200) = 'csv' -- Output File Extension
 , @OutputDir VARCHAR(200) 
 , @LogDir VARCHAR(200) = null
 , @OrderByCol VARCHAR(200) = null
 , @BatchSize INT = 500000
AS
/**************************************************************************  
-- Name   : [dbo].[sp_Export_Large_SQL_Tables]
-- Desc   : 
-- Notes  : 
-- Dependencies         : bcp.exe
**************************************************************************  
-- Ve   Date        Author     Description  
-- --   --------    -------    --------------------------- 
-- 1    2018-05-31 Isura Silva  Created
*************************************************************************/
BEGIN
 SET NOCOUNT ON
 DECLARE @vcStep VARCHAR(max) = '' -- Store Custom error message
 DECLARE @vcSQL VARCHAR(max)  = '' -- Store execution command
 DECLARE @Proc_Name varchar(255) = OBJECT_NAME(@@PROCID) -- Automatically picks the proc name
 ------- 
 SET @LogDir = ISNULL(@LogDir,@OutputDir) ;
 SET @Server = ISNULL(@Server, 'localhost');
 SET @Delimiter = ISNULL(@Delimiter, '|');
 SET @OutputExt = ISNULL(@OutputExt, '.csv'); -- Output File Extension
 IF @OrderByCol IS NULL OR @OrderByCol = ''
  BEGIN
  SET @vcSQL = 'SELECT TOP 1 @OrderByCol = COLUMN_NAME FROM ' 
   + @DBName + '.[INFORMATION_SCHEMA].[COLUMNS] WHERE CONCAT([TABLE_SCHEMA],''' + '.' +''',[TABLE_NAME]) = ''' 
   + @TableName + ''' OR [TABLE_NAME] = ''' + @TableName + ''''
  -- PRINT @vcSQL;
  DECLARE @vcStep2 NVARCHAR(max) = CAST(@vcSQL AS NVARCHAR(MAX))
  EXEC sp_executesql @vcStep2, @params = N'@OrderByCol VARCHAR(200) OUTPUT', @OrderByCol = @OrderByCol OUTPUT
  END
 ------- 
 DECLARE @BCPexe VARCHAR(200) = CASE 
  -- WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'unknown' -- 'SQL2008'
  -- WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'unknown' -- 'SQL2008 R2'
  -- WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'unknown' -- 'SQL2012'
  -- WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'unknown' -- 'SQL2014'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe' --'SQL2016'     
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe' --'SQL2017' 
  ELSE 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe'
  END

 DECLARE @SQLQuery VARCHAR(8000) = '';
 DECLARE @SQLbcp  VARCHAR(8000) = '';
 DECLARE @OutputPath VARCHAR(200);
 DECLARE @LogPath VARCHAR(200);
 -------
 DECLARE @SQLRowCount NVARCHAR(4000)= N'';
 DECLARE @CurrentRowCount INT = 0;
 DECLARE @TableRowCount INT;
 BEGIN TRY 
 Print '---------------------------------------------------------------------------------------------------------------'
  -- Get the row count of SQL table
  SET @SQLRowCount = CAST(('SELECT @TableRowCount = COUNT(*) FROM ' + @DBName + '.' + @TableName) AS nvarchar(4000)) 
  EXEC sp_executesql @SQLRowCount, @params = N'@TableRowCount INT OUTPUT', @TableRowCount = @TableRowCount OUTPUT
  --PRINT @SQLRowCount
 
  WHILE (@CurrentRowCount < @TableRowCount) 
  BEGIN 
   -- Pupulate the input query for BCP command based on the batch size (default batch is 500K rows)
   SET @SQLQuery = 'SELECT * FROM ' + @DBName + '.' + @TableName + ' ORDER BY ' + quotename(@OrderByCol) + ' ASC '
   + ' OFFSET ' + CAST(@CurrentRowCount as VARCHAR(100)) + ' ROWS FETCH NEXT ' + CAST(@BatchSize AS VARCHAR(100)) 
   + ' ROWS ONLY'

  ---------------------------------------- 
   SET @OutputPath =  @OutputDir + '\' + @TableName + '_' + CAST(@CurrentRowCount as VARCHAR(100)) + '-' + CAST((@CurrentRowCount+ @BatchSize) as VARCHAR(100)) + '.csv';
   SET @LogPath = @LogDir + '\' + @TableName + '.log';
   -- Pupulate BCP command
   SET @SQLbcp = 'CALL ' +  quotename(@BCPexe, '"') + ' ' 
    + '"' + @SQLQuery + '"'
    + ' queryout ' + quotename(@OutputPath, '"')
    + ' -S ' + quotename(@Server, '"') 
    + ' -d ' + quotename(@DBName, '"') 
    + ' -t ' + quotename(@Delimiter, '"') 
    + ' -e ' + quotename(@LogPath, '"') 
    + ' -T -c -C 65001 -q '

   PRINT @SQLbcp
   DECLARE @result int;  
   
   --------
   EXEC @result = xp_cmdshell @SQLbcp , NO_OUTPUT;  
   IF (@result = 0 and (@SQLbcp <> '' OR @SQLbcp IS NOT NULL))  
      PRINT 'Success'  
   ELSE  
    PRINT 'Failure'
    PRINT @SQLbcp;

  SET @CurrentRowCount = @CurrentRowCount + @BatchSize 
  END
  ----------------------------------------
  Print '---------------------------------------------------------------------------------------------------------------'
 END TRY  
 BEGIN CATCH  
  PRINT ERROR_MESSAGE() 
        RAISERROR(@Proc_Name,16,1)
 END CATCH
END

e.g. Call procedure - TSQL

EXEC [dbo].[sp_Export_Large_SQL_Tables] @Server = 'Localhost'
 , @DBName = 'Insight'
 , @TableName = 'dbo.YOU_Detailed'
 , @Delimiter = '|' 
 , @OutputExt = 'csv'
 , @OutputDir = 'E:\Insight\Export'
 , @LogDir = null
 , @OrderByCol = null
 , @BatchSize = 500000

e.g. Call  procedure - PowerShell

$ServerInstance = "Localhost"
$Database       = "Insight"
$TableName      = "dbo.YOU_Detailed"
$OutputDir      = "E:\Insight\Export"
   
## Export SQL tables to CSV
$SQLcmd  = "EXEC [dbo].[sp_Export_Large_SQL_Tables] @Server = " + "'" + $ServerInstance + "'" `
    + ", @DBName = " + "'" + $Database + "'" `
    + ", @TableName = " + "'" + $TableName + "'" `
    + ", @Delimiter = " + "'|'" `
    + ", @OutputExt = " + "'csv'" `
    + ", @OutputDir = " + "'" + $OutputDir + "'" `
    + ", @LogDir = " + "null" `
    + ", @OrderByCol    = " + "null" `
    + ", @BatchSize = " + "500000" `
 
Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $SQLcmd  -Database "Utils" -QueryTimeout 0 -verbose 

Unzip all the zip files in a folder + PowerShell

PARAM 

(
    [string] $ZipFilesPath = "E:\TEMP\TEMP DSE",
    [string] $UnzipPath = "E:\TEMP\TEMP DSE"
)
$Shell = New-Object -com Shell.Application
$Location = $Shell.NameSpace($UnzipPath)
 $ZipFiles = Get-Childitem $ZipFilesPath -Recurse -Include *.zip
 
$progress = 1
foreach ($ZipFile in $ZipFiles) {
    Write-Progress -Activity "Unzipping to $($UnzipPath)" -PercentComplete (($progress / ($ZipFiles.Count + 1)) * 100) -CurrentOperation $ZipFile.FullName -Status "File $($Progress) of $($ZipFiles.Count)"
    $ZipFolder = $Shell.NameSpace($ZipFile.fullname)
 
 
    $Location.Copyhere($ZipFolder.items(), 1040)
    $progress++
}

PowerShell - Get System information

Get PowerShell Information
$Host

Get Computer System Information

1 2 3 4 5

# Useful Computer System Info. Get-WmiObject -Class Win32_ComputerSystem -ComputerName . | ` SELECT PSComputerName, BootupState, Status, Domain, UserName, Manufacturer, ` NumberOfLogicalProcessors, NumberOfProcessors, SystemType, ` @{n="Memory(MB)";e={[math]::ROUND($_.TotalPhysicalMemory / ( 1024 * 1024) -as [Float],2)}} #,*

Get Operating System Information

1 2 3 4 5 6 7 8 9 10 11 12

# Useful Operating System Info. Get-WmiObject -Class Win32_OperatingSystem -ComputerName DI21 | ` Select-Object -Property PSComputerName, Caption, CodeSet, CountryCode, CreationClassName, CSCreationClassName, CSDVersion, CurrentTimeZone, EncryptionLevel, ForegroundApplicationBoost, InstallDate, LastBootUpTime, LocalDateTime, Locale, Manufacturer, MaxNumberOfProcesses, MaxProcessMemorySize, MUILanguages, NumberOfLicensedUsers, NumberOfProcesses, NumberOfUsers, OperatingSystemSKU, OSArchitecture, OSLanguage, OSProductSuite, OSType, ProductType, RegisteredUser, SerialNumber, ServicePackMajorVersion, ServicePackMinorVersion, SizeStoredInPagingFiles, SuiteMask, SystemDirectory, Version, PSStatus, BuildNumber, BuildType, @{n="TotalVirtualMemorySize(MB)";e={[math]::ROUND( $_.TotalVirtualMemorySize / (1024*1024) -as [Float], 2)}}

Get Processes' Information

1 2 3 4 5 6 7 8 9

Clear-Host Get-Process -ComputerName . | SELECT ProcessName, ` @{n="WS(MB)";e={[math]::ROUND($_.WS / ( 1024 * 1024) -as [Float],0)}}, ` # WS, @{n="VM(MB)";e={[math]::ROUND($_.VM / ( 1024 * 1024) -as [Float],0)}}, ` # VM, @{n="PM(MB)";e={[math]::ROUND($_.PM / ( 1024 * 1024) -as [Float],0)}}, ` # PM, @{n="CPU";e={[math]::ROUND($_.CPU -as [Float],0)}}, ` # CPU, Id, Path | ` Sort-Object "WS(MB)" -Descending | ` Format-Table -AutoSize

Get Logical Disk Information

1 2 3 4 5 6

# Useful logical Disk Info. Get-WmiObject -Class Win32_LogicalDisk -ComputerName DI21 | Select DeviceID, VolumeName, DriveType, ProviderName,FileSystem, Compressed,MediaTYpe, @{n="FreeSpaceGB";e={[math]::ROUND($_.FreeSpace / (1024*1024*1024) -as [Float],2)}}, @{n="SizeGB";e={[math]::ROUND($_.Size / (1024*1024*1024) -as [Float],2)}}, CreationClassName, VolumeDirty, Description, FilesystemSize, ErrorDescription

Get Installed Program Information

Get-WmiObject -Class Win32_Product -Computer . | ` 
    sort Name | Format-Table  Name, Version, Vendor

Get other system Information

Get-WmiObject -Class Win32_LogonSession -ComputerName DI21 

Get-WmiObject -Class Win32_LocalTime -ComputerName . 

Get-WmiObject -Class Win32_Service -ComputerName . | `
    Format-Table -Property Status,Name,DisplayName -AutoSize -Wrap

Get-CimInstance  Win32_Service
Get-CimInstance  Win32_Share
Get-CimInstance  Win32_ShareToDirectory
Get-CimInstance  Win32_StartupCommand
Get-CimInstance  Win32_SystemAccount | SELECT Caption, Name, 
    SID, Status| Format-Table -AutoSize -Wrap
Get-CimInstance  Win32_SystemDevices 
Get-CimInstance  Win32_SystemLoadOrderGroups
Get-CimInstance  Win32_SystemNetworkConnections
Get-CimInstance  Win32_SystemOperatingSystem
Get-CimInstance  Win32_SystemPartitions
Get-CimInstance  Win32_SystemServices
Get-CimInstance  Win32_SystemTimeZone
Get-CimInstance  Win32_SystemUsers
Get-CimInstance  Win32_UserAccount

Referance : https://msdn.microsoft.com/en-us/library/dn792258(v=vs.85).aspx

Powershell, Infile search and file copy between the servers in same domain

Find a keyword within multiple files in a folder.

$PATH = "D:\Veeva Activity\WE_00DA0000000Ci0mMAC_123\"
$FILES = Get-ChildItem -Path $path  # -Name user.csv*

Foreach ($FILE IN $FILES )
{
Get-Content $PATH$FILE -First 1   | Where-Object { $_ -like '*lm_Presentation_Version_vod*' }

$FILE
}

File copy between the servers in the same domain
$SourcePath = "\\Server1\Data Input Area"
$DestinationPath  = "\\Server2\Data Input Area"

$Folders = Get-ChildItem -Path $SourcePath  -Name Aver*
FOREACH ($Folder in $Folders)
{
#$DestinationPath + "\"+ $Folder
#Get-ChildItem -Path $SourcePath\$Folder | Where-Object{!($_.PSIsContainer)}
Copy -Path $SourcePath\$Folder\* -Destination $DestinationPath\$Folder | Where-Object{!($_.PSIsContainer)} # Exclude Folders
}

Identify and disconnect / log off remote RDP sessions

Run QWINSTA to extract the RDP session information

• QWINSTA /SERVER:servername

If the session exists, read the username and session ID.

• To disconnect user 

    TSDISCON /SERVER:servername sessionID

• To log-off user / kill the session

    RWINSTA /SERVER:servername sessionID


Ref : http://discoposse.com/2012/10/20/finding-rdp-sessions-on-servers-using-powershell/

Function to Get Folder Sizes in Powershell

Following Powershell function can be used to get the sizes of given folders.
Folder names hould be given within double quotes seperated by commas.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

function Get-FolderSize { param( $FolderPaths = $null ) [array]$FolderLists = $null foreach ( $FolderPath in $FolderPaths) { $FolderLists += Get-ChildItem $FolderPath -Force -Recurse | Measure-Object -property Length -Sum | select @{n="Path";e={"""" +$FolderPath + """"}} , @{n="Size GB";e={[math]::ROUND($_.Sum / 1GB -as [Float],2)}} , @{n="Size MB";e={[math]::ROUND($_.Sum / 1MB -as [Float],2)}} , @{n="Size KB";e={[math]::ROUND($_.Sum / 1KB -as [Float],2)}} } $FolderLists | Format-Table -AutoSize }

The function rutuns the sizes of given folders in kilobyte (KB), megabyte (MB), gigabyte(GB).

Connect to a SFTP site using Windows Powershell

This post explains how to access  a SFTP server using Windows Powershell commands. There are many file transfer utilities which can be used for this purpose. Here, I use WinSCP  in order to access SFTP server from windows powershell.

1. WinSCP PowerShell Wrapper can be downloaded from https://github.com/dotps1/WinSCP.
2. Import WinSCP Powershell module. $WinSCPFile path variable needs to be set to the WinSCP.psd1 file inside the downloaded folder.

$WinSCPFile = "C:\....\WinSCP-master\WinSCP.psd1"
Import-Module -Name $WinSCPFile -Verbose -ErrorAction Inquire -WarningAction Inquire | Out-null
3. Splat New-WinSCPSessionOptions.
$sessionOptions = @{
HostName = "SFTP-HostName"
UserName = "Username"
Password = "Password"
SshHostKeyFingerprint = "HostKey"
}

To get the SshHostKeyFingerprint, go to \WinSCP-master\NeededAssemblies\ folder and run WinSCP.exe.Then enter HostName, Username, & Password and try connecting to the SFTP site. You will get the SshHostKeyFingerprint when you log in for the first time.

4. Open new WinSCPSession using the splatted parameters.
$session = Open-WinSCPSession -SessionOptions (New-WinSCPSessionOptions @sessionOptions)
 
5. Send a file to SFTP server
Send-WinSCPItem -WinSCPSession $session -LocalPath "C:\localFile.txt" -RemotePath "./remoteDirectory/"
 
6. Get a file from FTP server
Receive-WinSCPItem -WinSCPSession $session -RemotePath "./remoteDirectory/rFile.txt" -LocalPath "C:\localFile.txt"
7. Close SFTP session
Close-WinSCPSession -WinSCPSession $session

Referance: 
https://github.com/dotps1/WinSCP
http://winscp.net/eng/docs/ssh_verifying_the_host_key