Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.sql

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Add scripttask script programmatically

I'm building packages programmatically. I need to add a ScriptTask to my package and include the script code. The script task itself is easy to add. But I can't figure out how to add the script code to the task.

I found one post in this forum saying the trick is to use the PutSourceCode method of the StriptTaskCodeProvider class, but I can't figure out how to do that.

Can anyone provide a code sample of how this is done?

Thanks.

After looking into this more deeply I found that adding a Script Task is a large job and I opted for a simpler approach to my particular issue. A lot of code needs to be added into the package. You have to create a VSA project, then add the script code it contains. To see an example of what's required, open a .dtsx file containing a Script Task and look for the tag "ScriptProject Name", and examine the ProjectItems it contains.

In case it is of use to anyone in the future, here's what I came up with. This doesn't create both the project and the script code because I didn't go that far, but it shows you the references and objects you'll need to do that.

Add references to Microsoft.SqlServer.ScriptTask and Microsoft.SqlServer.VSAHosting.

Dim exe As Executable = _Package.Executables.Add("STOCK:ScriptTask")

Dim thTask As TaskHost = CType(exe, TaskHost)

thTask.Name = "MyScriptTask"

Dim st As ScriptTask = TryCast(thTask.InnerObject, ScriptTask)

dim Moniker as String = "dts://Scripts/" & st.VsaProjectName & "/" & st.VsaProjectName & ".vsaproj"

st.ReadWriteVariables = "Var1, Var2"

Dim sb As New StringBuilder

sb.AppendLine("'Microsoft SQL Server Integration Services Script Task")

'build your code here

'this inserts the code into the script task

st.CodeProvider.PutSourceCode(Moniker, sb.ToString)

ActiveX&VB.net versus transformation tasks

Hello all -

Working on DTS packages we used to have two different ways of exporting data to spreadsheet... We could do it by a single transformation task or either writing an ActiveX script, after running a procedure.

I am able to cover the first way already, while dealing with SSIS... But, I wonder if it is worth to research on how I would write a vb.net code to load data into a spreadsheet (considering we are advised to try vb.net instead of ActiveX while working on these new packages).

It is always a huge discussion to know what is the best way of doing something so my point here is to hear some of you and decide if I just keep exporting data by doing transformation tasks or if I will ever go trying to deploy a vb.net code to do that... What is the best on performance, etc...?

Thanks in advance for all your help as always.

SSIS contains an Excel Destination that you can use.|||

Hi Phil.

Thanks for your reply. I know about the excel destination which is very much the same of doing transformation tasks in our old dts packages.

My question was... If I generate the spreadsheet through a vb.net code like we used to do with ActiveX scripts for dts packages, will I win anything on performance or something else? Example...

Working on dts packages we would always choose to generate a spreadsheet through procedures combined with ActiveX scripts because the procedure would have a query plan and would have less overhead, comparing with data transformation tasks. I don't know about SSIS because I am new here If you can add any insight to this I really appreciate that.

Thanks again.

|||I think you'll see better performance from the built-in Excel Destination. Script components are generally slower than pre-built components.

ActiveX Vbscript still ok in Yukon ?

Will I still be able to use my current VBscripts (Active X) code in dts
packages in Yukon ?Hi
In SQL Server 2005, there is an optional install of the SQL Server 2000 DTS
engine. this will allow the existing scripts to work (as long as you don't
use any T-SQL that is no longer supported in SQL Server 2005).
The new DTS engine in SQL Server 2005 is amazing and you will be very
tempted to convert your existing packages ASAP to the new engine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:I81xd.21621$rO2.720@.bignews1.bellsouth.net...
> Will I still be able to use my current VBscripts (Active X) code in dts
> packages in Yukon ?
>