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
Solved

Powershell and SQL 2012

Posted on 2013-06-12
5
1,170 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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Synchronize a new Active Directory domain with an existing Office 365 tenant
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 …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

839 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