September 29, 2015 at 2:18 am
Hi,
I need to deploy a SSIS project to a remote server to which I have very limited access. Don't have remote access to this SQL server. In fact, the only way I can connect is with RDP via a Citrix portal.
Usually, we'll just create the .ispac file & copy it to SSIS server & then run this as part of a SQL script:
DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'MyProj.ispac', SINGLE_BLOB) as BinaryData)
Exec catalog.deploy_project @folder_name = 'MyFolder', @project_name = 'MyProj', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out
Problem now is, I can't even copy the .ispac file to this remote server.
Is there a way I can get the binary version of the .ispac file & just add this into the SQL script? In other words, instead of using OPENROWSET to import the .ispac file, somehow export/convert the entire project to binary beforehand?
Hope this makes sense...
Thanks.
September 29, 2015 at 2:23 am
D1rtyD0g (9/29/2015)
Hi,I need to deploy a SSIS project to a remote server to which I have very limited access. Don't have remote access to this SQL server. In fact, the only way I can connect is with RDP via a Citrix portal.
Usually, we'll just create the .ispac file & copy it to SSIS server & then run this as part of a SQL script:
DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'MyProj.ispac', SINGLE_BLOB) as BinaryData)
Exec catalog.deploy_project @folder_name = 'MyFolder', @project_name = 'MyProj', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out
Problem now is, I can't even copy the .ispac file to this remote server.
Is there a way I can get the binary version of the .ispac file & just add this into the SQL script? In other words, instead of using OPENROWSET to import the .ispac file, somehow export/convert the entire project to binary beforehand?
Hope this makes sense...
Thanks.
If you cannot copy files, how are you proposing to get the data over there? Ie, what operations are still permitted?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 29, 2015 at 2:31 am
We do not have to get any data to & from this server. It's an isolated environment. It's an ETL package which will transport data from a transaction to a warehouse server within this environment.
I only need to get the package onto that server without having to copy the .ispac file or deploying directly to the SSIS catalog.
September 29, 2015 at 3:19 am
D1rtyD0g (9/29/2015)
We do not have to get any data to & from this server. It's an isolated environment. It's an ETL package which will transport data from a transaction to a warehouse server within this environment.I only need to get the package onto that server without having to copy the .ispac file or deploying directly to the SSIS catalog.
OK, my use of the word 'data' in this case was unclear. I was classing the ispac file as data.
But the question remains valid: if you have no means to transfer files to this server, what makes you think that you can achieve this requirement?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 29, 2015 at 3:53 am
That is exactly what I was asking in the first post. 🙂
So, instead of running this statement to import the .ispac file:
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'MyProj.ispac', SINGLE_BLOB) as BinaryData)
If I can get the binary version of the .ispac file beforehand(somehow) & just do this:
Set @ProjectBinary = {ispac binary string}
I can then create the entire SQL script on my local machine & just copy/paste the text into a query window on the remote server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply