goto Statement in DTS ?

Hello,

Can anyone plz tell me how to use GoTo statement in DTS packages ?

Thanks in advance !

I dont think goto stmt works in DTS !

goto Statement in DTS ?

Hello,

Can anyone plz tell me how to use GoTo statement in DTS packages ?

Thanks in advance !

I dont think goto stmt works in DTS !

Good reading

Can someone please recommend some good reading?
Books that go in depth about administering SQL Server 2000 and all the tools like EM, DTS, Profiler......
SQL Server 2000 Fast Answers for DBAs and Developers is a good book:
http://vyaskn.tripod.com/sql_server_...st_answers.htm
Inside SQL Server 2000, and Sams Teach Yourself Microsoft SQL Server 2000 in
21 Days are good too. You can find links to these at:
http://vyaskn.tripod.com/sqlbooks.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"cinderella" <cinderella@.discussions.microsoft.com> wrote in message
news:6D326BD9-B2B7-48B7-BE7F-CCF69770AF36@.microsoft.com...
Can someone please recommend some good reading?
Books that go in depth about administering SQL Server 2000 and all the tools
like EM, DTS, Profiler......

Good Question

I have some Lotus Notes files that I saved as Excel spreadsheets. I migrated
the files to SQL using DTS. One of the files has a table with 2 columns as
follows:
Room ID Number of Beds
201 3
202 2
I need to add a column to that table that will look into the combination of
each RoomID and Number Of Beds and will look like this:
Room ID
201A
201B
201C
202A
202B
So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
and 201C and so forth for the other rooms. What code I can use to accomplish
this?
Thanks a million
--
TSUse an auxiliary numbers table.
Example:
use northwind
go
create table t1 (
room_id int not null unique,
number_of_beds int not null check (number_of_beds between 1 and 5)
)
go
insert into t1 values(201, 3)
insert into t1 values(202, 2)
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a
cross join
sysobjects as b
go
declare @.s varchar(255)
set @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select
ltrim(room_id) + substring(@.s, n.number, 1)
from
t1
inner join
number as n
on n.number <= t1.number_of_beds
order by
room_id,
n.number
go
drop table t1
go
drop table number
go
AMB
"TS" wrote:

> I have some Lotus Notes files that I saved as Excel spreadsheets. I migrat
ed
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination o
f
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to accompli
sh
> this?
> Thanks a million
> --
> TS|||Hello TS,
CREATE TABLE [dbo].[Rooms] (
[RoomID] [int] NULL ,
[NumberofBeds] [int] NULL
) ON [PRIMARY]
GO
select * from Rooms
GO
RoomID NumberofBeds
-- --
200 3
201 4
202 2
select A.RoomID, B.Division
from Rooms A
Inner Join ( Select 'A' 'Division',1 'Sequence'
UNION ALL
select 'B',2
UNION ALL
select 'C',3
UNION ALL
select 'D',4
UNION ALL
select 'E',5
UNION ALL
select 'F',6 ) B
ON B.Sequence <= A.NumberofBeds
Thanks,
Gopi
"TS" <TS@.discussions.microsoft.com> wrote in message
news:07AF5AB5-BFD0-4E5D-90CE-316F267DAF4B@.microsoft.com...
>I have some Lotus Notes files that I saved as Excel spreadsheets. I
>migrated
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination
> of
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to
> accomplish
> this?
> Thanks a million
> --
> TS|||Here is an example of creating a table with the two fields and populating th
e
standard values. Then modifying the table to add new column and updating th
e
value of that column depending on the number of rooms.
Create table #Table1
(
RoomID nvarchar (3),
NumOfBeds nvarchar (1)
)
Insert #Table1
values ('201','3')
Insert #Table1
values ('202','2')
Insert #Table1
values ('203','3')
Insert #Table1
values ('204','2')
select * from #table1
--Alter table to have GUID ID
Alter TABLE [#table1] ADD [RoomType] nvarchar (4)
GO
-- Update values for colum with conditions for number of beds
Update #Table1
Set Roomtype = RoomID+'A'
WHERE Numofbeds ='3'
Update #Table1
Set Roomtype = RoomID+'B'
WHERE Numofbeds ='2'
Select * from #Table1
Drop table #table1
Hope this helps guide you in the right direction.
"TS" wrote:

> I have some Lotus Notes files that I saved as Excel spreadsheets. I migrat
ed
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination o
f
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to accompli
sh
> this?
> Thanks a million
> --
> TS|||Thanks a lot. Your code did exactly what I was looking for. Now the only
thing I need in order to finish the conversion is to include the description
next to the room id as follows:-
This is how the table looked like before applying your code:
Room Capacity Description
201 2 Small Single
202 1 Large Double
This is how the table looks like now after applying your code
RoomID
201A
201B
202A
What I need is to add another column to what I have now so the table will
look like this
RoomID Description
201A Small Single
201B Small Single
202A Large Double
What is the code for that.
Thank you for all your help.
TS
"Alejandro Mesa" wrote:
> Use an auxiliary numbers table.
> Example:
> use northwind
> go
> create table t1 (
> room_id int not null unique,
> number_of_beds int not null check (number_of_beds between 1 and 5)
> )
> go
> insert into t1 values(201, 3)
> insert into t1 values(202, 2)
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a
> cross join
> sysobjects as b
> go
> declare @.s varchar(255)
> set @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
> select
> ltrim(room_id) + substring(@.s, n.number, 1)
> from
> t1
> inner join
> number as n
> on n.number <= t1.number_of_beds
> order by
> room_id,
> n.number
> go
> drop table t1
> go
> drop table number
> go
>
> AMB
>
> "TS" wrote:
>

Global Variables

Hello,
Im having a little problem(i hope) with global variables.
Im working with the DTS of the SQL SERVER 2000! Does someone knows how can i load global variables using an SQL Task!?
Thank you all!!
Kind Regards,
LULUin package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des|||Originally posted by DesmondX
in package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des

Thanks.... DESMONDX

Global Variable

Hi All,

I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an error

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

I've seen some examples online to get global varaibles in task script and all of them display the same code

Any idea

Franck

To access variable this way it has to be included in the list of ReadOnlyVariables or ReadWriteVariables on your script task properties SCRIPT properties section. Remember also that variables names are case sensitive.