2

I am in charge of creating a documentation for an ETL Process which involves a Remote SQL Server which is linked to a Postgre DB. In order to periodically update the data, Jobs have been setup using Server Agent. All this was done by someone who is no longer in contact. But now I want to go through/viewing what exactly is happening inside the SSIS packages.

This ETL Process is running on a remote SQL Server.

I have SQL Server 2014 DB Engine and SQL Server Mgmt Studio installed in my local machine and yes I did select the Integration Services feature while in the installation stage.

After some research I found that in order to view SSIS packages, I first needed to connect using Server Type as "Integration Services", but when I try do that I get an "Access is Denied" error message. In order to tackle this problem I tried to change Access Rights in the DCOM configurations but still the same result.

If I can import these packages to my local machine then I can use Visual Studio 13 to open and know what exactly each package is doing. (Correct me if I'm wrong)

Can anyone help me to import a package which is remotely stored in a server?

Please note that the Remote Server and my local computer do not have the same name and password.

|improve this question
  • I would first go look at the SQL Agent job and see how the package is being run. If filesystem is being used, most of what you are doing is not required. – user507 Sep 22 '16 at 20:47
  • In reference to the VS13, that might work but you need to know what version the package was created under. You state what you have, but not what is being used in production or on that remote server. – user507 Sep 22 '16 at 20:47
  • @ShawnMelton I did have a look at the packages being run, and I don't quite understand what you mean by "filesystem". I can see there are multiple SSIS packages being run in succession. – Sravee Sep 26 '16 at 13:52
1

Quirk or Perk?

Try this. Close SQL Server Management Studio. Then, right-click it's icon and Run as Administrator. Then, try viewing those packages once more.

That being said...

Use DTUTIL

I find DTUTIL the easiest way to import/export packages to and from SQL Servers.

https://technet.microsoft.com/en-us/library/ms162820(v=sql.110).aspx

Example:

Update, the code from this author looks promising for mass-import of packages using DTUTIL though I have not tried it.

https://canserhat.wordpress.com/2012/08/23/export-and-import-all-ssis-packages-in-sql-server-2008/

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.