May 7, 2012 at 3:58 pm
I am trying to set up a scheduled job that executes an SSIS package stored in the package store. When I execute the job, it 'works' but is putting out an error message about the connection I set up in the package for use only with SQL Server Configuration values. To me, it seems when my package runs, it is trying to 'validate' the connection to my SQL Server Configuration too soon and I get the error messages.
Since I want to be able to write the package one time for deployment in 3 environments, I am setting the connection string for the 'SQL Server Configuration' on the job. I know that the package is reading from the Configuration table, because values used by the package are not being saved in the package and can only be found on the configuration table.
Each environment is on a separate server with different user id that the jobs run under. So I don't put that information in my package.
So while my Package actual runs correctly and is obviously getting the connection information, I don't want the job to keep putting out these messages that make it look like an error occurred.
I'm not sure what else I can say or show because this is very straight forward. The package is a simple data flow between two tables on two separate databases with a single configuration. On the job I set the ConnectionString for the connections.
May 7, 2012 at 10:02 pm
Have you tried package config file?
May 8, 2012 at 12:03 am
Can you post the error?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 8, 2012 at 8:18 am
I don't have access to the physical server so wouldn't be able to put a configuration file on the server to reference. I am putting in my connection information on the job itself and would think that SSIS would have some sort of pecking order:
1. Use configuration from the job.
2. Use configuration file if used.
3. Use configurations from the package in the order they are in the package.
Is it possible that when the Package is Loaded, there is some attempt to validate the connection from the package before the configuration information from the job is 'inserted'? Because that is what it seems like is happening because I get the error message in the job about the invalid connection, but then the job and package work fine. I know that it is using the configuration information from the job definition because without it, it would not be able to connect to the target database.
May 8, 2012 at 8:30 am
The error being posted relates to the invalid connection. I have tried alternate settings so the error message changes so it's not really meaningful.
Here is what I have defined in my package:
Connection 1 - This has no connection information in the package because I want to pass it from the job. This connection is to be used to read from the SSISConfigurations Table.
Connection 2 - In the package, this has default connection information for the test database. The SSISConfigurations table on the database has the correct Server and Database values specified so when the package begins, it picks up that connection information that is used by the package.
Here is what I have on the Job.
Package Is Running from SSIS Store
On the Data Sources tab I enter in the connection string for Connection 1.
So when the job runs, I expect the package to pick up the Correct connection string from the job. Then, I expect it to connect to the SSIS configuration database to get the connection information for Connection 2. Technically, this works and the package runs correctly and the job finishes without error. But the job log has a bunch of errors about not having valid Connection information for Connection 1. So that is what I don't get.
I thought maybe a Delay Validation setting on my package would make a difference, but it didn't.
It's almost like I get the error messages about Connection 1 too soon -- before they are passed from the job.
Thanks for your attempt to help. I guess I may just have to live with the job messages. It just seems I'm missing some concepts of SSIS packages used in Jobs and how to implement Configuration information.
May 16, 2012 at 12:38 am
It's indeed possible that connection 1 is validated too soon, giving you unwanted error messages. I've seen a similar thread before.
Why don't you configure connection 1 with an environment variable or XML config file, instead of the job? Normally these don't give errors.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 24, 2012 at 8:20 am
Have you set validation to be delayed on all connctions and package as a whole??
May 25, 2012 at 12:12 am
You can set delay validation on the package by clicking on the control flow, go to properties and change the property there.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2012 at 7:44 am
I'll try those options and see what happens. (Had moved on to other issues so put this aside for a bit).
How do I close a post once I'm satisfied?
May 26, 2012 at 1:36 am
You don't, this forum doesn't have those options.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply