You may be over complicating things. PRINT is a debugging method rather than sending something as output, so if you used the -Verbose switch when you called Invoke-SqlCmd, you would see the PRINT information in verbose output. As @Kiran indicated, you need to use SELECT to send data back. By default, Invoke-SQLCmd is going to send a PSObject back, so unless you were trying to send multiple selects back at once which would be multiple tables, then you don’t need to use datatables. My guess is you had issues getting the variable because you didn’t name it in your Select, so it’s easier to assign an alias (e.g. Select @var AS friendlyName).
It appears you’re calling a stored procedure that does and INSERT\UPDATE and returns a message for each symbol. You could do this multiple ways, one way is to pass the data to SQL and then have SQL pass everything back, like so:
$sqlstatementInsert = @"
DECLARE @Message nvarchar(50);
EXEC [ACTIVE].[uspUpsertStocks] @Symbol = '{0}'
,@st_date = '{1}'
,@ST_OPEN = '{2}'
,@ST_HIGH = '{3}'
,@ST_LOW = '{4}'
,@ST_CLOSE = '{5}'
,@ST_VOLUME = '{6}'
,@ST_ADJ_CLOSE = '{7}'
,@msg = @Message output;
SELECT @Message AS Message
,@Symbol AS Symbol
,@st_date AS Date
...
"@
When you get the results back from SQL, you would have everything already in a PSObject. Another method is to basically glue it together in Powershell using a calculated expression, like so:
$sqlstatementInsert = @"
DECLARE @Message nvarchar(50);
EXEC [ACTIVE].[uspUpsertStocks] @Symbol = '{0}'
,@st_date = '{1}'
,@ST_OPEN = '{2}'
,@ST_HIGH = '{3}'
,@ST_LOW = '{4}'
,@ST_CLOSE = '{5}'
,@ST_VOLUME = '{6}'
,@ST_ADJ_CLOSE = '{7}'
,@msg = @Message output;
SELECT @Message AS Message
"@
$list = Get-ChildItem -Path $csvpath | Select FullName
$results = forEach ($file in $list) {
$content = Import-Csv -Path $file.FullName
foreach ($symbol in $content) {
$params = @{
Query = $sqlstatementInsert -f $_.symbol, $_.date,$_.open, $_.high, $_.low, $_.close, $_.volume,$_.adjclose
ServerInstance = $DBServer
Database = $DB
}
$sqlResults = Invoke-SqlCmd @params
}
$file |
Select *,
@{Name='Message';Expression={$sqlResults.Message}}
}
$results
In any event, you want to return all of the data and append the message so you can see what the result was of passing that data. Then you don’t need all of the if statements and you can use grouping or where statements to see what happened with your batch:
$results | Group-Object -Property Message
Count Name Group
----- ---- -----
1 Inserted {@{Symbol=ABC; Message=Inserted}}
2 Symbol exist {@{Symbol=SUB; Message=Symbol exist}, @{Symbol=ZXR; Message=Symbol exist}}
1 {@{Symbol=GTF; Message=}}