Export Integration Services Catalogs

Question

Post reply

Export Integration Services Catalogs

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
October 5, 2016 at 3:39 pm

#330729

Hi Experts,
I need to transfer SSIS Packages which are under "Integration Services Catalog" under SSISDB.
Basically I need to get packages exported to Non Prd servers so that dev team can use them.
Under SSISDB , there are two folders - and each folder contains 30-40 packages.
I have already exported packages with an extnsion as .ispac, but don't know how to easily import them to the non prod servers.
Your inputs will be highly appreciated.
Thanks
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

You may want to try the steps Microsoft has outlined for importing/exporting packages:

Import and Export Packages (SSIS Service)

Sue

Phil Parkin SSC Guru Points: 246979 More actions · Answer 2

SQL-DBA-01 (10/5/2016)
Hi Experts,
I need to transfer SSIS Packages which are under "Integration Services Catalog" under SSISDB.
Basically I need to get packages exported to Non Prd servers so that dev team can use them.
Under SSISDB , there are two folders - and each folder contains 30-40 packages.
I have already exported packages with an extnsion as .ispac, but don't know how to easily import them to the non prod servers.
Your inputs will be highly appreciated.
Thanks

Navigate to the Integration Services Catalog node in SSMS, on the non-prod server, and then the SSISDB node. Either select the folder you want to create the project under, or create a new folder.

Expand the folder and, underneath it, right-click on the Projects node and then 'Deploy Project'.

'Project Deployment File' / browse and select your ispac. Then follow the prompts. This will import the packages and parameters from your ispac into SSISDB.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

In the dest (non Prod here) server, the SSDB is not created.

Should I create that first and then the similar two folder structure and try out the remaining?

I have already exported all the SSIS packages to the .ispac format!!!

Please suggest.

Thanks.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 4

Yes. Create first, then folder structure and finally deploy.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

SQL_Hunt

SSC-Dedicated

Points: 33518

October 5, 2016 at 7:36 pm

#1905077

Thanks Phil...I'll try tomorrow.

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Hi Phil,

I created the same folder structure as available in Production but when I try to import packages to the respective folders, I get the below error. Any advise how to successfully import the packages?

TITLE: Failed to launch the wizard

------------------------------

To launch the Integration Services Deployment wizard, one of the components: Integration Services, Management Tools - Basic or SQL Server Data Tools has to be installed by the %SQL_PRODUCT_SHORT_NAME% Standard, Enterprise, Developer, or Evaluation Edition. To install a component, run SQL Server Setup and select the component name.

------------------------------

BUTTONS:

OK

------------------------------

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 7

Hi Phil,

It worked...:) I am able to import/deploy the .ispac packages. Thanks much.

If I need any further help from you on this will let you know.

Thanks much for your advise!!!

Thanks.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 8

SQL-DBA-01 (10/6/2016)
Hi Phil,
It worked...:) I am able to import/deploy the .ispac packages. Thanks much.
If I need any further help from you on this will let you know.
Thanks much for your advise!!!

No problem, thanks for posting back.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 9

Hi Phil,

While deploying getting the below Warning. But able to deploy the packages successfully. No error.

Is there something to look into against this warning?

TITLE: SQL Server Integration Services

------------------------------

Warning: Failed to decrypt an encrypted XML node. Verify that the project was created by the same user. Project load will attempt to continue without the encrypted information.

Warning: Failed to decrypt sensitive data in project with a user key. You may not be the user who encrypted this project, or you are not using the same machine that was used to save the project. If the sensitive data is a parameter value, the value may be required to run the package on the Integration Services server.

------------------------------

BUTTONS:

OK

------------------------------

Thanks.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 10

SQL-DBA-01 (10/6/2016)
Hi Phil,
While deploying getting the below Warning. But able to deploy the packages successfully. No error.
Is there something to look into against this warning?
TITLE: SQL Server Integration Services
------------------------------
Warning: Failed to decrypt an encrypted XML node. Verify that the project was created by the same user. Project load will attempt to continue without the encrypted information.
Warning: Failed to decrypt sensitive data in project with a user key. You may not be the user who encrypted this project, or you are not using the same machine that was used to save the project. If the sensitive data is a parameter value, the value may be required to run the package on the Integration Services server.
------------------------------
BUTTONS:
OK
------------------------------

Yes. It means that the project originally had a protection level of 'Encrypt Sensitive Data with User Key' (or something like that). So any passwords or other parameters considered or defined as sensitive will not have been deployed, even though they existed in the original project.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 11

So need to use the same password whch are used for SSISDB ?

Thanks.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 12

SQL-DBA-01 (10/6/2016)
So need to use the same password whch are used for SSISDB ?

The sensitive values cannot be retrieved, unless the user who originally saved the project opens it again.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel