CODEWITHSUNDEEP
sql-serverpowershellrefreshvisio
Jerome
Jerome

Reputation: 267

Automate SQL Server imported Data Set Refresh via Powershell script in Visio

I'm currently trying to write a Powershell script that I can use to refresh the data in a Visio file and export the results to pdf. The issue I'm running into is that the DataRecordset.Refresh method seems to cause a pop up for credential entry, before it will allow the script to finish. Is there any way around this?

$floorplans = Get-ChildItem -Filter "*.vsdx"
Write-Host "Converting Visio documents to PDF..." -ForegroundColor Cyan

try
{
$visio = New-Object -ComObject Visio.Application
$visio.Visible = $false

foreach ($floorplan in $floorplans)
{
    


    $pdfname = [IO.Path]::ChangeExtension($floorplan.FullName, '.pdf')
    Write-Host "Converting:" $floorplan.FullName "to" $pdfname
    $document = $visio.Documents.Add($floorplan.FullName)
    
    foreach($dataSet IN $document.DataRecordSets())
    {

        $dataSet.Refresh()
    }
    # Export all pages to PDF, see constants here http://msdn.microsoft.com/en-us/library/office/ff766893.aspx
    $document.ExportAsFixedFormat(1, $pdfname, 1, 0)
}

}

catch
{
Write-Error $_
}

finally
{
if ($visio) 
{
    $visio.Quit()
}

}

Upvotes: 0

Views: 152

Answers (1)

Nikolay
Nikolay

Reputation: 12245

If your data source requires authentication (credentials) then you need to specify those or save them in Visio file. There is usually a checkbox to allow saving credentials in file (in Visio), on the database connection page, depends on your database type.

Also, you could try specifying the password by altering the connection string, like this:

$cs= $dataSet.DataConnection.ConnectionString
$dataSet.DataConnection.ConnectionString = "$cs;password=<your password>"

$dataSet.Refresh()

Here is a somewhat related blog article, check it out: http://ton.snoei.net/2009/08/03/systeem-en-softwaremonitoring-met-visio-2007-4-van-4-de-software/

Please note that you may need a different syntax, depending on the database you are using.

Upvotes: 1

Related Questions