Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Function Generate-SQL2CSVData
- {
- [CmdletBinding()]
- param(
- [Parameter(Position=0,mandatory=$true)] [String] $sSecurePasswordDataFilePath,
- [Parameter(Position=0,mandatory=$true)] [String] $sSQLConfigPath
- )
- [xml]$SQLConfig = Get-Content $sSQLConfigPath
- foreach( $DBConfig in $SQLConfig.SQLServerConfig)
- {
- $sSQLServer = $DBConfig.SQLServer
- $sDatabase = $DBConfig.DatabaseName
- $sUserName = $DBConfig.UserName
- $sSecurePassword = $DBConfig.Password
- $SqlQuery = $DBConfig.SQLQuery
- $CSVFileName = $DBConfig.CSVFileName
- }
- #Check if the SecurePassword Data file exists or if the contents of the Secure Password file a
- <# if(!(Test-path $sSecurePasswordDataFilePath) -or ((Get-Content $sSecurePasswordDataFilePath).Length -eq 0))
- {
- $creds=get-credential
- $SNowUser = $creds.username
- $bytes = ConvertFrom-SecureString $creds.password
- $bytes | out-file $sSecurePasswordDataFilePath
- }
- $encrypt = Get-Content $sSecurePasswordDataFilePath -ErrorAction Stop #>
- $sPassword = ConvertTo-SecureString -string $sSecurePassword -ErrorAction Stop
- # Create the SqlCredential object
- $sqlCred = New-Object System.Data.SqlClient.SqlCredential($sUserName,$sPassword)
- ## - Connect to SQL Server using non-SMO class 'System.Data':
- $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
- $SqlConnection.ConnectionString = "Server=$SQLServer;Database=$Database"
- #;Integrated Security = True
- $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
- $SqlCmd.CommandText = $SqlQuery;
- $SqlCmd.Connection = $SqlConnection;
- ## - Extract and build the SQL data object '$DataSetTable':
- $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
- $SqlAdapter.SelectCommand = $SqlCmd;
- $DataSet = New-Object System.Data.DataSet;
- $SqlAdapter.Fill($DataSet);
- $DataSetTable = $DataSet.Tables["Table"];
- $DataSetTable | Export-Csv -NoTypeInformation $CSVFileName
- $SqlConnection.Close()
- }
- I am generating the password and saving it in the SQLConfig File
- $password = read-host -prompt "Enter your Password"
- $secure = ConvertTo-SecureString $password -force -asPlainText
- $bytes = ConvertFrom-SecureString $secure
- $bytes
- <SQLServerConfig>
- <SQLServer>Instance</SQLServer>
- <DatabaseName>Master</DatabaseName>
- <UserName>sa</UserName>
- <Password>01000000d08c9ddf0115d1118c7a00c04fc297eb0100000084deba1b2c5da142b5901977ebc2d85f0000000002000000000003660000c0000000100000006eb6ee7491d60efc85452441b36fd4640000000004800000a00000001000000058acf811c005054da91f01fdab59d23018000000550f8316233ea420496c2dbb7800265dfffd298becacc51814000000c04e8425844fef154622647c89e25e7d28a3c498</Password>
- <SQLQuery>SELECT * FROM sysdatabases</SQLQuery>
- <CSVFileName>D:\SQLData2CSV\Export2CSV02.csv</CSVFileName>
- </SQLServerConfig>
Add Comment
Please, Sign In to add comment