If you’re curious, you can rename the .ispac file and give it a .zip extension, and extract. Voila, we see all the files that make up the ispac – a manifest, your DTSX files, and a .param file. Also note that the manifest does not have the usual uber-long .ssisDeploymentManifest extension

I wanted to do a cool experiment on SSIS that I haven’t tried before. I am really interested in this whole mapping component on SSRS, and I know that most likely the biggest challenge of companies is how to geocode addresses that are already in their databases. So what I wanted to do was to try and retrieve lat/long information from public geocoding web services within SSIS.

Please note that the exercise below is purely for experimentation. Please read the restrictions of each of the geocoding services, and go with whichever is appropriate for your purpose.

Also this is a simplistic code to show you how to get the latitude/longitude information. On your own versions, test thoroughly and add the appropriate exception handlers.

Ok, let’s start.

Step 1 – Look for the appropriate geocoding service.

There are few publicly available ones. Some are limited in content, some are very restricted. Check out the summary here – http://en.wikipedia.org/wiki/Geocoding

For this experiment I am going to use Google Maps v3 API since it’s publicly available, and V3 doesn’t need the Maps API key anymore! Please make sure you read the restrictions of using the Google Maps v3 API before deciding to use it in your applications/systems.

Note that for v3, there are only 2 available output formats: json and xml. Previous version of the API supported other output formats like kml and csv. I am going to stick with XML. (I’ll leave JSON for the next experiment).

Here is an example URL and result: http://maps.googleapis.com/maps/api/geocode/xml?address=Pacific+West+Outlet,Gilroy,California,US&sensor=false

Try to study the structure, it will help later on with the parsing for latitude and longitude.

Step 2 – Prep your package

Create your SSIS package. Drop a data flow task, and add your source. In my case my source is an OLE DB source pointing to my SQL Server table that has address fields already.


Read the rest of this entry »

I will be doing a series of tutorials (some simple, some more involved) in SSIS in the next little while. In addition to step by step instructions, I will also be providing the video tutorial equivalents which will be posted at the Black Ninja Software website. Once the video tutorials are up, I will be updating the individual links.

Now for this simple problem. You have a MySQL database, and you want to be able to dump data from it to SQL Server using SSIS.

Here are the steps:

1. If you haven’t already, download and install MySQL Connector for ODBC.

2. Create a DSN for your MySQL

a. Go to Start > Administrative Tools > Data Sources (ODBC)
b. Create a new User DSN (in my case I called it MySQL55)





Read the rest of this entry »

Today has been quite an eventful day for me, feels like a mishmash of stuff happened today. Definitely worth a blog post.

Here are some of the things I learned and did today.

And man it amazes me how many smart smart helpful people are out there

Service Broker

I possibly might need to work on SQL Server Service Broker. I think this might be the right tool for a project, but I need to be certain so I need to give it a test drive.

Thanks to Aaron Bertrand (blog | twitter) for pointing me to these excellent excellent resources on SQL Server Broker.

http://mrdenny.com

http://http://itknowledgeexchange.techtarget.com/sql-server/tag/service-broker/

http://rusanu.com

Read the rest of this entry »