How to simulate an array in SQL Server

Just thought some might be interested in how to identify individual
items in an 'array' by declaring a local variable as table
----
declare @.MyTable table (MyRowID int identity, MyData varchar(255))
declare @.MyCurrentRow as int
declare @.MyRowCount as int
insert into @.MyTable select Name from sysobjects -- (or whatever)
set @.MyRowCount = @.@.RowCount
-- @.@.Rowcount hold the count of the last table in these cases anyway
-- then you can use statement to access the nth. row in the 'array'
select MyData from @.MyTable where MyRowID = 5 -- as an example
--
-- or step through the table using @.MyCurrentRow
Set @.MyCurrentRow = 1
while @.MyCurrentRow < @.MyRowCount +1
Begin
Select MyData from @.MyTable where MyRowID = @.MyCurrentRow
set @.MyCurrentRow = @.MyCurrentRow + 1
Continue
end
Just thought it might help. It isn't rocket science but I saw a few
with problems so thought I'd post.
I needed it to obtain individual values from a table and use those
values to call a stored procedure repeatedly.
CheersSee if these help:
http://www.aspfaq.com/show.asp?id=2248 Arrays & Lists
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
http://www.users.drew.edu/skass/sql...bleProc.sql.txt List to
Table script
Andrew J. Kelly SQL MVP
<mal_k100@.hotmail.com> wrote in message
news:1125573743.271138.167070@.o13g2000cwo.googlegroups.com...
> Just thought some might be interested in how to identify individual
> items in an 'array' by declaring a local variable as table
> ----
> declare @.MyTable table (MyRowID int identity, MyData varchar(255))
> declare @.MyCurrentRow as int
> declare @.MyRowCount as int
> insert into @.MyTable select Name from sysobjects -- (or whatever)
> set @.MyRowCount = @.@.RowCount
> -- @.@.Rowcount hold the count of the last table in these cases anyway
> -- then you can use statement to access the nth. row in the 'array'
> select MyData from @.MyTable where MyRowID = 5 -- as an example
> --
> -- or step through the table using @.MyCurrentRow
> Set @.MyCurrentRow = 1
> while @.MyCurrentRow < @.MyRowCount +1
> Begin
> Select MyData from @.MyTable where MyRowID = @.MyCurrentRow
> set @.MyCurrentRow = @.MyCurrentRow + 1
> Continue
> end
> Just thought it might help. It isn't rocket science but I saw a few
> with problems so thought I'd post.
> I needed it to obtain individual values from a table and use those
> values to call a stored procedure repeatedly.
> Cheers
>sql

how to Set variable value in sub package?

hi !

how to set variable value for sub package inside another package?

some thing like this:

xp_cmdshell 'dtexec /f \"D:\\SSISProject\\Integration Services Project1\\ArchiveMainMultiTables.dtsx\" /Set \package.Variables[User::ArchivePackageName].Properties[Value];\"ArchiveTicketLog\" /Set \package.Variables[User::ArchiveFileType].Properties[Value];\"text\" /Set \package.Variables[User::connectst].Properties[Value];\"Data/tSource=SE413695\AASQL2005;Initial/tCatalog=TestDB;Provider=SQLNCLI.1;Integrated/tSecurity=SSPI;\[ConnectionString]'

Main Package = ArchiveMainMultiTables.dtsx

sub packge = ArchiveTicketLog. when i try to execute the sub pacakge it thorughs up error saying the connection string in not set. i am setting in the parent connection string of the main package. when i execute through the pacakge itself it works fine but when i execute through the Sql command line it fails.

is there some thing to to "/Set [package name]\Package.Connections[ConnectStringArchiveTicket].Properties[Value];

please help

thanks

jas

Have you looked at parent package configurations?

Kirk Haselden
Author "SQL Server Integration Services"

|||

Some links here that may help as well:

Accessing variables in a parent package
(http://blogs.conchango.com/jamiethomson/archive/2005/09/01/2096.aspx)

Passing variables between packages
(http://blogs.conchango.com/jamiethomson/archive/2005/03/17/1151.aspx)

-Jamie

|||thanks

How To Set Multiple ReadOnlyVariables in Script Component in Integration Services 2005

Hello!

I'ave got a problem of setting more than one Variable in ReadOnlyVariables Property of ScriptComponent...I provide comma separated list of names ( As described in the help ) byt VS Studio Editor can not be opoened claiming that there is no a variablle with such a name...Looks like it doesn't treat the list as a collection of names...

Please help.

Vladimir

Make sure there are no spaces in the list.

Var1,Var2,Var3

This will not work:

Var1, Var2, Var3

Also note that variable names are case sensitive.|||Triple-check your spelling and the scope your variables are defined in. I got the error just this morning and it was a spelling problem.
|||

Thanks for your response...

I verified the spelling got rid of spaces...but result is the same

It is interesting thing.. I have only two variables: One is set on a package level and another is on the Data Flow Task level...

When I set one of them in ReadonlyVariables and another in ReadandWriteVariables it allows me to open VS for Applications. If I move both to the same location ( ReadonLy or ReadAnd Write with comma separation and no spaces ) it issues the message I described...

I tried specifying the namespaces for the variables, but with no Luck...

Not usre what to do...

Any ideas will be greately appreciated...

Thanks,

Vladimir

|||What version of SSIS are you using?

RTM? SP1? SP2?

how to set multiple local variable from single select statement

is it possible to set the value of multiple local variables using just 1
statement. For example assume a simple table 'E' with 3 columns 'empID',
'empName', 'empToken'. I can get their values with
declare @.ENAME as char(30), @.ETOKEN as int
SET @.ENAME = (SELECT empName FROM E WHERE empID = 1)
SET @.ETOKEN = (SELECT empToken FROM E WHERE empID = 1)
What I want to do is compine the two assignments into 1 single statement so
that the db never has to be looked up more than once. e.g.
SET @.ENAME, @.ETOKEN = (SELECT empName, empToken FROM E WHERE empID = 1)
is this possible and if it is, what is the synthax?try this.. hope this helps.
SELECT @.ENAME = empName,
@.ETOKEN = empToken
FROM E WHERE empID = 1
--|||HI,
sure:
SELECT @.ENAME = empName , @.ETOKEN = emptoken FROM E WHERE empID = 1
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--

How To Set a Variable During an Insert Into Select From

Hello.
I'm inserting rows into a table that I retrieve from another table.
There's a lot of data manipulation going on during this process.
For 10 columns in the Select From portion I'm using a CASE statement that
starts with CASE
WHEN Left(Discount_Specification, 2)= @.PF THEN etc.
END,
Instead of doing the "Left" 10 times (10 * 8 million rows in the "From"
table!) I though of setting a variable: Set @.MyVar =
Left(Discount_Specification, 2) and then
saying WHEN @.MyVar = @.PF etc.
I just don't know where in the logic to place this Set @.MyVar so it works
for each row that's inserted.
TIA,
RitaHard to say without DDL, but perhaps something like:
INSERT INTO ...
SELECT ds, ds + 'a', col2
FROM
(
SELECT LEFT(Discout_Specification, 2) AS ds, col2 FROM tbl
) AS t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:A07835AD-AAC2-4225-BA8C-FDA70C1C0631@.microsoft.com...
> Hello.
> I'm inserting rows into a table that I retrieve from another table.
> There's a lot of data manipulation going on during this process.
> For 10 columns in the Select From portion I'm using a CASE statement that
> starts with CASE
> WHEN Left(Discount_Specification, 2)= @.PF THEN etc.
> END,
> Instead of doing the "Left" 10 times (10 * 8 million rows in the "From"
> table!) I though of setting a variable: Set @.MyVar =
> Left(Discount_Specification, 2) and then
> saying WHEN @.MyVar = @.PF etc.
> I just don't know where in the logic to place this Set @.MyVar so it works
> for each row that's inserted.
> TIA,
> Rita
>|||Hi Tibor,
Thanks for your response.
I'm trying to figure out how to use it along with a CASE statement.
Here's my code:
INSERT INTO MyTable(
Col1,
Col2,
etc.)
SELECT
CASE
WHEN Left(SM.Discount_Specification, 2) IN (@.P, @.L) THEN
Something
ELSE 1
END,
CASE
WHEN Left(SM.Discount_Specification, 2) = @.K THEN
SomethingElse
ELSE 1
END,
Etc.
From MyTable
Thanks,
Rita
"Tibor Karaszi" wrote:

> Hard to say without DDL, but perhaps something like:
> INSERT INTO ...
> SELECT ds, ds + 'a', col2
> FROM
> (
> SELECT LEFT(Discout_Specification, 2) AS ds, col2 FROM tbl
> ) AS t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RitaG" <RitaG@.discussions.microsoft.com> wrote in message
> news:A07835AD-AAC2-4225-BA8C-FDA70C1C0631@.microsoft.com...
>|||Is this a "lazy programmer doesn't want to type all those keystrokes" issue
or something else? It is possible that "Left(SM.Discount_Specification, 2)"
indicates a schema issue. If so, you should consider a change to the schema
to unbind the two attributes currently stored in the Discount_Specification
column. This can be done permanently via the addition of another column
(and the movement of the associated information), via a view, or via a
computed column, via a udf, etc. You can also do this via a derived table
within this particular query.
insert ...
select case when derived_discount in (@.P, @.L) then x else y end,
...
from
(select Left(SM.Discount_Specification, 2) as derived_discount,
...
from MyTable ) as t1
where ...|||Hi Scott,
No, it's not a "lazy programmer"! :-)
I just thought there may be a more efficient way since I'm dealing with a
large volume of rows (up to 10 million).
Thanks for your reponse. That was what I was looking for.
Rita
"Scott Morris" wrote:

> Is this a "lazy programmer doesn't want to type all those keystrokes" issu
e
> or something else? It is possible that "Left(SM.Discount_Specification, 2
)"
> indicates a schema issue. If so, you should consider a change to the sche
ma
> to unbind the two attributes currently stored in the Discount_Specificatio
n
> column. This can be done permanently via the addition of another column
> (and the movement of the associated information), via a view, or via a
> computed column, via a udf, etc. You can also do this via a derived table
> within this particular query.
> insert ...
> select case when derived_discount in (@.P, @.L) then x else y end,
> ...
> from
> (select Left(SM.Discount_Specification, 2) as derived_discount,
> ...
> from MyTable ) as t1
> where ...
>
>