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.
Best Answer
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/