Using sql query on ssis server sql job using powershell

I didn't work with the SSIS server. But the principle of running SQL queries from SQL is pretty simple.

You should have proper module or you can use snapin of SQL.

I will continue from the second one.

# If you have an SQL server on your server, so this is the possible path where they can be.$PossiblePaths = 'C:\Program Files\Microsoft SQL Server','C:\Program Files (x86)\Microsoft SQL Server'# Lets check where we have PSProvider.dll and PSSnapins.dll$PossiblePaths | ForEach-Object {    Test-Path -Path $_    {        $SQLPSProvider = (Get-ChildItem -Filter "Microsoft.SqlServer.Management.PSProvider.dll" -Path $_ -Recurse).FullName        $SQLPSSnapIn = (Get-ChildItem -Filter "Microsoft.SqlServer.Management.PSSnapins.dll" -Path $_ -Recurse).FullName    }}# Lets find Install Utility to add them with it.$InstallUtil = (Get-ChildItem -Filter "InstallUtil.exe" -Path "$env:windir\Microsoft.NET\Framework64\v2.0*" -Recurse).FullNameif (($null -eq $SQLPSProvider) -or ($null -eq $SQLPSSnapIn)){    Write-Host "Sorry, SQL PowerShell SnapIn or PowerShell Provider not found." -ForegroundColor Red}else{    # Adding them to our system.    Start-Process -FilePath $InstallUtil -ArgumentList "-i $SQLPSProvider"    Start-Process -FilePath $InstallUtil -ArgumentList "-i $SQLPSSnapIn"}# Now they should be in the system and we can add them to our PowerShell session.Add-PSSnapin -Name SqlServerCmdletSnapin100Add-PSSnapin -Name SqlServerProviderSnapin100# Now we should have Invoke-Sqlcmd like if we had SQLServer module.$SQLServer = "SQL2012"$SQLInstance = "JustForExample"$ServerInstance = $SQLServer + '\' + $SQLInstance# So you typing query for example like attaching DB.$Query = "CREATE DATABASE ExamleDB ON (FILENAME = `'C:\DBs\ExamleDB.mdf`'), (FILENAME = `'C:\DBs\ExamleDB_log.ldf`') FOR ATTACH"# And then executing it with Invoke-Sqlcmd like that.Invoke-Sqlcmd -ServerInstance $ServerInstance -Username 'sa' -Password 'Abcde12345' -Query "Query"