SSIS Data Flow task to import data in a CSV file kept in an azure blob container, into a managed Instance

Hello Readers !  This is the second part of my blogs on working with azure blob containers and managed instances using SSIS packages. In this blog content, we will be discussing about the steps to be followed to make SSIS package import data in a CSV file ,kept in an azure blob container into a managed instance table.

For pre-requisites, please read my previous post here

I have created a CSV file named emp.csv with content as below and uploaded it to the azure storage container named ‘ssistestsource' 

       

I have dragged a data flow task from SSIS toolbox and thereafter dragged the items named Azure Blob Source and Destination assistant to the designer window (Data flow designer)

Clicking on the destination assistant to add the managed instance connection manager (SQL Server Connection Manager)

     
Choosing SQL server and Supplied the private end point address of the managed instance with credentials and catalog as SSISTest (A database I have in the instance to which I will be importing the CSV data)      
Clicked on Okay to add the OLEDB connection manager entry after testing the connection.

Clicked on Azure Blob source , opened editor and gave the details of the blob container and file name as given in the below screenshot. ( I have the Storage account connection already added to the package for the previous test. I am reusing it here).
   

  Dragged the arrow originating from the Azure blob source to the oledb destination in the designer.

Clicked on the OLEDB destination again to set data access mode and to supply a table creation script in place of "Name of the table or view" (Click on New button on the right to get the popup).

   
Executing the package and it succeeded
   

 We could also see that the data got loaded into the emp table in SSISTest database of the managed instance.
 

  I also could test exporting the table data in managed instance into a CSV file kept in a blob container using

"Azure Blob target" and Source assistant. I hope you could do it yourself without any error following similar steps in this blog. Thanks for visiting my blog. I will come back again with another topic related to Azure and SQL Server. 

[Solved]: Replication monitor could not retrieve Publisher information from the distributor (In azure managed instances)

Hi Readers,

I am writing this blog to document a workaround for a connectivity issue I was facing in my Replication monitor (in SSMS).I am hoping those who are facing this issue, would be benefited from this blog.

 I was configuring MI-MI  replication with T-SQL scripts and at the end when I was trying to launch Replication monitor by right clicking on the publication , I have encountered the below error, which was basically telling me that the replication monitor is unable to connect to distributor or cannot retrieve the publisher information from the distributor.

Error message:

Clicking on the show details button above got me the below text

Message details:

===================================

Replication Monitor could not retrieve Publisher information from Distributor 'tcp:xxxxxx.xxxxxxxx.database.windows.net,1433'. (Replication Monitor)

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18131.0&EvtSrc=Microsoft.SqlServer.Management.UI.ReplicationMonitorErrorSR&EvtID=CantGetPublisherInformation&LinkId=20476

===================================

Could not refresh properties of this object from server. Check if this object still exists on the server.  (Microsoft.SqlServer.Rmo)

------------------------------

Program Location:

   at Microsoft.SqlServer.Replication.ReplicationObject.Refresh()

   at Microsoft.SqlServer.Management.UI.ReplMonitorMenu.Invoke()

<End of the error message>

Looking at the error message, I thought this must have something to do with the publisher details maintained in the distributor. I went and right-clicked on the Replicationàselected Distributor propertiesàWent further for the ‘publishers’ page in that window and could see everything was perfect there. And moreover I could successfully test the data inserted at the publication DB being sent successfully to the subscriber too. This cannot be anything to do with the permissions, as I have used SQL accounts with sysadmin access everywhere.

After browsing through some online forums and having found no solutions, I opened a case with Microsoft as I felt that the replication monitor is important for us if we are ever going to use replication in the Prod setup and we didn’t have the luxury of time to keep exploring solutions online.

The gentle man from azure team tried a few options like downloading the latest SSMS and trying to remove the publisher from the replication monitor and adding it back. Unlucky for both of us, nothing really worked.

On the very next day, I stumbled upon a workaround that saved time for both of us which was removing publisher from the replication monitor and adding it back using the third option.

The option that I used was the one in the screenshot

I have supplied the distributor server name and could successfully use the replication monitor to insert tracer tokens, check latency data, look at the agent profiles etc with no issue whatsoever.

I am hoping you would find this blog content useful or Microsoft fixing this issue in their next SSMS release. Thanks for visiting my blog.

Azure Networking Basics for Beginers. Part -1

Hello Everyone,

The below blog is only for those who are starting with Azure and wish to understand about some basic networking and resource concepts in Azure.

If you are a beginner and want to explore Azure and do some Tests with the azure resources/services yourself, please create an account in https://azure.microsoft.com/en-in/features/azure-portal/ . You could use any of your existing microsoft account for it. Azure gives 200$ free credits for 12 months along with a few free services' access. There are a set of 25 services that are available for free even after the 1 year trail period. I have not explored these myself as i was fortunate enough to explore Azure as part of my regular job.

Explore what comes free here . Without any further delay I will get started with some concepts for you to begin your azure journey.

Azure Subscription : This is your starting point  . Your azure account can have access to one of more subscriptions. Billing is done for all resources in the subscription and the one who is owning the subscription has to pay the bill as we use azure services. .For ease of use , you could have different subscriptions of your different environments like Prod, Dev, Test .

Azure resources: Simply put, these are instances of services, solutions and applications that we avail from azure.It could be a VM, database, Storage etc.You could find the resources in azure market place or get a direct link to it by searching for it in the search bar in azure portal, and get it added  to your subscription to start using them.

Resource groups (RGs) : This is a logical grouping of resources for your project, that came with the latest deployment model in azure called ARM(Azure Resource Manager). Imagine i have a POC project and need to use a bunch of resources for it. I can add all my resources under a single resource group and get the resource group deleted which will save my time in dealing with deletion of each of the resources that I have added.

Virtual Networks (VNet) : This is your isolated and  dedicated virtual network in azure to deploy your resources and establish network connectivity within (or outward when needed). Similar jargon in AWS cloud is called VPC. You can establish Vnet to VNet connections via VNet peering techniques and can connect to the  private on-premise networks outside the cloud using either Point-To-Site (Much like your office VPN and it doesnt need a VPN device), Site-Site (Biway connection VPN through which you can make your resources talk to the on-premise resources, need a devide ) and Express routes VPN (Similar in function to Site-Site VPN, but faster and has got more features as it works with  several connectivity providers- need VPN device too). 
I will write a short blog later on how to setup a Point-To-Site VPN for your Azure VNet.
A Virtual network is scoped to a single Azure region, which means you cannot have a VNet spanning multiple azure regions.

Subnets: This is a subset of your VNet to host similar resources and manage security better. For example, you need to  have a dedicated subnet created for managed instances in your VNet as per design .The IP address range you set for subnets need to be the subset of that you define for your VNet.

NSG(Network Security Groups):This is your firewall feature which needs to be there within the VNet to provide better security and isolation either at the Subnet level or any VM's NIC. You can create inbound and outbound rules in the NSG attached to your subnet to manage the traffic by allowing or denying it based on IP address, port numbers etc.

Hope this helps you gain some basic understanding about Azure. Thanks for visiting.!