NottyHeadedGeek

Generate CSV from SQLServer

Jun 5th, 2018
68
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Function Generate-SQL2CSVData
  2. {
  3.     [CmdletBinding()]
  4.     param(
  5.         [Parameter(Position=0,mandatory=$true)] [String] $sSecurePasswordDataFilePath,
  6.         [Parameter(Position=0,mandatory=$true)] [String] $sSQLConfigPath
  7.     )
  8.  
  9.  
  10.     [xml]$SQLConfig = Get-Content $sSQLConfigPath
  11.         foreach( $DBConfig in $SQLConfig.SQLServerConfig)
  12.         {
  13.             $sSQLServer = $DBConfig.SQLServer
  14.             $sDatabase = $DBConfig.DatabaseName
  15.             $sUserName = $DBConfig.UserName
  16.             $sSecurePassword = $DBConfig.Password
  17.             $SqlQuery = $DBConfig.SQLQuery
  18.             $CSVFileName = $DBConfig.CSVFileName
  19.         }
  20.  
  21.  
  22. #Check if the SecurePassword Data file exists or if the contents of the Secure Password file a
  23. <# if(!(Test-path $sSecurePasswordDataFilePath) -or ((Get-Content $sSecurePasswordDataFilePath).Length -eq 0))
  24.          {
  25.             $creds=get-credential
  26.             $SNowUser = $creds.username
  27.             $bytes = ConvertFrom-SecureString $creds.password
  28.             $bytes | out-file $sSecurePasswordDataFilePath
  29.          }  
  30.              $encrypt = Get-Content $sSecurePasswordDataFilePath -ErrorAction Stop #>
  31.             $sPassword = ConvertTo-SecureString -string $sSecurePassword -ErrorAction Stop
  32.  
  33.             # Create the SqlCredential object
  34.             $sqlCred = New-Object System.Data.SqlClient.SqlCredential($sUserName,$sPassword)
  35.  
  36.             ## - Connect to SQL Server using non-SMO class 'System.Data':
  37.             $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
  38.             $SqlConnection.ConnectionString = "Server=$SQLServer;Database=$Database"
  39.             #;Integrated Security = True
  40.             $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
  41.             $SqlCmd.CommandText = $SqlQuery;
  42.             $SqlCmd.Connection = $SqlConnection;
  43.            
  44.             ## - Extract and build the SQL data object '$DataSetTable':
  45.             $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
  46.             $SqlAdapter.SelectCommand = $SqlCmd;
  47.             $DataSet = New-Object System.Data.DataSet;
  48.             $SqlAdapter.Fill($DataSet);
  49.             $DataSetTable = $DataSet.Tables["Table"];
  50.            
  51.             $DataSetTable | Export-Csv -NoTypeInformation $CSVFileName
  52.             $SqlConnection.Close()
  53. }
  54.  
  55. I am generating the password and saving it in the SQLConfig File
  56. $password = read-host -prompt "Enter your Password"
  57. $secure = ConvertTo-SecureString $password -force -asPlainText
  58. $bytes = ConvertFrom-SecureString $secure
  59. $bytes
Add Comment
Please, Sign In to add comment