Tuesday, September 15, 2015

How to create and use temp tables in SSIS

Creating temp table by using SQL is very easy process. Some times we require to create temp tables in SSIS packages for this we need to configure some properties while creating the ssis package.

Creating a test SSIS  package for  temp table creation:-

 1) Drag execute sql task from SSIS Toolbox and rename it to temp table

 2)Double click on Execute sql task and select your connection string and in your sql statement paste your   SQL statement i pasted below sample

IF OBJECT_ID('tempdb..##temptest') IS NOT NULL
    DROP TABLE ##temptest
    CREATE TABLE ##temptest
    (
        id VARCHAR(255),
        name VARCHAR(255),
        city  VARCHAR (2)
    )
    INSERT INTO ##temptest VALUES
       ('hyderabad', 'cycle', 'ap'),
       ('delhi', 'bike', 'DL')


click ok.

3) Drag a data flow task from SSIS Tool box





4) Double click on the data flow task  after entering into it drag one OLEDB source. now we will pull data from temp table and insert into database by using oledb destination. To avoid error create global temp table in SSMS 



5) Again go to SSIS package and in data flow task go to oledb  select data access mode as SQL command
select the temp table. Click on oledb destination select data access mode as table or view fast load and create table and click ok

6) Once finished above task start debugging. Now you will see below error in execution results.


7)Now we will fix the issue go oledb connection and right click on it. Put the property  retain same connection as true check below image and the delay validation properties as true

8)Now execute the package  successfully executed.


9)Now check in SSMS data loaded into destination.

10) Why global temp table is used here ?

Ans:- We can create it in SSMS and use it in SSIS package because of  global access(##testtemp)(create in any session access in any session). if we use local temp(#testtemp) we face below issue and session specific 



 











Monday, September 14, 2015

Running SSIS Package From Command Line

 Methods used to execute the ssis package:-

  1. SQL Server data tools(SSDT)/Business Intelligence development studio(BIDS)
  2. Command Line utility 
  3. SQL Server Job Agent

1) SQL Server data tools(SSDT)/Business Intelligence development studio(BIDS)

Step 1:- Go to bids or ssdt whatever you have i have ssdt here, go to solution explorer right click on project name under solution file click on build.
Step 2:- After build successfull click on execute package. if you want make it as startup object click on startup object.  

2) Command Line Utility 

Step 1:- Open your command prompt as administrator (Run as administrator)


Step 2:-  type below text to call package

dtexec /f  <Filepath>
/set \package.variables[Variable]; value

Example:-   dtexec /f E:\test_package\test_package\Package.dtsx
/set \package.variables[id];1




Success.


3)Execution using SQL Server job agent

Step 1:- right click on SQL server job agent in your  SSMS and go to job. in general tab give job name, and go to steps and click on new


Give step name, select type as SQL server integration service package and Run as sql server


select your file system in package source and browse and select package path, and click ok


click on start job. execution will start you will see below window










Thursday, September 10, 2015

How to rename column name in a table

Column name can be changed in two ways 

A) SQL server management studio-->right click on the database-->select table and expand-->select column name which you want to rename--> change the name



B)By Using Query:-

SQL server allows you to rename column name by using sp_rename procedure which is already written in the procedure library.
Syntax:- sp_rename 'tablename.old column name','new column name','column'
         Ex:-sp_rename 'department.name','Fullname','column'





name column changed to Full name

Renaming Table name 

In same way we can re-name our table also  and below query used to rename table name

Here department is my old table 




Syntax:- sp_RENAME 'OldTableName' , 'Newtablename' 
       Ex:-sp_rename 'department','mydepartment'




Now table name is changed

Renaming Database:-
 for explanation purpose a test database has been created here

create database testdata

Syntax:- sp_renamedb 'old database name','new database name'
           Ex:-sp_renamedb 'testdata','nedb'







Tuesday, September 8, 2015

How to delete duplicate row from table



SQL server tables generally doesn't contains duplicates if design done with proper addition of constraints (Primary, Unique etc...). For data integrity we should not allow duplicates to be insert into table. Sometimes duplicates inserted into table because of duplicate primary keys exist stance. If duplicate pks are present in relational database tables primary key uniqueness is not worked (or) enforced this causes duplicates insertion


Here i will show how to delete duplicates from table:-


CREATE TABLE duplicates_test(id int,name varchar(50),salary int)

insert into duplicates_test values(1,'ram',5000)
insert into duplicates_test values(2,'priya',5000)
insert into duplicates_test values(3,'amitha',4000)
insert into duplicates_test values(4,'patokar',3000)
insert into duplicates_test values(5,'jaya',3000)
insert into duplicates_test values(5,'jaya',3000)

select * from duplicates_test


now  jaya related rows existed two times we want remove it. here am using with CTE(temp table)

with x as(select *,rn=ROW_NUMBER()over(partition by id order by name) from duplicates_test)select * from x where rn>1

result

 we got duplicates rows information. now use delete instead of select see below query

with x as(select *,rn=ROW_NUMBER()over(partition by id order by name) from duplicates_test)
delete  from x where rn>1

duplicates are deleted after running above cte query













Sunday, September 6, 2015

How to Fix Error Can not connect WMI Provider you do not have permission to Server

Problem Cause : WMI Provider Removed when you uninstalled instance of  32bit/64bit versions of sql using same wmi provider, location of your wmi  provider "C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof" file name,

To quick fix this error:-
go to command prompt type as below :-

C:windows\system32\mofcomp  "C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof" and press enter.

Then you will see below window appeared. Now you can access sql server configuration manager.


for deeper info: refer  MSDN



Wednesday, September 2, 2015

How to schedule a SQL server job In SQL Server 2012

     SQL Server agent helps you to automate (or) schedule a job T-SQL or SSIS Packages Jobs contains one or more steps to execute step by step manner. You can manually run a job or  you can schedule the job as per you convenience. In express edition it is not possible to create a sql server job.

This post helps you to create a job in SQL server agent step by step:-

step:-1) open SSMS  connect to database engine with sql authentication.


step:-2) go to sql server agent right click on it and click on start
             select new job and name it here i named as test-job


step:-3)  go to steps click on new and select type as Transact-Sql Script if  you want execute stored procedure here i considered stored procedure mention the step name,select database where the stored procedure is present. write command "exec load_table" in your case write your stored procedure name 


step:-4) go to schedules click new and give schedule name  and select schedule type based on your requirement 

step:-5)Mentioned the frequency as per your requirement here i choosed daily  frequency for every 15 minutes,duration how many days you want to run the job mention the dates ,i selected no end date, click ok, again click ok 


step:-6)it will execute for every 15 minutes. 


step:-7)if you want to run the job manually you can run by right clicking on the job and click on start job  




How to Fix named Pipes Provider Error 40 cannot open connection to Sql server instance



Symptoms of error

A network related or instance specific error occurred while establishing a connection to sql server the server was not found or not accessible.verify   that the instance name is correct (Provider : Named pipes error Error no 40; etc .

To fix this error goto start menu--> go to  Microsoft Sql Server --> go to configurations folder and click on sql server configuration manager. check below image.



Expand Sql Native client 11.0 Configuration manager. In client protocols you will see TCP/IP, named Pipes,Via disabled, enable those



Expand Server Network Configuration In Protocols for Sql Server here  Enable Shared,Named,TCP/IP

Expand Sql Native client 11.0 Configuration manager. In client protocols you will see TCP/IP, named Pipes,Via disabled, enable those  and restart the Sql related services. Now the error fixed.





how to enable sa user name in sql server

Most of the users facing issue with "sa" login. this issue caused because of not selecting mixed  mode authentication while installing the sql server (or) some limitations created dba's as per security policy in  company's and mixed mode is less secure.
below are the symptoms you face while login

Login failed for sa
cannot connect to sa login etc,.,

here am going to show changing authentication mode Sql server.
Follow below steps to enable to sa user in sql server
Login Filed issue:-
if you have access to any other user login(database instance) with that. right click on server instance and go to properties


select security and highlight the bubble Sql Server and windows authentication. this option can enable the sql server sa login

click ok and restart the sql server services. go to start menu type  services.msc and restart the  services


Your sa login account enabled automatically after above steps.
if you want enable manually  type below query  in ssms query editor window

ALTER LOGIN [sa] WITH PASSWORD='AnyStronG@#Passw)e', CHECK_POLICY=OFF
GO
ALTER LOGIN [sa] ENABLE
GO

now you can login with sa user name and  defined password