July 12, 2018 at 10:33 am
I'm very new to SSIS and I'm using the SSISDB catalog. I've built some packages to move data from a couple of SQL instances that are internal to our network over to a SQL instance on the DMZ network. The package truncates tables in the destination then goes into a control flow that gets the data from the internal SQL Server source and puts it into the target using fast load.
I initially tested just getting the packages to work using the internal servers and used my desktop instance as the destination for the data. Once that all worked from visual Studio and a SQL Agent job, I created a SQL account to use in a new connection manager to the DMZ SQL instance. I changed the connection managers in the parts of my packages that were loading the data so that they would use my new connection manager to the DMZ. I executed the packages from Visual Studio and it worked. I deployed the project to my local desktop and created a job using each of four packages as a step in the process to load the data. That all worked. Data from the internal SQL servers was loaded into the DMZ.
I then deployed the project to the prod server from Visual Studio. I scripted the job from my local server and ran that on the prod server. Now the job fails because in the @command part of the job script there is a reference to my local desktop in the job steps instead of there being a reference to the DMZ machine, like I expected, and there is no remote connectivity to my local desktop SQL instance. I've deleted the SQL Agent job and tried recreating it. However, the only option available in the New Job Step > Package tab is to log onto the server with Windows Authentication. That's not an option because it's in the DMZ. The Use SQL Server Authentication option is grayed out. So, even when I put in the IP of the DMZ server, I still get an error because I can't specify a SQL account.
I'm sure there's probably something fundamental about this process that I don't understand. I just don't know how to solve these issues.
July 12, 2018 at 10:48 am
lmarkum - Thursday, July 12, 2018 10:33 AMI'm very new to SSIS and I'm using the SSISDB catalog. I've built some packages to move data from a couple of SQL instances that are internal to our network over to a SQL instance on the DMZ network. The package truncates tables in the destination then goes into a control flow that gets the data from the internal SQL Server source and puts it into the target using fast load.I initially tested just getting the packages to work using the internal servers and used my desktop instance as the destination for the data. Once that all worked from visual Studio and a SQL Agent job, I created a SQL account to use in a new connection manager to the DMZ SQL instance. I changed the connection managers in the parts of my packages that were loading the data so that they would use my new connection manager to the DMZ. I executed the packages from Visual Studio and it worked. I deployed the project to my local desktop and created a job using each of four packages as a step in the process to load the data. That all worked. Data from the internal SQL servers was loaded into the DMZ.
I then deployed the project to the prod server from Visual Studio. I scripted the job from my local server and ran that on the prod server. Now the job fails because in the @command part of the job script there is a reference to my local desktop in the job steps instead of there being a reference to the DMZ machine, like I expected, and there is no remote connectivity to my local desktop SQL instance. I've deleted the SQL Agent job and tried recreating it. However, the only option available in the New Job Step > Package tab is to log onto the server with Windows Authentication. That's not an option because it's in the DMZ. The Use SQL Server Authentication option is grayed out. So, even when I put in the IP of the DMZ server, I still get an error because I can't specify a SQL account.
I'm sure there's probably something fundamental about this process that I don't understand. I just don't know how to solve these issues.
The DBA on the prod server should be able to create this job for you.
Package connection info (for runtime connection to the source and target databases) should be configured through the use of SSISDB environment variables.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply