SSIS in sql server 2005

SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. extract, transform and load data. While ETL processing is common in data warehousing (DW) applications, SSIS is by no means limited to just DW; e.x. when you create a Maintenance Plan using SQL Server Management Studio (SSMS) an SSIS package is created. At a high level, SSIS provides the ability to:
retrieve data from just about any source
perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.
load data into just about any source
define a workflow
The first version of SSIS was released with SQL Server 2005. SSIS is a replacement for Data Transformation Services (DTS) which was available with SQL Server 7.0 and SQL Server 2000. SSIS builds on the capabilities introduced with DTS.
In this tutorial we will step through a number of topics that you need to understand in order to successfully build an SSIS package. Our high level outline is as follows:
Creating SSIS packages with SQL Server Management Studio (SSMS)
Business Intelligence Development Studio (BIDS)
Creating a simple SSIS package in BIDS
Deploying SSIS packages
Executing SSIS packages

Different Way of Alias in column

There are different ways to alias a column. Here are some possible ways that work in SQL Server.
01. select
02. 1 as number,
03. 1 number,
04. 1 "number",
05. 1 'number',
06. 1number,
07. 1"number",
08. 1'number',
09. 1 as "number",
10. 1 as 'number',
11. 1.number,
12. 1."number",
13. 1.'number',
14. 1 as [number],
15. 1 [number],
16. 1.[number],
17. 1[number]

Bulk Insert in sql server 2000

Bulk Insert-Copies a data file into a database table or view in a user-specified format.
Eg-
BULK INSERT test
FROM 'd:\testabc.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n'
)

Break vs Return

Suppose you have a set of codes and when a particular condition is true, you want to terminate or switch to another set of code, you can use Break commnad. I see sometimes people use return commnad for this. But there is a significant difference between these two. Consider this example
Usage of BREAK (select will be executed)
declare @j int
set @j=1
while 1=1
begin
print @i
if @j>10 break -It Terminate the Current Block
set @j=@j+1
end
select @j

declare @i int
set @i=1
while 1=1
begin
print @i
if @i>10 return It Terminate the Entire Block
set @i=@i+1
end
.select @i
Break Command-it just terminate the current block (created by if clause, while loop etc), but Return Command- will terminate the entire block

Retrieving all the table Name and view name of specific Database

use ABC
select * from information_schema.tables-Retrieve Tables

select * from information_schema.view --retrieve Views

OPENROWSET

OPENROWSET-Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

Eg-SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=F:\', testing#txt)

List of datatypes supported in SQL Server

List of datatypes supported in SQL Server-
exec sp_datatype_info in sql server 2000
exec sp_datatype_info_90 in sql server 2005
exec sp_datatype_info_100 in sql server2008