Solved

Powershell and SQL 2012

Posted on 2013-06-12
5
1,204 Views
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
   iptCommand

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
   ll.GetScriptCommand

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
cls 

$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'
                                                                        Union
                                                                        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

0
Comment
Question by:Leo Torres
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 39241141
do u get any errors?
0
 
LVL 8

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..
0
 
LVL 8

Author Closing Comment

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

Expert Comment

by:sedgwick
ID: 39241345
sorry i missed it, cheers.
0
 
LVL 8

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..
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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