Blog

Working With SQL Server Management Studio in SSIS

SQL Server Management Studio (SSMS)

a) Open SSMS (SQL server management studio)
b) Select server type in the following

  1. Database engineàTo connect and work with normal database.
  2. Integration service à To and work with normal database.
  3. Analysis service àTo connect and work with analysis service database.
  4. Reporting servicesà To connect and work reporting services databases.
  5. SQL server compact EditionàTo connect and work with mobile applications.

c) Server name –> Either IP Address (or) actual server name.
d) Select authentication –>
i) Window  authentication –> Consider window credentials to logon
ii) SQL server Authentication –> Server database credentials.
Note: Select the option:  ii)for the real time work process.
e) Click connect…. Next…
Creating databases –> Right click on database
New database window
Database Name: DMNEW
–> Click ok
Creating tables –> DBNe3wq –> Tables –> Right click–> Select ‘new tables’
Column         Name               Data Type
PARTY          ID                        Int
PARTY         NAME             VARCHAR (50)
–> Next save the table with a specified Name i.e, PARTY
–> Click OK.

Adding data to the tables of the database

–> Right  click on TABLE –> Select the option “edit top 200 rows”.
PARTY ID         PARTY NAME
1                                x
2                            VINAY

Firing Queries on database

  • Go to database(i.e DBNEW)
  • Right click à select new query
  • SELECT* FROM PAETY;
  • Click Execute (or) press “f5”
  • Insert into party values(3,’Mathu’, ‘Hyd’, 5000)
  • Update party set party ID=20 where party ID=222.

Connecting to the other database
Two ways:

i) By Specifying –> ..
Ex: SELECT * FROM   VINAY-DB.TEST
SELECT * FROM     SIVA_DB.TEST
ii) Taking other database as current database
Syntax: USE
USE  SIVA_DBA
SELECT * FROM
SELECT  * From PARTY_TEST;
Note:  Second way is recommendable if we are firing many queries on the same database (Eg: SIVA_DB)

                                                Enroll for Live Instructor Led MSBI Training 


RELATED COURSES

Get Updates on Tech posts, Interview & Certification questions and training schedules