Scripting ALTER TABLE

I need to create a script to disable all triggers and constraints in my
database.

It appears as though I cannot use a local variable for the table name in the
ALTER TABLE statement (e.g. ALTER TABLE @.TBL).

Is there any reason for this?

Thanks,

Kevin"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>I need to create a script to disable all triggers and constraints in my
>database.
> It appears as though I cannot use a local variable for the table name in
> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
> Is there any reason for this?
> Thanks,
> Kevin

You can't use variables in place of table or column names, except when using
dynamic SQL, which has its own issues. Although if you're a DBA running an
admin script, then it's usually a reasonable option - there's more
discussion here:

http://www.sommarskog.se/dynamic_sql.html

Unfortunately, you don't say what your goal is, but if it's to load data
into the database, then all the usual loading tools (bcp.exe, DTS, BULK
INSERT) can ignore both constraints and triggers, so you might not need a
script anyway.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ecd751$1_1@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>>I need to create a script to disable all triggers and constraints in my
>>database.
>>
>> It appears as though I cannot use a local variable for the table name in
>> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
>>
>> Is there any reason for this?
>>
>> Thanks,
>>
>> Kevin
>>
> You can't use variables in place of table or column names, except when
> using dynamic SQL, which has its own issues. Although if you're a DBA
> running an admin script, then it's usually a reasonable option - there's
> more discussion here:
> http://www.sommarskog.se/dynamic_sql.html
> Unfortunately, you don't say what your goal is, but if it's to load data
> into the database, then all the usual loading tools (bcp.exe, DTS, BULK
> INSERT) can ignore both constraints and triggers, so you might not need a
> script anyway.
> Simon

I'll look into it. I'm planning on converting existing data into a new
format. Since I have to identify each table and write a query to do the
conversion, I could easily do a copy/paste for each table to disable and
re-enable the triggers and constraints. I am hoping to shortcut some of the
work by automating that piece.

Thanks,

Kevin

script USE help

I have a script that has a local variable called @.DB which holds the name of
the database to use, like this:
DECLARE
@.dbname varchar (256);
set @.dbname = 'DBNAME_REPLACEONINSTALL'
Our GUI installation modifies the value of this script. This is validated by
testing the value of the variable, using:
IF @.dbname like '%REPLACEONINSTALL%'
RAISERROR('Script is invalid. Must be run through GUI installer or local
variables at top of script must be replaced manually. Exiting database
create', 20, 1) WITH LOG;
else
select 'Script validated, proceeding' as STEP;
I then issue the USE command, using:
use @.DBNAME
DDL SQL..
DDL SQL..
DDL SQL..
The script fails due to this:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.dbname'.
I am guessing that you cannot use local variables in a USE command. Is this
true?
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:67AAD8FB-EDA2-4CE7-BDAC-93E8EC322185@.microsoft.com...
> I am guessing that you cannot use local variables in a USE command. Is
this
> true?
That is correct... You'll probably have to use dynamic SQL with the USE and
the DDL in a single batch.

script USE help

I have a script that has a local variable called @.DB which holds the name of
the database to use, like this:
DECLARE
@.dbname varchar (256);
set @.dbname = 'DBNAME_REPLACEONINSTALL'
Our GUI installation modifies the value of this script. This is validated by
testing the value of the variable, using:
IF @.dbname like '%REPLACEONINSTALL%'
RAISERROR('Script is invalid. Must be run through GUI installer or local
variables at top of script must be replaced manually. Exiting database
create', 20, 1) WITH LOG;
else
select 'Script validated, proceeding' as STEP;
I then issue the USE command, using:
use @.DBNAME
DDL SQL..
DDL SQL..
DDL SQL..
The script fails due to this:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.dbname'.
I am guessing that you cannot use local variables in a USE command. Is this
true?"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:67AAD8FB-EDA2-4CE7-BDAC-93E8EC322185@.microsoft.com...
> I am guessing that you cannot use local variables in a USE command. Is
this
> true?
That is correct... You'll probably have to use dynamic SQL with the USE and
the DDL in a single batch.sql

Script to assign task name to a variable

Hi,

I have a OnPre Execute event on a task in SSIS. I want to be able to assign the parent task name to this event to a variable.

I am using a script task. Are there any examples available on how to achieve this?
I have looked at the DTSProperty, but have to admit that I am struggling with this.

Once I have assigned the taks name to the varaible, I can then use my bubble up error handler, and still be able to log the name of the task which caused the error.

Many thnaks

RudyThe name of the task (more accurately - the container) that threw the event is available in the System::SourceName variable in the eventhandler.

Is that what you want?

Here's an example of doing something very similar: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

-Jamiesql

Script task User defined variable and Data Reader Source SQL Statement

Hello,

Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?

The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.

The SQL command in the custom properties doesn't cross reference back to the variable.

If anyone can help or needs further detail to help please post a resonse.

Thanks,

Marcus Simpson

The SqlCommand property can be overridden with a property expression. For data flow components expression are exposed on the parent Data Flow task rather than at the individual component level.

If you are not already familiar with expressions, look them up in Books Online.

To quickly set an expression, select the Data Flow task and hit F4. Then find the Expressions property in the property grid (as shown by the F4 key) and then select the property for the your DR source, SqlCommand. The name will be something like [DataReader Source].[SqlCommand]. Now add an expression that uses the variable as part of the command.

|||

Darren,

Thankyou very much for your response. It was correct.

Marcus.

|||

Marcus Simpson wrote:

Darren,

Thankyou very much for your response. It was correct.

Marcus.

Please mark Darren's post as answered.

Script Task Not Updating Package Variable

I am trying to update a package variable. The package consists only of a script task and a package user variable. I have included the variable, myVar (scope: package; type: string), in the ReadWriteVariables property of the script task.

The only code I have used, in Public Sub Main, is:

Dts.Variables("myVar").Value = "2"

The package runs successfully but the variable does not change. I thought that maybe the underlying value really does change even though the value as seen in the package variables window does not (I tested this in another package/solution but it does not seem to - not even during runtime).

I also tried running the variabledispenser method but this resulted in the package running continuously until I stop debugging.

Any suggestions greatly appreciated.

Regards,

Puzzled Again

Hi, Create a local (within script) variable that is a copy pacakge. The following is how I populate a variable in a parent package(failflag), from a variable in a child package(childfailvalue). This script in the child package.

Dim localfailflag As Variable = Dts.Variables("failflag")
Dim localchildfailvalue As Variable = Dts.Variables("childfailvalue")
localfailflag.Value = localchildfailvalue.Value

|||

Thanks Craig. I tried your code but it didn't work...probably because I didn't include other parts of code that were required.

I changed the routine to using an Execute SQL Task to update the package variable and this seems to have worked.

Regards,

Puz

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 component: copy a row into a variable

HI, is it possible to copy a row from the Input0_ProcessInputRow into another variable of a synchronous script component. I have a script component that has three outputs. What I would like to do is the following:

At first iteration, copy entire row into a variable.

For the other rows, is business key cols is different from the previous row (the one copied at previous iteration) or type 2 columns :

1-direct previous row (from the variable) to second.output
2- direct current row to the first output
3- copy the current row into the variable

If the business key cols are the same and type 2 cols are the same:
1-Direct row to the third output

My problem is that when I declare a variable as Input0Buffer and then copy the current row in it, at each subsequent iterations, the variable is updated. What I want to do is to keep the value of the previous row. I know I can do it b are differenty copying the content of all columns into separate variables but since some script components will have 150+ columns, I would like to find a simpler way to do it (like a copy of the structure of the input buffer).

The reason I use a script component is that the lookups are not updated dynamically, the rows I need to process may not exist in the target table and once inserted, they are not retreived subsequently by the buffer.

Thank you,
Cco

ccote wrote:

My problem is that when I declare a variable as Input0Buffer and then copy the current row in it, at each subsequent iterations, the variable is updated. What I want to do is to keep the value of the previous row. I know I can do it b are differenty copying the content of all columns into separate variables but since some script components will have 150+ columns, I would like to find a simpler way to do it (like a copy of the structure of the input buffer).

You should not use the buffer that was passed to you outside of the ProcessInputRow call - the data flow engine manages the buffer lifetime, and by trying to use it outside of this call you are breaking the rules.

Try copying the columns to another data structure, e.g. an array.

|||

HI, thank you for your reply. I knew I could copy the row's column to another structure (array, list, variables) but I was wondering If I could copy the whole row into a kind of "row" structure and be able to later direct it to an output. It would have been great since as I said, some of my tables have 150+ columns. I guess, the only way I can manage this is to actually add type1 and type 2 columns to my output and accumulate previous row type1 and type2 attributes into variables. So when the business key changes, I would copy variables into current row and have a conditionnal split that further filter out if any action have to be taken.

I could also use asynchrounous script but there I need to recreate all my 150+ table structure into output buffers. Actually, the real problem is that the lookup transform cache is not updated as new rows are added to the target table. That is why I need to do some custom code like this.

Thank's again,
Ccote

Script component and variable!!

Hi All,

I have developed a simple package utilizing script component and variable. What I am trying to do is declare a variable in the parent package that can be change at run time to either 1 or 0. If 1 I want the package to succeed and branch off to run some child packages and if 0, then branch the other way and run another child package. In ether way, I want to be able to load one of the two child packages and not both.

I I declare a variable in my parent package like IsExist, int32 with default 0.

Then I have this code thanks partly to Jamie Thomson in " SSIS: Writing to a variable from a script task"

Public Sub Main()

'

' Add your code here

'

Dim t As Integer

Dim vars As Variables

Dts.VariableDispenser.LockOneForWrite("IsExists", vars)

t = CInt((Dts.Variables("IsExists").Value))

'Dts.Variables(IsExists).Value = t

If t >= 1 Then

vars.Unlock()

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

End Sub

End Class

I also place the variable name in the ReadWriteVariables in the script component.

The problem now is it is always failing. It stopped at the script task and fail. This is not what I want. I want the package to execute another task upon failure. Anyone knows what I am doing wrong?

Hi,

You can achieve this without using a script task. Simply use expressions on your precedence constraints to check the value of IsExists. Here's how: http://www.sqlis.com/default.aspx?306

Oh, and you may want to make IsExists a boolean variable as well.

-Jamie

|||

Thanks alot Jamie. I tried using the Precedence constraint by seting the @.IsExists to 1 on one flow and 0 on the other but it is always running the one for 0. This is without using script task.

Again, when I use a script task with the following code, it gave me bunch of errors:

Public Sub Main()

Dim vs As Variables

'We need to lock the variables so we can read it without anything else changing it
Dts.VariableDispenser.LockOneForWrite("IsExists", vs)

'Assign it a value
vs.Item("IsExists").Value = (Dts.Variables("IsExists").Value)

'remember to unlock the variable now
vs.Unlock()

Dts.TaskResult = Dts.Results.Success
End Sub

Thanks

Omon

|||

OK, so you're using the script task to set the variable as well. I see.

What errors are you getting?

|||

Thanks jamie,

Yes. I used below script to set the value:

Dim vs As Variables

Dim t As Boolean

'MsgBox("variable_Passed)

t = CBool((Dts.Variables("IsExists").Value))

If t = True Then

'MsgBox("Condition_Stage")

Dts.TaskResult = Dts.Results.Success

'MsgBox("Success")

Else

Dts.TaskResult = Dts.Results.Failure

'MsgBox("Failure")

End If

So I was getting conversion error and when I placed CBool in front of the IsExists like: CBool((Dts.Variables("IsExists").Value)) it worked. I had to set

LockOneForWrite in the script task instead of using the code.

Again, I also had to select Logical OR in the presedent editor . This fix my problem.

I think I am all good for now.

Thanks very much.

Omon