Powershell and SQL 2012

Posted on 2013-06-12
Medium Priority
Last Modified: 2013-06-12
I have the script below. Which does exactly what its supposed to do in 2008. Place the name of server and it will extract all packages to a temp folder.

MY issue is that it wont work on a 2012 server. Why and how can i Fix

Here is the error
Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a conne
ction to SQL Server. The server was not found or was not accessible. Verify that the instance na
me is correct and that SQL Server is configured to allow remote connections. (provider: Named Pi
pes Provider, error: 40 - Could not open a connection to SQL Server)
At C:\Users\ltorres\Dropbox\Documents\Powershell\SSIS_PackageExtract.ps1:8 char:27
+ $Packages =  Invoke-Sqlcmd <<<<  -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "
WITH cte AS (
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScr

Invoke-Sqlcmd :
At C:\Users\ltorres\Dropbox\Documents\Powershell\SSIS_PackageExtract.ps1:8 char:27
+ $Packages =  Invoke-Sqlcmd <<<<  -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "
WITH cte AS (
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShe

Open in new window

Here is the code
Param($SQLInstance = "PCPRS01")

#####Add all the SQL goodies (including Invoke-Sqlcmd)#####
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue

$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "WITH cte AS (
                                                                        SELECT    cast(foldername as varchar(max)) as folderpath, folderid
                                                                        FROM    msdb..sysssispackagefolders
                                                                        WHERE    parentfolderid = '00000000-0000-0000-0000-000000000000'
                                                                        Select '\',folderid FROM msdb..sysssispackages dts
                                                                        Where folderid = '00000000-0000-0000-0000-000000000000'
                                                                        UNION    ALL
                                                                        SELECT    cast(c.folderpath + '\' + f.foldername  as varchar(max)), f.folderid
                                                                        FROM    msdb..sysssispackagefolders f
                                                                        INNER    JOIN cte c        ON    c.folderid = f.parentfolderid
                                                                    SELECT    c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
                                                                    FROM    cte c
                                                                    INNER    JOIN msdb..sysssispackages p    ON    c.folderid = p.folderid
                                                                    WHERE    c.folderpath NOT LIKE 'Data Collector%'"

Foreach ($pkg in $Packages)
    $pkgName = $Pkg.name
    $folderPath = $Pkg.folderpath
    $fullfolderPath = "c:\temp\$SQLInstance\$folderPath\"
    if(!(test-path -path $fullfolderPath))
        mkdir $fullfolderPath | Out-Null
    $pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx"

Open in new window

Question by:Leo Torres
  • 3
  • 2
LVL 42

Accepted Solution

Meir Rivkin earned 2000 total points
ID: 39241141
do u get any errors?

Author Comment

by:Leo Torres
ID: 39241322
Hey sed sup... was looking fo ya on that other question for powershell and schedule..

I figured this out.. it was wrong name i had to enter the instance complete name server\Instance..

Author Closing Comment

by:Leo Torres
ID: 39241324
Thank for the help!!
LVL 42

Expert Comment

by:Meir Rivkin
ID: 39241345
sorry i missed it, cheers.

Author Comment

by:Leo Torres
ID: 39241706
Do you want me to open.. Powershell question again.. You have already have the logic in place I just need a new page..

Featured Post

We Need Your Input!

WatchGuard is currently running a beta program for our new macOS Host Sensor for our Threat Detection and Response service. We're looking for more macOS users to help provide insight and feedback to help us make the product even better. Please sign up for our beta program today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Welcome to 2018! Exciting things lie ahead in the world of tech. To start things off, we compiled great member articles on how to stay safe, ways to learn, and much more! Read on to start your new year right.
Just after setting up Cloud PBX connectivity and migrated Skype users to SFBO, we noticed inbound calls not working but outbound calls would work.
Loops Section Overview
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question