CODEWITHSUNDEEP
sql-servercsvpowershellsql-job
Aerobane
Aerobane

Reputation: 85

Powershell Job Step in SQL Error

I'm new to PowerShell and still early in SQL Server, but I'm trying to write a PowerShell step for a SQL Agent job that looks at a CSV file that contains names of .sql files.

It should then look at a different directory and if the names from the CSV file exist in that directory it should open the .sql file and execute the function inside.

I'm getting an error:

Unable to cast object of type 'System.String to type System.Type

Any help would be greatly appreciated.

$excelFile = "C:/ExcelTest/Test.csv"
$functionDirectory = "some directory"

$excel_Array = (Get-Content $excelFile)[0].split(",")

foreach ($sqlName in $excel_Array) 
{
    if($sqlName::exists($functionDirectory + "/" + $sqlName) -ne $true) 
    {
        invoke-sqlcmd -inputfile $functionDirectory + "/" + $sqlName -serverinstance "serverinstance" -database "database"
    }
}

Upvotes: 0

Views: 160

Answers (2)

user847990
user847990

Reputation:

I would adjust a few things in your script to properly handle a CSV and then utilize built-in cmdlet for testing the path of a given file.

[cmdletbinding()]
param()

Import-Module SQLPS -DisableNameChecking
$functionDirectory = "C:\temp\PowerShell_Testing2"
$excelFile = Import-Csv "C:\temp\PowerShell_Testing\SQLFileList.csv"

foreach ($e in $excelFile) {
    $fileonly = Split-Path $e.SQLFile -Leaf
    $fdFile = $functionDirectory + "\" + $fileonly
    if (Test-Path $fdFile) {
        Write-Host "Found File $fdFile"
        Invoke-Sqlcmd -ServerInstance "MANATARMS\SQL12" -InputFile $fdFile -Database master
    }
}

In my setup there is only one file that just runs:

SELECT TOP 1 name FROM sys.databases

enter image description here

If your CSV contains say a ServerName, and then SQLFile you can adjust your script to also pull the ServerInstance value like this:

[cmdletbinding()]
param()

Import-Module SQLPS -DisableNameChecking
$functionDirectory = "C:\temp\PowerShell_Testing2"
$excelFile = Import-Csv "C:\temp\PowerShell_Testing\SQLFileList.csv"

foreach ($e in $excelFile) {
    $fileonly = Split-Path $e.SQLFile -Leaf
    $fdFile = $functionDirectory + "\" + $fileonly
    if (Test-Path $fdFile) {
        Write-Host "Found File $fdFile"
        Invoke-Sqlcmd -ServerInstance $e.ServerName -InputFile $fdFile -Database master
    }
}

Upvotes: 0

Eris
Eris

Reputation: 7638

If understand the question correctly, you need to use Test-Path not ::exists

$excelFile = "C:/ExcelTest/Test.csv"
$functionDirectory = "some directory"

Import-Csv $excelFile | 
    Foreach-Object {
        $filename = $functionDirectory + '\' + $_[0]
        if (Test-Path $filename) {
            invoke-sqlcmd -inputfile $filename -serverinstance "serverinstance" -database "database"
        }
    }

Upvotes: 1

Related Questions