SSIS Binary Code is not found - You need to invetigate by youself

Recently when one of my collegues worked in a SSIS script task, she got a "binary code is not found" error. What she did is

• Open the VSTA IDE
• Add a web reference to the reporting service
• Build the code, and close the IDE
• While she tried to close the script window, an error window "binary code is not found" popped up She went back and checked her code, nothing was added yet except for the web reference
• She set the "delay verification" to true to remove the red cross, but she executed the script task, the task failed with identical error message.

When we do a google search, basically you can find hints like:

• The code is wrong, check your syntax
• Set "delay verification" to true if you are using SSIS2008 and later versions
• Set "PrecompileScriptIntoBinaryCode" to true if you are using SSIS2005

Obviously none of above is applicable in her case. So I grab the package and run on my PC, guess what? Nothing is wrong on my PC. So problem must occurs on her machine.

Now look at MSDN at https://msdn.microsoft.com/en-us/library/ms152787.aspx, it says "to develop Reporting Services applications based on the Web service...using Microsoft Visual Studio and the Microsoft .NET Framework SDK"

Now it looks much clear. I downloaded .Net Framework SDK and installed on here machine. Now she can build the script task with web service.

So considering the case I posted before DBCC Error, searching online is good, but you still need to do your own task. :D

Run Subscription from T-SQL

As a multi client facing company, we scheduled quite a lot reporting service subscriptions at the backend. Some of them are data-driven subscriptions, some of them are time-driven subscriptions, and some are running from SSIS, a custom control flow task to generate batch reports by calling ReportExecution2005 web service, for example, customer fulfilment form.

Occasionally we will receive the request to re-run the subscription. Instead of navigating to reporting service site and change the subscription setting manually, below script will shot start subscription immediately:

 use ReportServer  
 go  
 DECLARE @JobID varchar(50)  
  SELECT @JobID = S.ScheduleID   
  FROM ReportSchedule RS JOIN Schedule S ON RS.ScheduleID = S.ScheduleID  
    JOIN Subscriptions SUB ON RS.SubscriptionID = SUB.SubscriptionID  
    JOIN [Catalog] C ON RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID  
  WHERE C.Name LIKE '%agl%' --subscription report name comes here  
  EXEC MSDB..sp_start_job @job_name = @JobID  

SQL Server Spatial - Distance Test

This is just a quick post about using spatial data in business reporting.

Quite often distance to site is a very important element when client is running some types of appointment campaign or service campaign. Below is a short script can be used to do the distance test:

 declare @lat float = 145.41, @lng float = -38.02  
 declare @geo geography   
 select @geo = geography::STGeomFromText('point(' + Cast(@lat as varchar(20)) + ' ' + cast(@lng as varchar(20)) + ')', 4326)  
 select top 10 geom.MakeValid() as Geom  
      , geography::STGeomFromText(geom.MakeValid().AsTextZM(), 4326).STDistance(@geo) as Distance  
      , POA_NAME as Postcode  
 from POA  
 order by 2   

Here is some explanation:

Firstly, POA is a table from Australia Bureau of Statistics (ABS). You can find a full explanation at
http://www.abs.gov.au/ausstats/abs@.nsf/mf/1259.0.30.001?OpenDocument. To get shape file imported into the database, you can use Shape2Sql, which you can find at http://www.sharpgis.net/page/shape2sql

Secondly, we need to know latitude and longitude of a particular place, for instance, a business site or a clinic. I found this site is quite helpful, as it allows you to do batch geocode: http://www.findlatitudeandlongitude.com/batch-geocode/

Thirdly, you may find Paf Postcode file from Australia Post is quite helpful. But unfortunately starts from 2014, this resource is not free to individual user anymore, though they provides a pdf formated postcode file.

So after you get your resources sorted out, running above query will give you some results such as top 10 postcodes to a business site, and with some extra works in SSRS and T-Sql, we can make the result more clear to the end user.