July 25, 2018 at 10:55 pm
I'm using the project deployment model with SSIS/SSDT.
I've got a number of packages that copy Table1 from Server A to Server B.
The default logging in SSIS doesn't show the actual connection strings for the source and target.
More details:
If I navigate to Integration Services Catalogs --> SSISDB --> Some Folder --> Projects --> My Project --> Packages --> Some Package, RMB, Reports --> Standard Reports --> All Executions, set the filter, find my specific execution ID, All Messages...
While I can see the parameters that were active at execution time, nowhere in the log messages do I see the actual connection strings at the time of execution.
I know I can RMB My Project --> Configure --> Connection Managers, to see the currently deployed connection managers...but how do I know the connection details at the time the job ran?
What if I RMB SSISDB --> Properties --> Server-wide Default Logging level, and change from Basic to Verbose? Will this give me what I want, without too much junk in the logs?
(The issue is I did some testing, and failed to revert those settings (i.e. redeploy the old SSDT solution) before a production run. While I got lucky and it wasn't a big deal, in investigating this, I realized that, with the default logging, if you redeploy a new version of the project, you lose the ability to know which servers were the source & target of the package.)
July 25, 2018 at 10:57 pm
I just tried this on a test server. Way too much junk in the log to be useful.
July 29, 2018 at 9:42 pm
Anyone? I can't believe the SSIS package logs don't allow me to see the connection strings for the source and target tables at the time the job was run.
July 30, 2018 at 9:24 am
You could probably use a script task to write out the connection information to the log if you wanted, although there could be an issue if the connection uses and expression as the script task may not force the expression to be evaluated if delay validation is set.
A better option though would probably be to avoid the problem entirely by using either config files to set the connection, or expressions using package variables that are set by the job on each server. That way there is nothing set within the package, and moving between environments won't require changes to the package itself.
July 31, 2018 at 1:37 am
You could probably use a script task to write out the connection information to the log if you wanted, although there could be an issue if the connection uses and expression as the script task may not force the expression to be evaluated if delay validation is set.
So by implication this basic functionality is not built into SSDT/SSIS execution logs? Sometimes I wonder if Microsoft have a clue how their customers need to use their products? Isn't this basic program logging 101?
A better option though would probably be to avoid the problem entirely by using either config files to set the connection, or expressions using package variables that are set by the job on each server. That way there is nothing set within the package, and moving between environments won't require changes to the package itself.
I'm using profiles with connection parameters to move between Dev and Prod. I'm not sure if this is what you meant by config files? I deploy my packages with the appropriate connections strings (esp. target tables) pointing to dev (so that's the default), but require a profile to be chosen when the package is executed. So, while it's not "nothing is set within the package", there is no need to change the package itself when moving between environments.
Let me give you this scenario:
1) I execute a package, and my boss wants to know what the source and target databases were for this execution. Can I tell that from the execution log?
2) Three months go by, and I deploy some changes to the packages, for example pointing to a new server, or a copy of a database (with different data).
3) A month goes by, and we discover problems with the data in the run from #1 above. We haven't noted when the deployment occurred (is there a way to note the history of package deployment OOTB?). So, again, I want to review this 4 month old log, and know what the source and target tables were for this package from reviewing the execution log.
If not, then I say it's another fail from Microsoft - this is really basic functionality they should think of. If I'm just an inexperienced SSDT/SSIS developer that isn't aware of some XYZ setting, then let me know that too 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply