Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extract ispac file from SSISDB Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 12:49 AM
Points: 2, Visits: 14
Hi -

I need to extract the .ispac file from the SSISDB. I can retrieve the stream with catalog.get_project sp. However, the file I end up with cannot be unzipped, giving an error message. My guess is that it is meta-data on the zip/ispac file that has a problem, because I can actually unzip it with Winrar, but not with any of those I (programmatically) need to unzip it with.

Below is the code for my stored procedure - please take a hard look at where something might go wrong. My own suspicion is in the BCP usage to turn the stream into a file.

Thanks,
Anders

USE [SSISDB]
GO

ALTER PROCEDURE [dbo].[spGetIspacFile]
@project VARCHAR(255) ,
@environmentFolder VARCHAR(50) ,
@ispacTempFolder VARCHAR(100) ,
@ispacFilePath VARCHAR(200) OUTPUT
AS
BEGIN
DECLARE @ispacFileName VARCHAR(200) = CONVERT(NVARCHAR(50), NEWID())
+ '.ispac'

SET @ispacFilePath = @ispacTempFolder + '\' + @ispacFileName


CREATE TABLE ##resultsTableVar
(
binaryProject VARBINARY(MAX)
)

INSERT ##resultsTableVar
( binaryProject
)
EXEC [SSISDB].[catalog].[get_project] @folder_name = @environmentFolder,
@project_name = @project

DECLARE @sqlCommand VARCHAR(500)

SET @sqlCommand = 'BCP "SELECT binaryProject FROM ##resultsTableVar" queryout "'
+ @ispacFilePath + '" -T -n -S"' + @@SERVERNAME + '"'

EXEC master.dbo.xp_cmdshell @sqlCommand

DROP TABLE ##resultsTableVar
END

Post #1592232
Posted Monday, July 14, 2014 10:19 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 12:49 AM
Points: 2, Visits: 14
I found the solution. Apparently, BCP is faulty, although it is uncertain exactly how - might be a header setting on the file that is not right, when it creates the file from the stream. So here is the solution, without using BCP. (thanks to this on Stackoverflow for leading to the solution):

USE [SSISDB]
GO

ALTER PROCEDURE [dbo].[spGetIspacFile]
@project VARCHAR(255) ,
@environmentFolder VARCHAR(50) ,
@ispacTempFolder VARCHAR(100) ,
@ispacFilePath VARCHAR(200) OUTPUT
AS
BEGIN
DECLARE @ispacFileName VARCHAR(200) = CONVERT(NVARCHAR(50), NEWID())
+ '.ispac'

SET @ispacFilePath = @ispacTempFolder + '\' + @ispacFileName


CREATE TABLE ##resultsTableVar
(
binaryProject VARBINARY(MAX)
)

INSERT ##resultsTableVar
( binaryProject
)
EXEC [SSISDB].[catalog].[get_project] @folder_name = @environmentFolder,
@project_name = @project


DECLARE @fileStream VARBINARY(MAX) ,
@objectToken INT

SELECT @fileStream = binaryProject
FROM ##resultsTableVar

EXEC sp_OACreate 'ADODB.Stream', @objectToken OUTPUT
EXEC sp_OASetProperty @objectToken, 'Type', 1
EXEC sp_OAMethod @objectToken, 'Open'
EXEC sp_OAMethod @objectToken, 'Write', NULL, @fileStream
EXEC sp_OAMethod @objectToken, 'SaveToFile', NULL, @ispacFilePath, 2
EXEC sp_OAMethod @objectToken, 'Close'
EXEC sp_OADestroy @objectToken

DROP TABLE ##resultsTableVar
END

Post #1592250
Posted Thursday, July 24, 2014 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:27 AM
Points: 6, Visits: 42
first try (from powershell):
Am able to write these out to an .ispac files and rename to .zip
but the contents are garbage

2nd try (SSMS)
your OACREATE method works, creates the ispac/zip file fine
but if im hitting a remore server
the powershell calls TSQL on the remote server which calls OACreate (local to it) and saves it the file on the remote server's disk




Post #1595885
Posted Thursday, July 24, 2014 10:25 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 3,024, Visits: 2,628

Below is the code for my stored procedure - please take a hard look at where something might go wrong. My own suspicion is in the BCP usage to turn the stream into a file.



I think that your issue with BCP is simply - it is the wrong tools for this task. BCP is intended to write data to a file on a row by row basis with a separator (e.g. CR/LF) between each row. What you need for this task is a method/tool that will write the individual bytes to a file with the extra separators. This is why the solution you found works.



Post #1596079
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse