I am using VS 2017 SSDT 15.9.20 to create an SSIS package. The package is originally created in Server A where SSDT is installed and the SQL server resides in Server A. So I was ale to create the package in Server A. I created a SQL job in Server A and linked directly to run the DTSX file without deploying it.
What my package does:
In server A my package will read the excel in the location C:\Users\xxx\Documents\myproj\excelfile.xls and will create excel sheets inside C:\Users\xxx\Documents\myproj\files\ folder.
Deployment:
Now I want to deploy this package to Server B. And my package will read the excel in the location \ServerB\S:\Documents\myproj\excelfile.xls and will create excel sheets inside \ServerB\S:\Documents\myproj\files\ folder.
My questions:
Should I deploy my project to create dtsx file? There is already a dtsx file inside my project folder. Can I not just move that file to server B and change the connection string and paths? Will it work that way?
SSIS deployent tool does not work for me from server A. It does not identify the destination servers.How can be deployment made easy? How can I change the destination paths?
Server A has both SSDT installed and SQL database resides in same server. But the target server B is a database server and does not have SSDT in it. I will schedule a job in SQL server of server B that will call my dtsx package and execute it.
I am new to SSIS. so please don't close this question and would be great if anyone can help me with these questions. Thanks!
Without knowing the version of SQL, I'll give both answers:
Should I deploy my project to create dtsx file? There is already a dtsx file inside my project folder. Can I not just move that file to server B and change the connection string and paths? Will it work that way?
Versions less than SQL 2012: Copy the dtsx file from your project to the folder on the target server. Connection strings can be change in the configuration of the SQL Agent job or by using package Configurations:
Versions greater SQL 2012: Create the ssis catalog on the target servers:
Right click on the project and use the wizard to deploy to the target server. Connection strings can be modified in SQL Agent as noted above, or you can use parameters:
The latter my seem like more work at first, but there is a myriad of benefits and it is the preferred way of doing things
SSIS deployent tool does not work for me from server A. It does not identify the destination servers.How can be deployment made easy? How can I change the destination paths?
Follow either deployment method noted above. Package deployment (copy files to a folder) still works in later versions, but it is less easy to manage and less things done for you like securing configurations and setting up logging.
Server A has both SSDT installed and SQL database resides in same server. But the target server B is a database server and does not have SSDT in it. I will schedule a job in SQL server of server B that will call my dtsx package and execute it.
SSDT has no bearing on SSIS, but Integration Services does. SSDT is a developer tool and really should only be installed on developers machines for the purpose of creating packages and testing. It is better to not have this on the server because it encourages development to happen there and for devs to logon to the server like it's their laptop. Integration Services is a service that comes with SQL Server and is used for executing packages. You'll need to add this service to the instance if you want to execute packages from SQL Agent. This link explains that and gives guidance on installing SSDT locally: