Responsive Ads Here

Saturday, November 18, 2017

How to create a Linked Server to an Azure SQL database

5:16 AM Azure , Azure SQL database 1 Comments
Hi friends, today I will explain how to create and configure a linked server to retrieve data from an Azure SQL database.

Few points are describe in this article for configure link server with SSMS

  1. How to configure link server step by step in SSMS
  2. Solve some common Problem/Issue  when create link server
  3. Using the Query/Script base to create link server
How to configure link server step by step in SSMS

        To create link server via SSMS, go to Object Explorer under the Server Objects folder, right click on the Linked Servers folder and from the context menu choose the New Linked Server Command:

Fig 1.1

The New Lined Server window will be opened:

Fig 1.1

Put your Linked Server Name, then
Select data source: Microsoft OLE DB Provider for SQL Server
Then click on security tab on left side

Under the Security tab, select the Be made using this security context radio button and enter user credentials this exist on Azure server
Fig 1.2

Under the Security tab, use the same setting that we used in the previous example and press the OK button.
This will create a linked server to an Azure SQL Database(Azure Database). And when the plus(+) sign next to the table folder is pressed, the Tables folder will expand and show all tables for the selected Azure database:

Fig 1.3
To retrieve data from the SQL Azure table (e.g [dbo].[Actions]), type the following code:
SELECT Top 10 * FROM [LinkedServerName].[AzureDataBaseName].[dbo].[TableName]

Solve some common Problem/Issue  when create link server

When we create linked server we facing some common issue like Linked server is showing created but we can’t able to access the linked database. It’s given error which is mention below image:

Fig 2.1


Above mention error, Fail to retrive data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

          ADDITIONAL INFORMATION:
                         An exception occurred while executing a Transact-SQL statement or batch.  (Microsoft SQL Server, Error: 40515)

Using the Query/Script base to create link server

How to create linked server for SQL Database on Azure from local SQL Server
Step by step Azure SQL Database Linked Server
Step 1: Go to SQL Server Management Studio
Step 2: Run the below scripts. This script using a stored procedure "SP_addlinkedserver".
EXEC sp_addlinkedserver
@server='Azure', -–Provide Linked Server Name
@srvproduct='',    
@provider='sqlncli',
@datasrc='AzureDataServerName', -–provide Azure SQl Server name
@location='',
@provstr='',
@catalog='AzureDatabaseName' -–Provide azure database name




Step 3: Run the below script to provide the SQL Server login for the above created linked server.


Exec sp_addlinkedsrvlogin 'Azure', 'FALSE', NULL, '[azure sql server login]', '[sql server password]';




Step 4: Verify the created linked server, under the Databases -> Server Objects -> Linked Servers -> Your Linked Server Name





Step 5: Access the azure SQL database table. You need to use 4 part query. [linked server name].[database name].[schema name].[table name]




SELECT Top 10 * 
FROM [linked server name].[database name].[schema name].[table name]























By



at







1 comment:
  



















Labels:
,
,
,
,
,
,

















          

        

          

        

Wednesday, November 1, 2017


          

        

























SSIS FOR BEGINNERS











2:12 AM


basic about ssis
,
Janak Goti

0
 Comments



























SSIS FOR BEGINNERS















Hi friend's, today we are going to understand about SSIS. i collect many information about SSIS blob and try to understand how to use SSIS. i hope it is useful for you.












Now first try to understand about SSIS.








    

  1. What IS SSIS?
    2. 

  2. Why SSIS?
    3. 

  3. SSIS Variable.
    4. 

  4. SSIS Connection Manager.
    5. 

  5. SSIS ToolBox.
    6. 

  6. Data Flow Task.
    7. 





1. What Is SSIS?




    

  • It is a powerfull ETL tool, which is used for building enrterprise level data transformation, and dara integration solutions.
    • 





2. Why SSIS?






    

  • Extract, Transform, and Load (ETL) data from SQL Server to a file  anf also from file to SQL.
    • 

  • Sending and email.
    • 

  • Download the File from FTP.
    • 

  • Rename,Delete,Move File from Defined Path.
    • 

  • It allows you to join tables from different database (SQL, Oracle, ect..) and from potentially different servers.
    • 




Please refer Install SQL Server Data Tools article to understand the steps involved in installing the SQL Server Data Tools or Business Intelligence Development Studio(BIDS).









We will see following contents in this article.




    

  • Variables
    • 

  • Connection Manager 
    • 

  • SSIS Toolbox
    • 

      

    • Contaniner
      • 

    • Tasks
      • 

    

  • Data Flow Task
    • 




Variable:







Variables store values that a SSIS package and its containers, task, and event handlers can use at runtime.







System variables: Defined by Integration Services








    

  • SSIS provides a set of system variables that store information about the running package and its objects. These variables can be used in expressions and property expressions to customize packages, containers, tasks, and event handlers.
    • 






You can go Through this link for more details about System variable (https://docs.microsoft.com/en-us/sql/integration-services/system-variables)























User-Defined variables: Defined by Package Developers













How to create user define variable that shown in below image.












Fig.1









How to set expression for variable that shown in below image.











Fig.2







Connection Manager:







SSIS provides different types of connection managers that enable packages to connect to a variety of data source and servers:






    

  • There are built-in connection managers that setup installs when toy install Integration services.
    • 

  • There are connection managers that are available for download from the Microsoft website.
    • 

  • You can create your own custom connection manager if the existing connection managers do not meet your needs.
    • 






Let's see, step by step how we can add Connection Manager.








    

  1. Solution Explorer > Connection Managers > New Connection Manager.
    2. 




You can see the list of connection managers for different type of connections.













Fig.3














    

  •  Add connection manager.
    • 








Fig.4










    

  • After adding your connection. you can see the all connection here.
    • 








Fig.5











SSIS Toolbox:







Steps: Menu bar > SSIS > Select SSIS Toolbox.







Now,you can see SSIS toolbox on the left side.







SSIS toolbox have list of tasks and containers that you can perform.







List of Containers:




    

  • For each Loop Container:  Runs a control flow repeatedly by using an enumerator.
    • 

  • For Loop Container:  Runs a control flow repeatedly by testing a condition.
    • 

  • Sequence Container: Groups tasks and containers into control flows that are subsets of the package control flow.
    • 




List of Tasks:




    

  • Data Flow Task
    • 

      

    • The tasks runs data flows to extract data, apply column level transformations, and load data.
      • 

    

  • Data Preparation Task
    • 

      

    • These tasks do the following processes: copy file and directories, download files and data, run web methods, apply oprations to XML documents, and profile data for cleansing. 
      • 

    

  • Work Flow Tasks
    • 

      

    • The tasks that communications with other processes to run package, run program or batch file, send and receive messages between packages,send e-mail messages, read Windows Management Instrumentation(WMI) data, and watch for WMI events.
      • 

    

  • SQL Sever Tasks
    • 

      

    • The tasks that access, copy, insert, delete, and modify SQL Server objects and data.
      • 

    

  • Scripting Tasks
    • 

      

    • The tasks that extend package functionality by using scripts.
      • 

    

  • Analysis Services Tasks
    • 

      

    • The tasks crreate, modify, delete and process Analysis Services objects.
      • 

    

  • Maintenance Tasks
    • 

      

    • The tasks that perform administrative functions such as backing up and shrinking SQL Server databases, rebuilding and reorganizing indexes, and running SQL Server Agent jobs.
      • 

    




you can add task/container by drag the task/Container from SSIS toolbox to design area.







Data Flow Task:







Drag the Data Flow task from SSIS Toolbox to design area and double click on it.



you are now in Data flow tab.



now you can see that SSIS Toolbox has different components.







Type:








    

  • Source: From where you want your data.
    • 

  • Destination: It is where you want to move your data.
    • 

  • Transformation: It is operation that perform ETL(Extract, Transform, Load)
    • 




So, that's all guys i hope These will you to understand basic SSIS.


































By



at







No comments:
  



















Labels:
,
,

















        

      




























{
  "@context": "http://schema.org",
  "@type": "Organization",
  "url": "http://c-sharpnets.blogspot.com/",
  "logo": "http://c-sharpnets.blogspot.com/images/logo.png"
}