Script Task Variables

script task: there should be another way to select variables than the comma seperated list

One has to type in a whole list of variables, hoping not to make any mistakes

IntelliSense for example?

But hey, I'm not complaining...

greets

There is! You can do it in code.

Writing to a variable from a script task
(http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx)

Still no intellisense though!!

I highly recommend you use the code option because this minimises the risk of variable locking. I plan to blog about this soon and have written about it in an upcoming article in SQL Server Standard.

-Jamie

|||

ok, I'll do it your way

thanks

|||

Dear Jamie,

Your method works fine for 1 variable.
I assume you know this, but for other readers of this thread:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_VariableDispenser.htm

quoting:

There are two scenarios for using the variable dispenser.

You want just one variable. In this scenario, call LockOneForRead or LockOneForWrite, and a collection with one element is returned.

You want several variables. In this scenario, call LockForRead and LockForWrite several times, one for each variable. This builds up two lists, one list that contains variables for reading and a list of variables for writing. Next, call GetVariables, which gives you a collection that contains all of the locked variables. If GetVariables succeeds, the two lock lists, which are the lists of variable names, not actual locks, is cleared.

To clear the locks, call Unlock on the collection when finished to explicitly release the locks. This unlocks the variables themselves. If GetVariables fails, the lists remain unchanged, and you can call GetVariables again. If you still do not succeed, call Reset to clear the lists and bring the variable dispenser back to its initial state.

Cheers,

Tom

Script task Error

I have a script task that is supposed to perform some task but it fails and throws this exception

"Unable to cast COM object of type System._OComObject to class System.Data.Odbc.odbcConnection". Instances of types that represent com components cannot be cast to the types that represent COM components; they can be caste to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the inteface".

Here is the code that throws this

Dim sqlString As String

Dim conn As Odbc.OdbcConnection

Dim da As Odbc.OdbcDataAdapter

Dim ds As Data.DataSet = New Data.DataSet()

sqlString = " SELECT count(*)FROM tmpDAY INNER JOIN tblData ON tmpDAY.CusID = tblData.datFKCusID WHERE(tmpDAY.Serial = tblData.datSerial)"

Dim connName As String = Dts.Connections(0).Name

Try

conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

da = New Odbc.OdbcDataAdapter(sqlString, conn)

Catch ex As Exception

MsgBox(ex.Message.ToString())

End Try

Any suggestion will be greatly appreciated?

you could try replacing your CType with a normal ODBC connection constructed from a string
i.e. replace
conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

with

Dim connString As String = Dts.Connections(0).ConnectionString

conn = New Odbc.OdbcConnection(connString)|||

replace:

conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

with:

conn = New Odbc.OdbcConnection(connectionString)

conn.Open()

Script Task and Variable Type

Hi,

I am using the following code in Script Task and it is giving me the error as shown below

FileCount is a variable defined as Int16 (with initial value = 0 ) and it is part of Read/Write variables portion of Script

Dim FileCount As Int16

FileCount = CType(Dts.Variables("FileCount").Value, Int16)

Dts.Variables("FileCount").Value = FileCount + 1 //Error on this Line

The type of value being assigned to @.User::FileCount differs from the current variable type. Variables may not change during execution.

Please Guide what is wrong with this.

The result produced from "FileCount + 1" is of type Integer, you are trying to assign it to type Short. Change the line to the following and it should work:

Dts.Variables("FileCount").Value = CShort(FileCount + 1)

Script execution problem

When I try to execute my script using the rs command i canâ't
Generally I use to type:
rs â's http://myServer/reporrserver ; â'i myScript.rss
but they are some predefined values that donâ't allow me to execute my
command because it tries to look for the server:
http://http://myServer/reporrserver/reporrservices
if I change it like this
rs â's myServer â'i myScript.rss
it also does not works because it interpreted it like
http://myServer/reporrservices
Where can I change this default values'I have the same problem in one more server. I donâ't know if it has something
to do. But In the server was the SP1_en and over it I stalled a SP1_de. It
always have the same problem:
Could not connect to server: http://my_server/ReportService.asmx
"Soan" wrote:
> When I try to execute my script using the rs command i canâ't
> Generally I use to type:
> rs â's http://myServer/reporrserver ; â'i myScript.rss
> but they are some predefined values that donâ't allow me to execute my
> command because it tries to look for the server:
> http://http://myServer/reporrserver/reporrservices
> if I change it like this
> rs â's myServer â'i myScript.rss
> it also does not works because it interpreted it like
> http://myServer/reporrservices
> Where can I change this default values'
>

Script databsae does not compatible with SQL2000

I've created a databse on SQL2005, and I did not use any new data type for columns. I script the database, and select SQL2000 for "Script for Server version", howvever, the script generated cannot be run on SQL2000, it can be only run on SQL2005.

The script generated by SQL2005 is not backward compatible? like SQL2000 can generated script compatible wtih SQL7.

CREATE TABLE [dbo].[Table1](
[Col1] [nvarchar](16) NOT NULL,
[Col2] [nvarchar](100) NOT NULL,
[Col3] [nvarchar](10) NULL,
[Col4] [datetime] NULL,
[Col5] [nvarchar](10) NULL,
[Col6] [datetime] NULL,
[Col7] [bit] NULL CONSTRAINT [DF_Table1_Col7] DEFAULT ((0)),
[Col8] [bit] NULL CONSTRAINT [DF_Table1_Col8] DEFAULT ((0)),
[Col9] [int] NULL CONSTRAINT [DF_Table1_Col9] DEFAULT ((0)),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Error detected on the line "IGNORE_DUP_KEY = OFF".

This is a known bug. From the feedback center:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a6510471-9c40-4184-9611-5656457864d8

Edit: I have tried it and it does seem to be fixed in the SP1 CTM

Louis

|||Generally, you should not rely on the scripting capabilities other than for ad-hoc stuff. It is better to maintain your scripts in a source code control system and use it for reference / maintenance. There are other problems with scripting other than syntax issue with the CONSTRAINT in your example. For example, resolving dependencies is not accurate since the engine itself doesn't guarantee it for all cases. There are also issues with expressions which are normalized / modified by the engine so if you try to compare the script generated from the engine with your source it will not match even though they produce identical results.|||I'm sorry, but I can't hold back. The condescension in your reply to this stranded user is almost palpable. These types of responses are absolutely maddening, especially when the bugs in the SQL Server 2005 tools are so *painfully* obvious upon even casual observation. People don't need lectures on why you shouldn't be using a feature YOU included in the server which doesn't work because YOU did incredibly inadequate testing before you shipped. Seriously, just about any script you create from SQL Mgmt Studio which you want to run on 2000 will not work because it uses sys.objects. That bug should have never been included in the release, and frankly is a sign of a real management problem inside your team which needs to be addressed.|||

I won't dispute the 2005 tools have errors comment, and neither did he (in fact I would sing in that chorus with you if need be). However, he post was not a "well, you shouldn't be using it anyway" comment, I don't think. He goes on to explain that some parts of scripting out objects is just really quite hard (like maintaining precedence of which script to run first.)

As a side note, the sys.objects issue has been changed in the CTP as well for the 2000 compatible scripting.

All he was trying to say was that the best way to use the tools and do development is to maintain scripts of your work, and not rely on the tools to script out a database. I doubt that he was suggesting to never use the scripting tools for any reason. (especially since he advocated it for ad-hoc stuff.) I know I use it quite often, if for no other reason than to post a table structure to a discussion. But for a production system you should generally should have scripts for all objects that were created outside of using the SSMS tools and then scripting the objects.

It is very common to include a mini-lecture with any post where someone sees that a person *might* be abusing a feature. I learned a ton from my early days in the newsgroups because a friendly user or two (and an unfriendly albeit, very intelligent, jerk) lectured me on how something should be done.

|||Thanks, ***. Fix your God damned product or don't ship it. SQL 2005 is ***.

Script component, dumb question

How do you tell the script task NOT to write a particular row?

This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Private iType, iRest As String
Private rawAmount As Double

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub

End Class

Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.

Thanks for any help or information.To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.

By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.

In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If True Then

Row.DirectRowToOutput0()

Else

Row.DirectRowToOutput1()

End If

End Sub

End Class

|||

Having problems with this. I have a script component in a data flow.

when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.

In the code, when I attempt to add code like this

Row.DirectRowToOutput1()Row.DirectRowToOutput1()

property DirectRow* is not available. I must be missing something.

Also, while on this, how does one insert additional rows?

|||

You can't insert new rows into a synchronous output, only into an asynchronous one.

If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.

|||

re "property DirectRow* is not available. I must be missing something."

I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs

Script component, dumb question

How do you tell the script task NOT to write a particular row?

This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Private iType, iRest As String
Private rawAmount As Double

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub

End Class

Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.

Thanks for any help or information.

To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.

By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.

In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If True Then

Row.DirectRowToOutput0()

Else

Row.DirectRowToOutput1()

End If

End Sub

End Class

|||

Having problems with this. I have a script component in a data flow.

when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.

In the code, when I attempt to add code like this

Row.DirectRowToOutput1()Row.DirectRowToOutput1()

property DirectRow* is not available. I must be missing something.

Also, while on this, how does one insert additional rows?

|||

You can't insert new rows into a synchronous output, only into an asynchronous one.

If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.

|||

re "property DirectRow* is not available. I must be missing something."

I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs