Dynamically set logging levels?

Hello All,

I suspect I know the answer but I'll ask away. We currently have our SSIS packages set up to log to SQL Server. Currently they log OnError, OnInformation and OnTaskFailed. If I'd like to have it log OnPipeLineRowsSent, is there anyway I can get that done without opening up the package and editing it? I know the change is trivial from the IDE but the deployment process at my current engagement is quite lengthy. If something breaks in production, I'd like to know if it'll be possible to turn up the chattiness of logging without going through a full deploy scenario.

I was looking at the parameters for dtexec/dtexecui and I see that you can configure where something logs but nothing about the verbosity of the logs generated. Is it something I'm missing with that or is that all you can set there?

The only other option that jumps out at me is to develop a custom script or component that sets the logging level based on a parameter. Anyone have a thought as to how much effort that would besomething easily tackled or probably more trouble that it's worth?

Thanks for the help

I don't think you can send parameters to the logging provider because logging occurs before pretty much any thing else. See this post for more details:
http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx

Dynamically change the servername

Hi all,

I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).

For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.

Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.

My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.

I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.

How can I achieve this the easiest way ?

Thanks in advance !

Geert

You need to take a look at creating "package configurations". Try doing a search in Books Online for this. It helps you to do exactly what you are looking for.

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:

> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:
> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

Dynamic SSIS Configurations

Hey there,

I know that many articles have been written describing configurations for packages but I have yet to have found one that describes if it is possible to use SQL Server configuration type for a package that is to be tested on DEV, then UAT, then PROD boxes.

I would like to know if there's a way to store values in a config table in a database on DEV, UAT and PROD but never have to change anything in the package.

I mean, I wonder if I can pass a parameter that defines the server to go get the configurations from.

The 3 servers will contain a database with a config table named the same on each server but have different configuration values to point them to the proper sources and destinations depending on which server the configuration database resides.

Thank you,

Robin

Sure. This is how I do it. I have one XML file that is stored on each server that stores the connection string to the "configuration" database. Then each time we promote the package, no changes have to be made to it.|||

Awesome,

Thank you.

Also, for scheduling purposes, if I did not want to use an xml configuration file, I can use the /SET option with the dtexec.exe command and set the Package.configurationLocation (don't quote me on the name of the configuration) property as well right?

The DBA's over here would rather only have packages and not config files to move over from server to server.

Would that be an option as well?

Of course it would be defined in the documentation that the package expects a certain parameter.

Thanks

|||I can't comment on all of that, but the way we do it here, we create the XML config file once and never change it. So once it's on the server, we never change it. Of course we would have to change it if the database contained in the config file changes to a new name...|||

Again, Thanks for the rapid responses.

Cheers

Rob

|||

r4enterprises wrote:

Awesome,

Thank you.

Also, for scheduling purposes, if I did not want to use an xml configuration file, I can use the /SET option with the dtexec.exe command and set the Package.configurationLocation (don't quote me on the name of the configuration) property as well right?

The DBA's over here would rather only have packages and not config files to move over from server to server.

Would that be an option as well?

Of course it would be defined in the documentation that the package expects a certain parameter.

Thanks

You can use /SET or /CONN (I think) to set the connection strings in the packages through DTEXEC. However, be aware that if you run other packages from the first package, the connection string won't be passed along, unless you use Parent Package variables.

|||

Thats great!

Luckily, I do not use other packages inside the first one so I don't have to worry about it.

However, this is noted and I'll forward this to the documentation group so they make a note.

If I use /SET to set the connection string of the child package, would that work though?