I try to apply the principle of Intelligent Laziness to make life as straightforward as possible. It’s hard to find meaning and clarity in a world that guarantees neither, and the Absurd perspective means that I do not stop trying in any case. Please note that I’m not associated with GoDaddy in any way, and I’ll be pleased to hear of any other hosting options that you use; please leave a comment below.
One aspect is to try and get access to the data I need to deliver Business Intelligence projects, as quickly and effectively as possible. As a freelancer, however, there is an additional issue around physical location: mine and the clients. I don’t always work at the client site, and can work from home. In this case, I need access to data in a way that does not necessarily mean that I have access to the client’s network or VPN.
One solution I’ve found is to get client data via a GoDaddy hosted SQL Server databases, and I use SSIS to consume the data directly from there. There are a lot of posts already on connecting to GoDaddy hosted SQL Server databases using .Net and so on, but it’s very easy to connect directly from within SSIS. This post is aimed at showing you how easy it is to do that.
In order to show what we’re aiming towards, here is an example of a very simple dataflow, which retrieves the data from the GoDaddy source, and places it into a text file. If you click on the image, it will take you to a larger version held at my Flickr account.
In order to access the GoDaddy source, then it will be necessary to set up the connection to GoDaddy. To do this, you will need the following items:
Server details: This will look something like stirrj.db.1234567.hostedresource.com
Username: this will be SQL authentication, not Windows authentication. So, type your SQL authentication username here. In this example, the username is ‘stirrj’
Password: This will be your SQL authentication password.
Database list: Once you’ve put this information in, you should be able to see a list of databases in the drop down list. Here, the example database is called ‘denali’.
To assist you, here is an example of how it looks:
Once you’ve set up the connection manager, you can take a look at retrieving the data as normal. Here is an example query, which retrieves data against the database hosted at GoDaddy.
You can see your OLE DB Connection Manager name in the drop-down list. Once you’ve set this up, you can test out that it works, and hopefully you will get some green boxes, as below:
I personally haven’t experienced any issues with the speed of retrieving data. However, if you want to load this data into a Tableau workbook, you may want to consider a number of ways of optimising the speed of retrieval:
– you could try to connect directly. I haven’t tried this but I imagine you just use the sql server reference details as above
– Using SSIS, you could extract the data from GoDaddy to a local SQL Server environment for development work. I’m integrating data from various sources, so this is working for me. I then use a Tableau data extract (TDE) to hold the data in the Tableau workbook, and simply refresh the TDE extract. This works very nicely, particularly since I can share the Tableau workbook with other Tableau as required.
One important note to make is that the Tableau Desktop edition will not connect to SQL Server, remote or otherwise. If you are a Desktop user, you will need to extract the data to a CSV file, and then import the data from the CSV file.
I hope that helps someone; any questions, just leave a comment and I will come back to you as soon as I can.
4 thoughts on “Intelligent Laziness: Connecting to a GoDaddy SQL Server using SSIS and Tableau”
Love your blog! I did notice in this post that you say Tableau Software won't connect to SQL. Indeed it will, as you mention earlier in the post– did you mean Tableau Public? Because that is the restricted free version that in fact does not connect to SQL.
I think Jen is making the distinction between Tableau Desktop and Professional. The difference isn't so obvious to the casual user.
For those who aren't aware:
Tableau Public: free, fully functional and can only save files to the web
Tableau Desktop: not free, connects to text files, XLS, Access, Azure and OData
Tableau Desktop Professional: connects to pretty much anything!
I notice that the new Tableau website doesn't make that distinction as obvious as it used to.
I tried this sql query but its not working. i don't know how to tweak it. could you please tell me whats wrong with that. I am new to IT. I found your blog. I don't know where to ask these kinda questions. if i am not suppose to be here then I am sorry..
DELETE FROM (select name from sys.objects where type = 'U')
I'm glad you found my blog and I am happy to help you!
Can you please post your question here: http://goo.gl/OnOv0
I am thinking that other people will have the same question as you, and posting on the Microsoft forum will help other people with similar questions. Once you've done that, can you please email me at jenstirrup [at] gmail.com and I'll respond on the forum? This will help you and others who need some pointers.
I look forward to hearing from you and look forward to helping you on your IT journey!