Script works in DTS, but has problems as a Stored Procedure

I have a script that builds multiple tables and then builds tables from those tables, etc..

Usually, I run the script as a DTS package, and it doesn't have any problems. However, when I save the script as a stored procedure, I think it is compiling the table builds into a different sequence.

As a result, some of the tables are blank when this script is run as a stored procedure.

Do I need to use transactions to prevent this compilation problem, or is there an easier setting that I can use to keep everything in the original sequence?

Thanks in advance.Anyone have any ideas on this? I can't use "GO", but I am still still having this compilation issue.sql

Script task: Dts.Logging.Log

I am trying to understand the use-case of Dts.Logging.Log.

Following BOL, I see that I can use Dts.Log to send a custom log message to my log provider. The following example code produces an event called "User:ScriptTaskLogEntry", with the message "My custom message here".

Dim dataBytes(0) As Byte

Dts.Log("My custom message here", 0, dataBytes)

I would like to be able to produce my own custom event (lets call it "MyCustomEvent"), as well as a custom log message. I thought it would be possible via Dts.Logging.Log, which requires that I specify an event name, in addition to a log entry. However, when I place the following code in the script task, I do not get any logging whatsoever.

Dim dataBytes(0) As Byte

Dts.Logging.Log("MyCustomEvent", System.Environment.MachineName, System.Environment.UserName, "Custom Log", "", "", "My custom message here", Now, Now, 0, dataBytes)

Any thoughts?

Thanks!

The script task can't log custom message type. The problem is that custom log message needs to be declared by task in advance so that it appears in the Details tab of the Logging window.

Custom task (reusable separately packaged .NET or C++ code, that appears on toolbox just like any Microsoft task) can do it, but this functionality is not available in Script task.sql

Script task raising OnError Events

Is there a way I can stop a script task of a control flow from raising OnError Events? or Is there a way I can Set Dts.Results to Fail without having the script task raise OnError event.

The reason why I need to fail the task is because I am using Precedence constraints and by setting the status to Fail I can stop the control flow. However, this action raises OnError event. I expect onError event to be raised if there are any exceptions in the script not when the Results is changed to fail.

I have tried capturing the failure into a dummy script, however the OnError event is still raised.

Try putting the code that raises the error in a TRY...CATCH block, that'll stop individual lines of code from throwing errors.
I'm pretty sure though that if you set Dts.TaskResult = Dts.Results.Failure then it'll trigger an OnError event.

-Jamie

|||Failing a task is designed for signalling an error,
and should not be used to drive regular (non
exceptional) control flow.

To alter control flow, a better solution is using
conditions on precedence contraints instead.

You can set some variable in the script task, then
add on-success or on-completed precedence constraint
with condition that checks that variable.

Hope this helps,
Michael [SSIS Technical Lead]|||There are better ways to stop the task without returning failure result from the script task. For example, you could have an expression on the precedence constraint that checks a variable value you set in the script for whether to go on or not.
Try that, it will solve both problems I think.
K|||I have tried setting expressions within precedence constraint and it works. But I find setting variable and changing variable's value little kludge and I am surprised that Script Task does not have a property that will fail precedence constraint without raising an error.

On the contrary end, I guess it makes sense to have script task raise an error if I set it to failure manually, since all the other tasks have similiar behaviour. That is if they fail they raise OnError event.

Thanks everyone.|||

Do you want to force a failure result without actually throwing an error? If so try the ForceExecutionResult property. It is a container property, so available on all tasks.

|||

Hi

I think ive got a similar problem.

We have set up an SSIS package which goes to an FTP site and downloads files.

Everything is fine... EXCEPT (lol) when there are no files to download. This then fails the task.

However, I want the package to continue to run.

Is there away of assigning the error message given to an expression and then using the expression in the precedence contraint?

thanking you in advance

David

sql

Script task raising OnError Events

Is there a way I can stop a script task of a control flow from raising OnError Events? or Is there a way I can Set Dts.Results to Fail without having the script task raise OnError event.

The reason why I need to fail the task is because I am using Precedence constraints and by setting the status to Fail I can stop the control flow. However, this action raises OnError event. I expect onError event to be raised if there are any exceptions in the script not when the Results is changed to fail.

I have tried capturing the failure into a dummy script, however the OnError event is still raised.

Try putting the code that raises the error in a TRY...CATCH block, that'll stop individual lines of code from throwing errors.
I'm pretty sure though that if you set Dts.TaskResult = Dts.Results.Failure then it'll trigger an OnError event.

-Jamie

|||Failing a task is designed for signalling an error,
and should not be used to drive regular (non
exceptional) control flow.

To alter control flow, a better solution is using
conditions on precedence contraints instead.

You can set some variable in the script task, then
add on-success or on-completed precedence constraint
with condition that checks that variable.

Hope this helps,
Michael [SSIS Technical Lead]|||There are better ways to stop the task without returning failure result from the script task. For example, you could have an expression on the precedence constraint that checks a variable value you set in the script for whether to go on or not.
Try that, it will solve both problems I think.
K|||I have tried setting expressions within precedence constraint and it works. But I find setting variable and changing variable's value little kludge and I am surprised that Script Task does not have a property that will fail precedence constraint without raising an error.

On the contrary end, I guess it makes sense to have script task raise an error if I set it to failure manually, since all the other tasks have similiar behaviour. That is if they fail they raise OnError event.

Thanks everyone.|||

Do you want to force a failure result without actually throwing an error? If so try the ForceExecutionResult property. It is a container property, so available on all tasks.

|||

Hi

I think ive got a similar problem.

We have set up an SSIS package which goes to an FTP site and downloads files.

Everything is fine... EXCEPT (lol) when there are no files to download. This then fails the task.

However, I want the package to continue to run.

Is there away of assigning the error message given to an expression and then using the expression in the precedence contraint?

thanking you in advance

David

Script Task Error?

I have the following code inside a Script task:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Collections

Public Class ScriptMain

Public Sub Main()

Dim nm As String = "ship_metrics_rpt_*.csv"

Dim files As ObjectModel.ReadOnlyCollection(Of String) = My.Computer.FileSystem. _

GetFiles(CStr(Dts.Variables("MainPath").Value))

If files.Count > 0 Then

Dim dic As Generic.SortedDictionary(Of Date, String)

For a As Integer = 1 To files.Count

If files(a) Like nm Then

Dim nfo As System.IO.FileInfo = My.Computer.FileSystem.GetFileInfo(files(a))

dic.Add(nfo.CreationTime, files(a))

End If

Next

If dic.Count > 0 Then

Dim FinalFiles(dic.Count - 1) As String

Dim count As Integer

For Each kvp As Generic.KeyValuePair(Of Date, String) In dic

FinalFiles(count) = kvp.Value

Next

End If

End If

Dts.TaskResult = Dts.Results.Success

End Sub

The following exception gets thrown at "If files.Count > 0 Then"

"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"

Why?

Thanks for your help

Has MainPath been passed in as a variable? It is scoped properly?|||

Yes it's been passed. You can insert "msgbox files.count" before "if files.count > 0" and get a count just fine. I don't understand it.

|||There are a number of problems here (easily fixed though)

1 . Fence post condition. The ReadOnlyCollection index starts at 0, not 1.
Change

For a As Integer = 1 To files.Count

To

For a As Integer = 0 To files.Count -1

2. Unitialized Variable:

Change

Dim dic As Generic.SortedDictionary(Of Date, String)

To

Dim dic As Generic.SortedDictionary(Of Date, String) = New Generic.SortedDictionary(Of Date, String)

|||Ah - I didn't catch the uninitialized part - that should fix it - thanks.

Script task

Hi,

I am new in SSIS development. In the script task of the Control Flow, how to use my custom classes (that use SMO and DTS for building a package and are written in C##?). If not the Script Task then which tool I have to use?

Natali Rozin wrote:

Hi,

I am new in SSIS development. In the script task of the Control Flow, how to use my custom classes (that use SMO and DTS for building a package and are written in C##?). If not the Script Task then which tool I have to use?

If you have compiled them into assemblies then you can reference them.

You should read this though:

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx)

-Jamie

|||

thank you very much Jamie.

Did you put your DDLs into the root of the Microsoft.Net folder? Through "Add Reference", I cannot find my DDLs I just put into C:\WINDOWS\Microsoft.NET\Framework

|||

Natali Rozin wrote:

thank you very much Jamie.

Did you put your DDLs into the root of the Microsoft.Net folder? Through "Add Reference", I cannot find my DDLs I just put into C:\WINDOWS\Microsoft.NET\Framework

Natali,

You should put it into the appropriate .Net Framework version folder (I always just pick the highest). e.g.:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

My apologies, the post I linked to above does not explain this does it?

-Jamie

|||

Thank you - all are working!

The problem was I put mine into C:\WINDOWS\Microsoft.NET\Framework\v3.0 and do not have VS SDK yet.

In C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 works well!

thanks again

script of dts?

hello, its possible make a sql script of a dts to install the dts in
other sql server instance?
Yes, the easiest way is to open the package. Then under the package
menu option, select save as. This will give you a dialog box with
options on how you can save it. You can save it directly to your other
SQL Server, or if you need this included in a deployment plan of some
sort, then you should save it to a file. Your options for saving are
in the "Location" dropdown. Hope this helps.

script of dts?

hello, its possible make a sql script of a dts to install the dts in
other sql server instance?Yes, the easiest way is to open the package. Then under the package
menu option, select save as. This will give you a dialog box with
options on how you can save it. You can save it directly to your other
SQL Server, or if you need this included in a deployment plan of some
sort, then you should save it to a file. Your options for saving are
in the "Location" dropdown. Hope this helps.

script of dts?

hello, its possible make a sql script of a dts to install the dts in
other sql server instance?Yes, the easiest way is to open the package. Then under the package
menu option, select save as. This will give you a dialog box with
options on how you can save it. You can save it directly to your other
SQL Server, or if you need this included in a deployment plan of some
sort, then you should save it to a file. Your options for saving are
in the "Location" dropdown. Hope this helps.

Script Objects on SQL Server

Hi..

I need a script to export all the objects from my DB, I can't use DTS because this server is on another private LAN , so the only thing that i can do is drop a *.sql file on a common sharing server and my other server will execute it from that location.

What do you recommend? I know i can do it from My Enterprise Manager but i need to run it on a schedule time ....

Anybody has a script for it ? Any clue?

Thanks a lot.
:DIf you don't plan to change your schema, then you can script your DB using EM and save the script as a file.

If changes are anticipated, you can look into using SQL-DMO and write your own script. You can also google "Script database" and am sure you'll find a couple of hits there.|||The schema is changing always ...

It's for the QA guys but they work on a private LAN ... They can't reach our Production LAN..|||Look up this file in your directory structure (scptxfr.exe). My personal edition has it in C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade directory.

Also, check http://database.ittoolbox.com/code/d.asp?d=1691&a=s|||In case you still wonder, this is a sample command line that would generate all objects of PUBS database:

"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s <SERVER_NAME> /I /d pubs /r /f C:\test.sql

script indexes

I have a monthly process where I replace all the data in certain tables. I
truncate the tables then DTS the new data in from another SQL server. I've
found that it's much faster to drop all the indexes on my tables and then
rebuild them at the end.
I'm looking to automate this process. It's easy to drop all the indexes in
a cursor - that's not really my issue. I'm currently using a vb app that I
wrote that uses DSO to generate a text file with the create index
statements, but it's not overly efficient because I don't want it to include
all tables - which it does. I've thought it might be more efficient to
query the sysindexes tables to find my indexes but I haven't found a great
way to save this information prior to dropping the indexes. I have no
objections to creating a table to store this information in.
I'm wondering if anyone has a suggestion for this?
Thanks in advance,
AndreBy DSO, do you mean SQL-DMO? If so, then why don't you iterate through
the collection of tables in your VB app, and only script out the
indexes for the tables of interest? You can do this in an ActiveX task
in the DTS package, saving the script to a global variable, and then
recreate the indexes using another ActiveX package to execute the SQL
statements.
I guess it sounds like you have all of the right elements to do what
you want to do; I'm not sure what the roadblock is.
Stu|||Yes, I do mean DMO - my mistake. You'll have to forgive me as I'm just a
rookie programmer and don't know DMO thoroughly. I don't know how to loop
through and get only the tables I'm interested in. This is a snippet of the
code I'm using to get my indexes:
For Each View In svr.Databases("MyDb").Tables
If InStr(View.Name, "sys") = 0 Then ' exclude system tables
Err.Clear
' add index info to text file
Print #1, View.Script(SQLDMOScript_Indexes)
End If
How would I modify it to get the indexes for specific tables? And how would
I store this in a global var?
Thanks for your help.
Andre|||First, I'd recommend that you get a book on DTS programming; there are
a couple of good ones out there. They'll help you get a grip on using
global variables, etc.
Second, here's a quick stab at some VBScript that may point you in the
right direction. If the tables are few, you can get by with SELECT
CASE. If you have a great deal of tables, you'll probably want to
write a function to look up the table name from an array.
Dim sql
SET SQLServer = CreateObject("SQLDMO.SqlServer")
SET Database = CreateObject("SQLDMO.Database")
SET Table = CreateObject("SQLDMO.Table")
SET Index = CreateObject("SQLDMO.Index")
SQLServer.LoginSecure = TRUE
SQLServer.Connect ServerName
For Each Database in SQLServer.Databases
If Database.SystemObject = False Then
Set filetxt = filesys.CreateTextFile(FilePAth & ServerName & "\" &
Database.Name & ".sql", True)
sql = Database.Script
sql = sql & CHAR(10) & "USE " & Database.Name & CHAR(10)
For each Table in Database.Tables
SELECT CASE Table.Name
CASE "Table1 you want"
For each Index in Table.Indexes
sql = sql & Index.script(532676612)
Next
CASE "Table You don't want"
END SELECT
Next
End if
Next
SQLServer.Disconnect
SET Index = NOTHING
SET Table = Nothing
SET Database = NOTHING
SET SQLServer = NOTHING
HTH,
Stu|||Hi Stu
I'm just now making my way back to this project and I seem to remember that
you had responded to this thread of mine with some example code. However I
don't see it anymore. I'm wondering if you could re-post it here?
Thanks very much.
Andre|||http://groups.google.com/group/micr...218b84be5a282a6
should take you to it. If not, let me know.|||Awesome, thanks Stu!
Andre