Update views on Multi databases via SSIS Package

Good Friday Readers,

So I got another good challenge at work the other day and thought I would share what I learned in hopes it would help you out.  I did lots of research on figuring out how to do this.  

What did I need to do?

We have multiply databases on a server that have same views on them.  The databases are based on location and the view is filtered on location.  Each view is the same so when we update a view we have to update all the views.  It's not effiecent to run the alter view command on each database, but rather have  loop that automatically does it.  We tried by creating a dynamic loop in sql with a cursor but that was not working.  So I thought of creating an SSIS package to do!  What a great and simple way to do it.  

What do you need?

Here are the tables, data and view sql commands to get started.  I have two databases mlb_Brewers and mlb_Twins.  

Brewers Table Script:

USE mlb_Brewers

CREATE TABLE Players(
   P_ID INT NOT NULL PRIMARY KEY IDENTITY ,
   LastName VARCHAR(255),
   FirstName VARCHAR(255)
)
INSERT INTO Players VALUES ('Braun','Ryan')

INSERT Players VALUES ('Hart','Corey')

CREATE VIEW v_Players AS SELECT LastName + ' , ' + FirstName AS PlayerName FROM Players









Twins Table Script:

USE mlb_Twins

CREATE TABLE Players
(
   P_ID INT NOT NULL PRIMARY KEY IDENTITY ,
   LastName VARCHAR(255),
   FirstName VARCHAR(255)
)

INSERT INTO Players VALUES ('Mauer','Joe')
INSERT Players VALUES ('Revere','Ben')

CREATE VIEW v_Players AS SELECT LastName + ' , ' + FirstName AS PlayerName FROM Players



Sweet now we have our tables and views set up!  Let's query the views quickly to make sure it looks how we wanted it.

USE mlb_Brewers
SELECT * FROM v_Players


USE mlb_Twins
SELECT * FROM v_Player












So our view works.  Now onto the fun with SSIS.  This is a preview of the entire package.














































Step 1.  Set Up Variables

Code - String Type
CurrentDatabase - String Type
DatabaseList - Object Type


Step 2.  Setting up Connections

We need to set up the following two database connections.

Master Database on the Server
Variable driven database on the Server


The variable driven database is where all the magic happens.  We create a normal database connection, but change the expression afterwards (located in the properties of the data source) to our variable CurrentDatabase.  Look at the screen shows below for reference.







Step 3.  Script Task


We need to get the alter statement.  I create the package to grab this command from a text file.  The reason  I do this is so you can quickly change the alter statement without having to go into the package. I use a script task with a read only variable of Code.  We will assign the command in the text file to the this variable.  








The Code to assigning the value of the text file to the variable is as followed.


Public Sub Main()
       '
       ' Add your code here
       '
        'initialize StreamReader class for text file
        Dim streamReader As New IO.StreamReader("C:\SQLCode.txt")
        ' Read the StreamReader To End and assign to local variable
        Dim StreamText As String = streamReader.ReadToEnd()

        'making sure there is code in the text file before going any on.
        If StreamText.Length < 1 Then
            ' failing the package
            Dts.TaskResult = ScriptResults.Failure
        Else
            ' assign SSIS variable with value of StreamText local variable.
            Me.Dts.Variables("Code").Value = StreamText
            ' going onto the next step
            Dts.TaskResult = ScriptResults.Success
        End If

   End Sub

Step 4.  Grab the database list



Now we need to grab the list of databases we want to through in the SSIS Package.  We can just query the master database to get the list of databases as seen below.
In SSIS the next task we create is the Execute SQL task editor.  The result set will be a full result set.  We then have the result set applied to the variable of DateabaseList. As seen the screen shots below.













Step 5.  Loop Through databases










Now that we have a a list of databases in the variable.  We can create a for each loop and loop through that within in SSIS.  We need to create the enumerator for a type of Foreach ADO Enumerator and the mode is rows in all the tables as seen on this screen shot. 














We need to map the variable to the loop.  To do this we just add the variable of DatabaseList to the variable mappings screen. 

Step 6.  Grab Current Database

Now that we can loop through all the selected databases.  We have to know which database we are currently on so our dynamic connection from earlier will work correctly.  To apply the current database to the variable.  We use some sql magic.


Select Statement: 
SELECT name FROM sys.databases WHERE name = ?

Parameter Mapping: DatabaseList variable
Result Set: CurrentDatabase




















Step 7.  Run SQL Command



We are finally at our last step in this process.  We want to run the custom code in that text file to the current database we are on in the loop.


  Datasource: (local).currentdatbase - This is the database source that use an expression as the database name linked to the variable.

Parameter Mapping: User:Code - This is the variable we created that contains the command from the text file.



















Step 8.  Run SSIS Package

Now the let's see if this works.  Let's run the SSIS Package.















Looks like it worked! Congrats!



I hope this little blog helped you out.  With SSIS the possibilities are endless...





Till next time,



Robert



  

Pivot Data from Rows to Multi Columns

I had a great challenge the other day at work for a client and thought I would post what I learned in hopes it would help others.  I need to pivot some data in SQL Server 2005 from multi rows into multi columns.

What did I all learn in this post?

• Pivot function
• Unpivot function
• row_number function
• Max function
• Case statement
• Left Join

Let's compare what kind of data I was given and how they wanted it outputted.

This is how the data was entered in the database (not the easiest data to work with):



But this is how the client wanted the data displayed:



 So I thought to myself how am I going to do this?  To make this easier for everyone the following are some temp tables I created to re-create what I was doing at work.

Here is the main table I used

DECLARE  @A TABLE (
   ID INT,
   value VARCHAR(10),
   instance INT,
   attributeID VARCHAR(4)
)

INSERT INTO @A VALUES (123,1,1,4391) 

INSERT INTO @A VALUES (123,190,1,4396)

INSERT INTO @A VALUES (123,2,2,4391)

INSERT INTO @A VALUES (123,190,2,4396)
INSERT INTO @A VALUES (124,3,1,4391) 

INSERT INTO @A VALUES (124,235,1,4396)

INSERT INTO @A VALUES (124,1,2,4391) 

INSERT INTO @A VALUES (124,200,2,4396)

Here is the department table:

DECLARE @Department TABLE(
   ID INT,
   Department VARCHAR(200)
)

INSERT INTO @Department VALUES (1,'IT')

INSERT INTO @Department VALUES (2,'Accounting')

INSERT INTO @Department VALUES (3,'Human Resources') 

INSERT INTO @Department VALUES (4,'Finance')

Here is the User table:

Declare @Users table
(

DECLARE @Users TABLE (
   UserId INT,
   FullName VARCHAR(200)
)

INSERT INTO @Users VALUES (190,'Robin Yount')

INSERT INTO @Users VALUES (235,'Ryan Braun') 

INSERT INTO @Users VALUES (200,'Prince Fielder')

Well if you could not tell I am a huge fan of Milwaukee Brewers fan!  Let's just hope they can beat the Cards in the playoffs this year.  Well back to the T-SQL stuff...

Before looking at the entire picture at once lets break down this down in parts.

First let's grab the basic information before pivoting this data.

SELECT ID, 
       value, 
       instance, 
       RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum 

FROM @A WHERE attributeID IN (4391,4396) 

So what's the row number for you are asking?  Well we need that for the next step to be able to pivot the data.

Our second step is to pivot this data.

SELECT  ID,
   [01] AS Dept01,[02] AS Name01,
   [03] AS Dept02,[04] AS Name02FROM (
    SELECT ID, value, instance,
           RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum
           FROM @A WHERE attributeID IN (4391,4396) 
    )A
pivot (MAX(value) FOR rownum IN ([01],[02],[03],[04])) AS pvt

Let's good so far right?  Well we don't want to show ID's multi times, but rather just once.  So at this point I was struggling to figure out the next step for a while.  I knew I had to unpivot the data, but how?  After doing some research I came across a very helpful blog.  So special thanks to Mangal for sharing the knowledge of SQL!

Our third step is to unpivot the data.  We want to create only three columns; ID, DeptID, UserID.  This will make it way easier when converting the rows to columns.


 

SELECT ID,
   DeptID, 
   UserID
   FROM ( 
       SELECT  ID,
           [01] AS Dept01,[02] AS Name01,
           [03] AS Dept02,[04] AS Name02
       FROM (
           SELECT ID, 
               value, 
               instance,
               RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum
           FROM @A WHERE attributeID IN (4391,4396) 
       )A
       pivot (MAX(value) FOR rownum IN ([01],[02],[03],[04])) AS pvt          
   )main 
unpivot (UserID FOR Names IN (Name01,Name02))n
unpivot (DeptID FOR Dept IN (Dept01,Dept02))i 

WHERE RIGHT(Names,2)= RIGHT(Dept,2)

Now the hard part is done and what is left is the easy stuff!  So now I wanted to convert all those ID's such as User ID and Department number to their actually value.  Simple just had to join the correct table to the column.  Our fourth part is to join the department and user table. Please note we don't need the UserID and DeptID columns anymore I just added them so show you the relationship.

SELECT a.ID,
   a.UserID,
   b.FullName AS NamesValue,
   a.DeptID,
   c.Department
   FROM (
       SELECT ID,
           DeptID, 
           UserID
       FROM ( 
           SELECT  ID,
               [01] AS Dept01,[02] AS Name01,
               [03] AS Dept02,[04] AS Name02
           FROM (
               SELECT ID, 
                   value, 
                   instance,
                   RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum
               FROM @A WHERE attributeID IN (4391,4396) 
           )A
           pivot (MAX(value) FOR rownum IN ([01],[02],[03],[04])) AS pvt          
       )main 
       unpivot(UserID FOR Names IN (Name01,Name02))n
       unpivot (DeptID FOR Dept IN (Dept01,Dept02))i
       WHERE RIGHT(Names,2)= RIGHT(Dept,2)
   )a

LEFT JOIN (SELECT * FROM @Users) AS b ON a.UserID = b.UserId 

LEFT JOIN (SELECT * FROM @Department) AS C ON A.DeptID = c.ID

So only one step left in this quest to display the data as the client wanted.  We just have to convert those rows into columns.  The last step is converting the rows into columns as seen below.

SELECT ID AS ReferralID
   , MAX(CASE WHEN DeptID = 1 THEN NamesValue END) [IT]
   , MAX(CASE WHEN DeptID = 2 THEN NamesValue END) [Accounting]
   , MAX(CASE WHEN DeptID = 3 THEN NamesValue END) [Human Resources]
   , MAX(CASE WHEN DeptID = 5 THEN NamesValue END) [Finance]

 FROM (  
   SELECT a.ID,
       a.UserID,
       b.FullName AS NamesValue,
       a.DeptID,
       c.Department
   FROM (
       SELECT ID,
           DeptID, 
           UserID
       FROM ( 
           SELECT  ID,
               [01] AS Dept01,[02] AS Name01,
               [03] AS Dept02,[04] AS Name02
           FROM (
               SELECT ID, 
                   value, 
                   instance,
                   RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum
               FROM @A WHERE attributeID IN (4391,4396) 
           )A
           pivot (MAX(value) FOR rownum IN ([01],[02],[03],[04])) AS pvt          
       )main 
       unpivot (UserID FOR Names IN (Name01,Name02))n
       unpivot (DeptID FOR Dept IN (Dept01,Dept02))i
       WHERE RIGHT(Names,2)= RIGHT(Dept,2)
   )a
   LEFT JOIN (SELECT * FROM @Users) AS b ON a.UserID = b.UserId 
   LEFT JOIN (SELECT * FROM @Department) AS C ON A.DeptID = c.ID)d 

GROUP BY(ID)

So that's it.  Not to bad to write up.  Well hopefully this help you out as I learned some neat tricks with SQL Server.

TGIF!

Robert