Question PowerShell Scripting

Plus d'informations
il y a 1 an 2 semaines #33242 par Phesheya Hlophe
I have 2 server, 1 is a physical server and the other one is a VPS, I am hosting superset dashboards from the VPS while the physical server is where data land, I want to sync the data from the physical server to the VPS automatically via a powerShell script, I have tried to write the below script can anyone but its not working can anyone help

# connection to remote server
#server IP: 176.57.189.102
#server: VMI690251

$remote_SQLServer = "41.211.48.78\EDCU-LAB"
$local_SQLServer = "176.57.189.102\VMI690251"

$SQLDBName = "Sentinel Surveillance"
$SQLUser = "sentinel"
$SQLPassword = "P@ssw0rd"

# fetch data from local server

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $remote_SQLServer; Database = $SQLDBName; User ID= $SQLUser; Password= $SQLPassword"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = ” SELECT * FROM [COVID_Screening]”
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

# Assign the Sql Command object Sql Adapter object
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.SelectCommand.Connection = $SqlConnection

# Declare DataSet object
$DataSet = New-Object System.Data.DataSet
$DataTable = New-Object System.Data.DataTable

# Execute Sql Adapter object to fill the DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataTable = $DataSet.Tables[0]

#clean COVID_Screening table before cumulative import
$csv_path='C:\Data\Sentinel Surveillance\default\COVID_Screening\data_unformatted.csv'
$temp_path='C:\Data\Sentinel Surveillance\Temp\COVID_Screening_temp.csv'
$final_path='C:\Data\Sentinel Surveillance\COVID_Screening.csv'

$DataSet.Tables[0] | export-csv $csv_path -notypeinformation
Import-Csv $csv_path | Export-Csv $temp_path -Delimiter "|" -NoTypeInformation
(Get-Content $temp_path).Replace(",","").Replace('"',"") | Out-File $final_path


#load covid_screening data into covid database
sqlcmd -S "EDCU-LAB\SQLEXPRESS" -E -Q "DELETE FROM [Sentinel Surveillance].dbo.covid_screening"
sqlcmd -S "EDCU-LAB\SQLEXPRESS" -E -Q "BULK INSERT [Sentinel Surveillance].dbo.covid_screening FROM 'C:\Data\Sentinel Surveillance\COVID_Screening_historical.csv' WITH (DATAFILETYPE = 'widechar',ROWTERMINATOR = '\n',FIRSTROW = 2,FIELDTERMINATOR = '|')"

#
#updating the remote server with fetched data

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $remote_SQLServer; Database = $SQLDBName; User ID= $SQLUser; Password= $SQLPassword"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlQuery = "SELECT * INTO [COVID_Screening2] FROM [dbo].[COVID_Screening]"
$SqlCmd.CommandText = $sqlQuery
$SqlCmd.Connection = $SqlConnection
$sqlCmd.ExecuteNonQuery()
$SqlConnection.Close()

Connexion ou Créer un compte pour participer à la conversation.

Temps de génération de la page : 0.096 secondes
Propulsé par Kunena