Changing the value of a variable

I'm looking for all possible ways to change the value of a variable.

I know this ways:

- edit direct

- configuration file

- scripting task

- scripting component

It is possible to change the value with other components?

Can we assign the output of Execute SQL Task to a variable?|||

It is unclear whether you are looking for tasks/components that change the value of a variable or whether you are looking for ways that it can be done. Any task/component can theoretically change the value of any variable in scope (excluding system and read only variables of course). Additionally expressions can change the value of a variable and as you mention configurations can as well. Also the ForEach loop via its variable mappings will change variable values.

Off the top of my head the sql task, row count component, execute dts package task, and recordset destination change variable, but I wouldn't say this list is exhuastive.

Matt

|||

@. Matt:

I'm looking for a general overview. Perhaps there is an overview in BO or in a blog or anywhere else.

@.Nitesh:

try this:

1. Define a variable with the datatyp = Object

2. Set ResultSet to Single Row

3. At the ResultSet-Tab add a line and configure your variable to a Outputname

4. Write a SQL-Statement : select max(abst) as output from art

Loom

|||

There is a walkthrough of the Exec SQL Task here
(http://www.sqlis.com/default.aspx?58)

Another variable setting solution is the ExecValueVariable. Several tasks support this, which is a way to specify a variable into which the task puts some form of execution result data, for example the Transfer SQL Server Objects Task puts the number of objects transferred. These can be usefull, although MS's own implementation seems to be a bit sparse, but use them when you can.

sql

Changing the OPEN OBJECTS variable

SQL 2000 error in event log... 2 events as follows:
1. OPEN OBJECTS parameter may be set to low
2. Run sp_configure to change parameter.
How can I see what it is set to now? How can I change this value?
TIAThis is documented in Books on line... - search for 'Setting Configuration
Options'
It is fairly unusual ( in my experience) to need to change this in sql
2000...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>|||(1) exec sp_configure
(2) exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
-- Keith
"_M_" <here@.gone.com> wrote in message =news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> > 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> > How can I see what it is set to now? How can I change this value?
> > TIA
> >|||Ran the "exec sp_configure"
Check the value for 'open objects'
Was set to 1000
Ran a query in QA as follows...
Exec sp_configure 'show advanced options', 1
Go
RECONFIGURE WITH OVERRIDE
Go
Exec sp_configure 'open objects', 0
Go
RECONFIGURE WITH OVERRIDE
Go
Re-Ran the "exec sp_configure"
Value is now shown as 0, WHICH IS DYNAMIC, CORRECT '
The thing that bothers me is that it said to run RECONFIGURE 2-3 times in
the output in QA... Did it actually change it, or do I have to run
RECONFIGURE WITH OVERRIDE by itself ?
TIA
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:#JRyCeXbDHA.2412@.TK2MSFTNGP10.phx.gbl...
(1)
exec sp_configure
(2)
exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
--
Keith
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>|||It prints it event if you run it, as long as you "know" you ran it you can
ignore the extra messages.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:uH1No6YbDHA.2672@.tk2msftngp13.phx.gbl...
Ran the "exec sp_configure"
Check the value for 'open objects'
Was set to 1000
Ran a query in QA as follows...
Exec sp_configure 'show advanced options', 1
Go
RECONFIGURE WITH OVERRIDE
Go
Exec sp_configure 'open objects', 0
Go
RECONFIGURE WITH OVERRIDE
Go
Re-Ran the "exec sp_configure"
Value is now shown as 0, WHICH IS DYNAMIC, CORRECT '
The thing that bothers me is that it said to run RECONFIGURE 2-3 times in
the output in QA... Did it actually change it, or do I have to run
RECONFIGURE WITH OVERRIDE by itself ?
TIA
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:#JRyCeXbDHA.2412@.TK2MSFTNGP10.phx.gbl...
(1)
exec sp_configure
(2)
exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
--
Keith
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto Porter
On Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing global variable based on day of the week

I'm new to SSIS so please be gentle...

I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?

Thanks in advance!

Is that the only file on the site? If so it is easier to just download it using a wildcard *.zip. Then use a for each loop to process each file in the folder which will pick up the filename for you.

Even if it's not it's probably better to check all files on the site and process those which you haven't processed before.

What happens on bank holidays.

|||

Thanks for replying. Unfortunately it is not the only file (they keep them for archival purposes so there are hundreds). Is there an easy way to track which files I've processed (flag/switch) or are you suggesting a directory comparison or storing file names in a table somehow (not sure how to compare?)?

If there is no file, it just emails a failure notice and the process would be run manually as it is now (not the most efficient way but we can get to bells and whistles later).

You can disregard this question. I actually found the solution in a posting from "killerless" on 8/30/2006. He/she actually did pretty much the exact expression I needed to calculate the day. I love forums! :-)

|||

I would use a ForEachLoop conatiner with an expression in FileSpec property. The FileSpec property overrides the 'files' entry in the conatiner GUI. The expression should have the logic to get the right file name based on the system or execution date. I think sothing like this should work:

Datepart("dw", @.[User::CurrentDate] ) == 2 ?
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -3, @.[User::CurrentDate])),2) + ".zip"
:
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -1, @.[User::CurrentDate])),2) + ".zip"

It says someting like: if its friday; then substarct 3 days otherwise substract 1.

Put all the logic inside of the container. Let me know if you want the sample package.

|||It looks like my answer got too late |||Sorry! I do appreciate the effort!

Changing DB with a script using a variable.

I am trying to write a TSQL Script for maintenance that willl read the
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCT
You can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT

Changing DB with a script using a variable.

I am trying to write a TSQL Script for maintenance that willl read the
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCTYou can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT|||Hi,
the problem with your command is, that EXEC will open a connection, change
the database on close the connection again. EXEC will use another scope than
the actual script is running in. As Manikanth said, you will have to include
the Script within your EXEC call to execute it on the same execution
context.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT
>

Changing data types of a variable in an expression

Greetings once again SQL friends,

Sorry to bug you with my silly questions but this little problem has been driving me crazy for the last hour or so!

I am attempting to change the data type of a variable in an expression but I keep getting an error saying that the expression can not be evaluated.

The syntax I am using is as follows :

(dt_str) @.[User::MAX_OFFER_PRICE_ID]

My variable MAX_OFFER_PRICE_ID is type int32 and I am attempting to cast it as a string.

What am I doing wrong?!

Thanks for your help in advance.

Try,

(dt_str, 6, 1252) @.[User::MAX_OFFER_PRICE_ID]

If you're using the expression builder then expand "Type Casts" in the top right hand corner and you'll see the required syntax.

-Jamie

|||

Hi Jamie,

You are my SSIS hero now

Thanks very much mate.

|||

Hi Jamie,

Just wondering, if I want to use retrieve another value from a different table, would I have to create yet another SourceSQL variable?! I am trying to think of a method that would allow me to re-use the sqlSource rather than create a new one.

My initial thoughts (after some reading) is to use a configuration file but I have not figured out the finer details of this solution.. God help me!!

Have you come across this kind of thing before? Do you have any suggestions?

Please forgive me for pestering you with my questions but I am eager to learn as much as I can about this great tool. As much as I am annoyed (for spending ages tryi8ng to work out simple thigs), I am even more determined to learn more about this beast of a tool.

Thanks for all the advice.

|||

You say "yet another SourceSQL variable" as if its a bad thing :)

If you need to extract data from a different table then create another variable - no problem with doing that.

-Jamie

|||

Hello there,

I have read your questions and i must say that i sympathize with you alot!!!

I have spent hours trying to figure out the logic behind the syntax and i must say that even though the examples appear simple in books online, they become complex to decipher when I start to get these error messages from the parser.

would you be able to share with me some of the simple syntax logic behind these expressions?

Thank you!!

|||

The syntax will likely appear familiar to C programmers, as the grammar is similar to the C language. Beyond that, the books online has a pretty good reference.

If you run into any specific errors that perplex you, please do post the expression and the error message on these forums. Most likely someone will be able to help you, and furthermore it will help the SSIS team identify cases where the error messages are not as helpful as they should be.

Thanks
Mark

Changing data types of a variable in an expression

Greetings once again SQL friends,

Sorry to bug you with my silly questions but this little problem has been driving me crazy for the last hour or so!

I am attempting to change the data type of a variable in an expression but I keep getting an error saying that the expression can not be evaluated.

The syntax I am using is as follows :

(dt_str) @.[User::MAX_OFFER_PRICE_ID]

My variable MAX_OFFER_PRICE_ID is type int32 and I am attempting to cast it as a string.

What am I doing wrong?!

Thanks for your help in advance.

Try,

(dt_str, 6, 1252) @.[User::MAX_OFFER_PRICE_ID]

If you're using the expression builder then expand "Type Casts" in the top right hand corner and you'll see the required syntax.

-Jamie

|||

Hi Jamie,

You are my SSIS hero now

Thanks very much mate.

|||

Hi Jamie,

Just wondering, if I want to use retrieve another value from a different table, would I have to create yet another SourceSQL variable?! I am trying to think of a method that would allow me to re-use the sqlSource rather than create a new one.

My initial thoughts (after some reading) is to use a configuration file but I have not figured out the finer details of this solution.. God help me!!

Have you come across this kind of thing before? Do you have any suggestions?

Please forgive me for pestering you with my questions but I am eager to learn as much as I can about this great tool. As much as I am annoyed (for spending ages tryi8ng to work out simple thigs), I am even more determined to learn more about this beast of a tool.

Thanks for all the advice.

|||

You say "yet another SourceSQL variable" as if its a bad thing :)

If you need to extract data from a different table then create another variable - no problem with doing that.

-Jamie

|||

Hello there,

I have read your questions and i must say that i sympathize with you alot!!!

I have spent hours trying to figure out the logic behind the syntax and i must say that even though the examples appear simple in books online, they become complex to decipher when I start to get these error messages from the parser.

would you be able to share with me some of the simple syntax logic behind these expressions?

Thank you!!

|||

The syntax will likely appear familiar to C programmers, as the grammar is similar to the C language. Beyond that, the books online has a pretty good reference.

If you run into any specific errors that perplex you, please do post the expression and the error message on these forums. Most likely someone will be able to help you, and furthermore it will help the SSIS team identify cases where the error messages are not as helpful as they should be.

Thanks
Mark