Hello everybody
I have tried to create a script for SQL Server2008 executed as job.
JobType: PowerShell

Until this point everything worked fine...
I need to read the OS Disk Statuts... so here the Snippet which works just fine:
get-Wmiobject -class Win32_LogicalDisk | ft Name, VolumeName, Size, FreeSpace

Then I tried to write a script to fill the data in a DataTable which will be automatically bulkinserted into sql.
Here my prob begins...
If somebody has a clue please help :/

Code:

In the future I would suggest getting code to run interactively from regular Powershel console before trying to create a SQL Agent job. There are a couple of issues:
I don't know if this formatting of your forum post, but the entire script you posted is on a single line. This will not work as there are considerations for needing line breaks, having certain keywords by themselve or using a semi-colon or continuation character. It's much easier to use line break as follows (this works):

function Out-DataTable            
{            
    param($Properties="*")            
    Begin            
    {            
        $dt = new-object Data.datatable              
        $First = $true             
    }            
    Process            
    {            
        $DR = $DT.NewRow()              
        foreach ($item in $_ |  Get-Member -type *Property $Properties ){              
          $name = $item.Name            
          if ($first) {              
            $Col =  new-object Data.DataColumn              
            $Col.ColumnName = $name            
            $DT.Columns.Add($Col)       }              
            $DR.Item($name) = $_.$name              
        }              
        $DT.Rows.Add($DR)              
        $First = $false              
    }            
    End            
    {            
        return @(,($dt))            
    }            
}            
            
$dataTable = get-Wmiobject -class Win32_LogicalDisk | Select Name, VolumeName, Size, FreeSpace | Out-DataTable            
$connectionString = "Data Source=QCDEVPDS01\MSSQLSERVERDEV;Integrated Security=true;Initial Catalog=SS_DBA_Dashboard;"            
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString            
$bulkCopy.DestinationTableName = "t_OS_Drives"             
$bulkCopy.WriteToServer($dataTable)

The second issue I see you are using Format-Table (FT) instead of select-object (select).
The output of format table cannot be piped to your out-datatable function.
Format table returns a differrent type

thanx alot for the hint, but it sill doesnt work...
could it be that powershell 1.0 doesnt support that kind of action?


Error yet:
The corresponding line is '$bulkCopy.WriteToServer($dataTable)'.
Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to type int of the specified target column."

It looks like you we worked past the issues of converting the output to a datatable, but now the issue is in calling the WriteToServer method. Based on the error message your problem is that the dataTable column order does not match your SQL Server table column order and the wrong column are mapped. There are two ways to fix this:

Explicity map columns using the SqlBulkCopy class OR change your SQL table to match the datatable column order. The latter is easier, to do this:
After this line
$dataTable = get-Wmiobject -class Win32_LogicalDisk | Select Name, VolumeName, Size, FreeSpace | Out-DataTable
Run $dataTable | get-member

Recreate or alter your table, t_OS_Drives to match the order you see in the Powershell console. If you have issues doing this post your t_OS_Drives table SQL script and I'll take a look at it.

Thanx for the hint.
it works now, a bit slow but it does what it should.
many thanx again.