Image for post
Image for post

If you are using Azure Data Factory (ADF) and need to access data outside of Azure you need to install an Integration Runtime (IR) on a Virtual Machine (VM) that does have access to the data. At ASOS we have some sources that are still on premises or within VNETs in which case you need an IR. We are always evolving our implementations and recently had to split one of our solutions into two, which meant we had to reconfigure the IRs in multiple environments to connect to different ADFs in other Azure subscriptions.

The current recommendation of re-configuring an instance is that you cannot enter another key and you will need to reinstall it. However, there is a much easier way to do this using a single line of PowerShell. …


Azure Portal Corrupts Downloaded Files

Image for post
Image for post
Credit: http://www.keepcalmandposters.com/poster/5273092_keep_calm_it_works_on_my_machine

Have you ever had a situation where you’re working with a tester who has reported a bug, and you can’t for the life of you recreate it? It does after all work on your own machine…

We had an example of when downloading a parquet file using the Azure Portal Data Explorer, it corrupted the file and made it unreadable by Spark and parq, however downloading the same file with the Azure Storage Explorer solved the problem.

We were two days from deploying to production and our tester raised a bug, reporting he could not read the file he downloaded using the Azure Portal Data Explorer. Executing the below, we got a very strange and unhelpful error.


Image for post
Image for post

In the Advanced Analytics team here at ASOS, we have a few infrequently executed (monthly or weekly) Python processes. They were built by technical users, not engineers, and therefore used to run on a physical machine somewhere in a cupboard. For obvious reasons they had to be moved to a more stable and manageable infrastructure.

We had a requirement to run these Python scripts as part of an ADF (Azure Data Factory) pipeline and react on completion of the script. Currently there is no support to run Python natively inside of ADF. …


Description

When querying a SSAS instance using a linked server on a remote server while the same user also have a RDP session (Active/Inactive) to the same remote server using SSMS or any other method for ex SQL Unit Tests this error is produced:

OLE DB provider "MSOLAP" for linked server "TABULAR" returned message "The following system error occurred: ".Msg 7373, Level 16, State 2, Line 1
Cannot set the initialization properties for OLE DB provider "MSOLAP" for linked server "TABULAR".

Microsoft Connect Item

Please vote on this connect item if you experience the same https://connect.microsoft.com/SQLServer/feedback/details/1470258

Steps to Reproduce

The setup consists of 3 different machines:
LOCALHOST (LH)
REMOTE SQL SERVER (RSQL)
REMOTE SSAS SERVER…


So today I got the error message below

DFT Extract interface_RiskScorecard:Error: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)
DFT Extract interface_RiskScorecard:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.
The ProcessInput method on component "Parse Scorecard Name into properties" (66)
failed with error code 0x80004003 while processing input "Input 0" (76).
The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. …

So you can open a QVW file from the command line with one of the following options

/r  Reload, save, close.
/rp Partial-Reload, save, close.
/l Reload, leave open.
/p Partial-Reload, leave open.
/v Pass variable.

None of these options suited me, what I wanted was the following; Open QVW, immediately save it without doing anything i.e. not reloading data, not open script, nothing… then close the file. I was pointed towards the Automation Documentation over here http://community.qlikview.com/docs/DOC-1793 and I was provided with a macro that uses the COM Object in one QVW that opens, saves and closes another QVW. …


So I have been struggling for hours trying to figure out why my SQLPackage.exe task thinks it needs drop my Certificates even though I just deployed them and haven’t made any changes. Look at the example below, this is not the SSDT project or output it is only used for illustrating the problem and solution:

create certificate [YourCertificateName]
authorization [dbo]
with
subject = N'Some description about your encryption certificate'
, start_date = N'01/01/2012 00:00:00'
, expiry_date = N'12/31/2050 00:00:00'
active for begin_dialog = on;

If you run this from SSDT or SQLPackage where you have the above Certificate scripted as part of a Database Project and deploy this more than once you will notice that it is dropping and recreating the Certificate every time. This happens without you making any changes and the object already existing. My colleague Simon Sabin point out the following “The reason for this is that the dates in the above script aren’t ISO Standard.” This means that if you run a Schema Comparison you will notice the Certificate on the server looks like…


I recently resigned and was tasked to document all the important stored procedure and triggers. Yes I know this should have been done at the time but we have never had time, or should I rather say it’s never been priority.

I was deliberating over the different methods possible to document the T-SQL code. Red-Gate has SQL Doc but that only updates extended properties. I thought I could use Word and document what every Stored Proc or Trigger does but that document will be dead in the water. When making changes some of this come to mind… “Oh I need to update the documentation”… “Where is the documentation saved again?”… “Aargh!!! The other guy did not document his changes”. The last two can be mitigated by using source control or versioned docs like Google Docs or Sharepoint etc. …


My wife works as a Salesforce Administrator. When Chatter launched she very quickly became annoyed with the new Chatter profile page that appeared when you clicked on a user’s name.

She wanted to go to the User Details page instead as this is where all the Admin gets done. She realised that to go to the User Details page all you needed to do was append noredirect=1 to the end of the Url.

I decided to develop a Chrome Extension to do this automatically for all User Profile links (i.e. Id’s that Start with 005).

Please feel free to download. If you have any feedback, bugs or suggestions please leave them on the Chrome Web Store.


Error

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Ovott” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Very helpful, I know… Please excuse the sarcasm. I spent days trying to fix this error as I just installed some database drivers before building a new package. At first I thought it was the drivers, so I removed them. …

About

Eugene Niemand

Lead Data QA Engineer at ASOS.com - I have a passion for Test Driven Development, Agile Methodologies, Continuous Integration and Delivery using Microsoft Azure

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store