Asymmetry in "Execute SQL Task"?

Anyone know why the Execute SQL Task is only available under Control flow and not Data flow? (At times it's a useful data manipuation task and I wish was available as a Data flow task as well.)

Barkingdog

It could lead to some extremely inefficient data flows.

Imagine executing a SQL statement for every record in a 1,000,000 row transformation?

Or what if you decided to execute an update against the source data in your transformation?

The posiibilities for abuse are staggering...

If you need to run a SQL task doing it before or after a transformation allows you to execute SQL on the entire set... Not just a single record.

|||ssis provides the ole db command data flow transformation component for sql-based data manipulation: http://msdn2.microsoft.com/en-us/library/ms141138.aspx

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round.
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round.
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

Assign expression value using code

I am trying to use a variable to set an attribute value of an SSIS task but I keep running into the 4000 character limit of the string variable. Not sure why the variable which is of .NET type String has this limit when it doesn't when you are in a .NET environment. Regardless, can anyone provide some sample code that I could use to do this in a script task? I am trying to set the QueryString property of the Data Mining Query Task. All help would be appreciated.

Thank you in advance.

Variables and expressions in SSIS are limited to 4000 bytes. Sorry.|||As far as dynamically setting the QueryString, when you look at the properties of the Data Mining Query task, is there an "expressions" parameter? If you expand the expressions parameter, do you have an option for QueryString? (I'm not sitting in front of SSIS at the moment, so I can't verify.) If so, you may want to put your variable there instead of using a script.|||

All task properties support expressions, it is only Data Flow components that require the developer to actually set some code that says an expression is supported.

The problem is that the result of an expression cannot be greater than 4000 characters. Using a variable whose value is greater than 4000 characters will not work because this still has to pass through the expression evaluator to be assigned to the property value.

Since the DM Query Task does not offer anything other than a literal string for the query, this cannot be set dynamically within the package. I think this is a limitation, obviously it would be nice to have expressions with > 4000 characters but also tasks should be (consistently) developed with properties such as this accepting literals, variables and files. An ideal example is the Execute SQL Task with the SourceSQLType property that describes the interpretation of the SQL Statement property.

|||That is too bad. Hopefully this will change in a later version. Thanks for the help.

Assign a value to a variable without using SQL Task

Greetings once again,

I am trying to achieve a seemingly simple task of assigning datetime value to a user variable at the point my package starts running. How can I do this without using a SQL Script Task? Should I be using a script task for this or is there a simpler way to achieving the same thing?

Thanks in advance.

There are a couple System variable that might have have what you are looking for: StartTime and ContainerStartTime. To see them go to variables and click in the gray icon with the X.|||

Hi Rafael,

Yes thank you. I am now using the System variable StartTime which is the start time of the package when it's run.

That's perfect!

assertion task or something like it

I am playing around trying to develop a thing in SSIS that would be like an assertion elsewhere -- e.g., test a precondition and raise an exception if the precondition isn't true. I'm posting this to see if folks have already done something like this or have suggestions how to go about it in a semi-general way that could be re-used.

My toy thingamabob right now has the goal of comparing the grand totals for a numeric field in two xml files and raising an exception if they aren't equal. I've implemented it as two XML tasks, each of which uses XSLT to compute the total and post it as a string into a variable, then a script task to convert the string variables into numbers, compare them, and take appropriate action.

chw

Nothing in the box, and nothing else I have seen. Script tasks and a "debug" variable taht you set yourself would seem to be the obvious method. Perhaps use an environment variable or similar configuration method to set the Debug variable.