Write Back Data Prototype

This is the first post on Reporting Services on the blog, and as usual this is not a routine concept but a customized requirement from client.


The objective of the SSRS project here is to first generate a SSRS 2008 report, something like below:

1. Report Design:

The below is a report displaying the client information like ID, Name and City. Along with the actual data we have a dummy column which is linked to a URL, which is a GUI to change the data that inherits on the database.

 2. Report on Report Server:

The below is a shot of report which is deployed on to Report Server.

3. Customized URL:

The below is a customized URL to make changes to the data, which will be inherited on the database.
This has to be designed in ASP.Net project which has to be linked with SSRS.

4. Report and URL integration:

The below screen shot demonstrates a new window pop-up rises, when the Link for a row is clicked. The new window will pop-up with the existing data. The user can now make changes in the editable textboxes and click on UPDATE. Once it is clicked that will make changes to the data in the database.

5. Enhancements:

When user makes changes and clicks on UPDATE button, it should do two operations.

v  It should update the data in the database. (It is accomplished already in the present version of code)

v It should refresh the report page so that the changed data appears instantly. (Got to work on this tomorrow with the developer) 

This in one simple example of the integration of ASP.Net and SSRS. Hope this is useful!!

SQL Server 2012 Integration Services Enhancements

This is an article to discuss few of the many enhancements made to SSIS 2012.

The very first change we notice is the BI development tool itself. The common tool for all BI projects, The Business Intelligence Development Studio (BIDS) is now called SQL Server Data Tools (SSDT).

1) New Project Dialog Box

Two templates are available here. One is for Integration Services Project, this type is available in previous versions of SSIS as well. The second one is Import Project Wizard, to import a project from SSIS Catalog. To enhance an existing SSIS project, we go for this option.

Rich Look Designer: With all new features added, the designer looks so cool to work.

2) SSIS Toolbox:  

Not the regular Toolbox, call it "SSIS Toolbox". A dedicated toolbox for SSIS tasks and components.

3) Parameters: 

New window in the designer which allows us to specify a runtime value for packages, tasks etc.,

4) Variables and SSIS Toolbox Button: 

Two shortcut buttons can be found on the right most side of the designer for variables and SSIS toolbox respectively.

5) Status Indicators:

In this latest version of SSIS, right after a execution of a task or when we have any issue with the configuration of Task is indicated with Green Tick Mark and Red Cross Mark accordingly. No fill up of Green or Red to the entire task now.

6) Expression Indicators: 

If a connection, component or task are configured with dynamic expressions, they are highlighted with "fx" icon, right on tasks.

7) Enhanced Data Viewers: 

Data Viewers helps us a lot in viewing the data at run time. Now we have this feature little modified as show in the image.

8) New Control Flow Tasks & Data Flow Items:

Business requirements are endless. The business people are hungry of Information. In order to turn the junk into information, the ETL developer has to implement logic using transformations available in the toolbox. In case the requirement is complex and not in hands of within transformations, then .Net Script Task and Script Component always helps us in fixing them. As expected, lets welcome new guests in the form of new tasks and components.

9) Finally, Undo-Redo: 

Thanks for the development team for adding a very small feature, (Undo and Redo) but still very important while designing a package. When this feature was present in the previous versions of SSRS , I was wondering why this was not included in SSIS. Anyways, it’s a good news to get the CTRL-Z and CTRL-Y in SSIS.

10) Zoom In and Out: In the right side bottom corner of the workspace you can find the zoom feature to enlarge or reduce the components size. The complex packages can now be easily navigated adjusting the zoom feature.

11) Brand New Deployment Architecture: This is most important feature in SSIS. I have gone through the deployment process and it is as below.

Hope this helps those looking for an overview in the enhancements in SQL Server 2012.

Happy SSIS'ing ;)

Searching for a Column or Table in Multiple Databases? Here is an easy way!!

Hi Guys
Today while working on a requirement, I was searching for a specific table in my entire SQL Server instance. My Instance contains around 75 databases, and searching manually is not a good idea,  seriously. To find a object in a specific database (not in the whole instance), first thing that comes to any developer's mind is to use System Objects like sys.tables or sys.columns. But again executing and checking the results using below system query on all 75 databases again involves manual effort.


-- System Objects Based Query
SELECT T.Name AS TableName, C.Name AS ColumnName 
FROM sys.tables T
INNER JOIN sys.columns C ON T.Object_ID = C.Object_ID
ORDER BY T.Name

So thought of using CURSOR at-least here. Developer's hate it for its performance but there are certainly few situations where you would like to use it. Here is the solution in the form of CURSOR I designed that picks every table, column and the concerned database to which they belong.


-- Cursor that loops over all the databases in a SQL Server instance
DECLARE @NAME VARCHAR(50)
DECLARE @SQL VARCHAR(500)
DECLARE @DATABASENAME CURSOR
SET @DATABASENAME = CURSOR FOR
SELECT name FROM sys.databases
OPEN @DATABASENAME
FETCH NEXT FROM @DATABASENAME INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE '+ @NAME + 
' SELECT ''' + @NAME + ''' AS [Database], T.name AS TableName, C.name AS ColumnName
FROM sys.tables T
INNER JOIN sys.columns C ON T.object_id = C.object_id'
EXEC (@SQL)
FETCH NEXT FROM @DATABASENAME INTO @NAME
END
CLOSE @DATABASENAME
DEALLOCATE @DATABASENAME
GO

If you want to exclude the four system databases to be avoided from querying, you can make use of a filter with WHERE database_id NOT IN (1, 2, 3, 4)

Hope it is useful for those who work on multiple databases with lot of objects in there. :)