SSIS 2012 -How To Get List of SSIS Packages With Folder Names From MSDB

Scenario:

A quick question sometime comes in our minds, How can I get the list of SSIS Packages those I have deployed to SQL Server with Folder Names?

Solution:

SQL Server 2012, provide two system tables in MSDB database those can be used to answer our question.
1.  [dbo].[sysssispackages]
2.  [dbo].[sysssispackagefolders]

Dbo.sysssispackages table will provide information about SSIS Package such as name, description,package createdate, foloderid (so we can join with sysssispackagefolders to get folder name) and other columns.

Dbo.sysssispackagefolder will provide us the folder name, folderid and parentfolderid. We can join above tables and get Packages with Folder information.

SELECT f.foldername            AS FolderName,
       p.name                  AS PackageName,
       p.description           AS [Description],
       P.createdate            AS PackageCreateDate,
       Suser_sname(p.ownersid) AS OwnerName,
       CASE
         WHEN p.isencrypted = 0 THEN 'N'
         ELSE 'Y'
       END                     AS [IsEncrypted]
FROM   [dbo].[sysssispackages] p
       INNER JOIN [dbo].[sysssispackagefolders] f
               ON p.folderid = f.folderid



SSIS - How To Read Object Type Variable In Script Task [Script Task SSIS]

Scenario:

It is frequently asked question, I can read object type variable by using For-each Loop in SSIS but How  can I read the object type variable in Script Task.

Solution:

Here is quick demo that will show you step by step approach to read Object Type variable in Script Task.

Step 1: ­­­­­­­­­­­­­
Create connect to your SQL Server and then use Query in Execute SQL Task as given below and save the result set to VarObject Variable that is object type variable.

Select 1 as ID, 'AAMIR' AS Name
Union All
Select 2 as ID, 'Raza' AS Name


 Set the Result Set to VarObject Variable

Step 2:
Configure Script Task as shown in snapshots for your SSIS Package

Select the Script Language and Variable you need to use in Script Task

Add the Code as shown in snapshot to your Script task

Code that you see in snapshot added by me in Script task
using System.Data.OleDb;

 OleDbDataAdapter A = new OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            A.Fill(dt, Dts.Variables["User::VarObject"].Value);
// TODO: Add your code here

            foreach (DataRow row in dt.Rows)
            {
                string ID;
                string Name;
                object[] array=row.ItemArray;
                ID = array[0].ToString();
                Name = array[1].ToString();
                // I Declare both variables ID And Name as String, So I can show in Messagebox. You can Declare ID as INT and set the value and use
                //However you want to use.
                MessageBox.Show("ID Value="+ ID +" AND Name Value=" + Name);

Final Output:
Run your SSIS Package, It is going to Run Execute SQL Task in which it will load the Result Set to VarObject Variable and then inside Script task we are using VarObject variable to read values from and displaying them.

TSQL INTERVIEW QUESTIONS [SQL Server Interview Questions]

Here are TSQL Interview Questions and Database Interview questions. I am going to do my best to provide a good list of real time questions.  I am not going to categories the questions depending upon topics but rather have them one after one as you never know what interviewer is going to ask you next ;). All the best with Database developer interview!

1.What is Heap?
2. What are DMVs in SQL Server and name two of them that will you use to find out long running queries or stored procedures?
3. If you need to find out the last time the job was run, which database will help you to find that information?
5. What are 2 differences between Delete and Truncate?
6. Let me know at least five differences between a Stored Procedure and a Function?
7. What is parameter sniffing in Stored Procedure? How you will overcome that?
8. Can you create a function and have dynamic SQL in it?
9. What are CROSS APPLY and OUTER APPLY in SQL Server and why do we need to use it?
10. What are Window functions in TSQL? In SQL 2012. new window functions are added such as FIRST_VALUE, LAST_VALUE, LEAD, LAG, Explain the definition with scenario where will you use these?
13.  I heard indexes are good, can I create cluster index on multiple columns or cluster index can be created only on one column?
14.  I need to create non-cluster index on 20 columns, data type of each column is Char (7000), can I include all of the columns in non-cluster index? What is max number of columns that I can include in non-cluster index, what other options I have if there is limit on number of columns?
25.  I want to audit information such as who created table, who has dropped table, what are my options?
27. We have an application that is running on SQL Server 2005. Our company has decided to have no more SQL server 2005 but only SQL Server 2012. We have migrated the Database to SQL Server 2012. How would I set the compatibility to SQL Server 2005 so application can run without any issues?  (Video Demo)
30.  How do you handle exceptions in TSQL?
31. List at least 5 system functions those you have used or will use to Retrieve Error information?
32. What is uncommittable state?
34. What are the things will you look into when you do performance tuning of TSQL query?
35. What is the difference between Rollup and Cube, What are alternative of these in SQL Server 2012?
36.     I have sale column in one of the table, Explain two different queries those you can use to have running total in your final output?
38.     Have you heard Wait for, Break, Continue key words in TSQL , explain about each of them.
39.     If you have created a table with identity (1, 1), is that possible that sequence can break and if yes then in which situation that happen?
40.     What are common table expressions (CTE)? what is Recursive CTE? In which situation have you used it? If you want to limit recursion how can you do that in CTE?
41.     What type of parameters you can provide to Stored Procedure? Explain where did you used them and why?
42.  What string functions have you used, if you need to find the starting position of character or string which function will you use?
45.     Can you insert ordered data into a SQL Server table and will SQL Server store data in ordered you have inserted? And if you will write Select Query to extract that data, will you retrieve in order the way you have inserted or not?
46.     Explain a scenario where do you need to write Correlated Subquery?
47.     When do you like to create filter index, do they take less or more space than cluster index on Disk? What are advantages of using Filter index?
48.     Sometime when you run your query you receive this warning with result sets as well “Warning: Null value is eliminated by an aggregate or other SET operation”. How can you avoid this?
49.     If you need to find the difference in days between two dates which function will you use?
50.     Can you use having clause without group by?
51.     Let’s say you have tableA on SERVERA and ServerB. If you want to see all the records from TableA on both servers, how will you be able to do that?
52.     There is an Excel file siting on your Desktop. You want to write a query to read that file and see the records in SSMS. How will you do that in TSQL?
53.     You have few word documents those you want to save in SQL Table, What data type will you use to save the word document in SQL Table?
54.     Let’s say you have used dynamic SQL, you have created a temp table inside Dynamic SQL , Will you be able to read data from that temp table after execution of dynamic SQL?
55. What keyword will you use to retrieve unique rows from table?
56.  Can I execute Stored Procedure inside Function?
57.  What is for xml, in which scenario have you used?
58.  Can you use Case Statement in Order by Clause?
59.  Which data type will you use that is time zone aware?
60.  What is Sparse Column?
61.  What are different ways to run dynamic SQL? If I need to pass parameter(s) to dynamic SQL, how can I do that?
62.  Cross join gives you Cartesian product, where have you used Cross Join?
63.  If I need to add “Total” Line at the end of result set, what option do I have?
65. You have built a big dynamic SQL query. The total length of this built query is more than 20,000 characters, now you want to see if your built query looks good, how will you print that query?
66.     How will you transpose column into row by using TSQL?
68.  Can you declare variable in Create View Statement if not then what object you prefer to create so you can use Select statement like view?
69.  What is Self-Join; explain a scenario where you need to use that?
70. What are the steps to create partitioned table? what are the benefits of creating it?
71.  What is Switch Operator in TSQL?
72.  If you need to get month name, which TSQL Function will you use?
73.  If you need to get Year which function will you use?
74.  If you need to extract data from Oracle table and join with SQL table, what steps are required? Explain sample query as well?
75. What new features are available in SSMS those were not in previous versions? Any third party tools those can be added in to SSMS and are helpful for daily use?
76.  Can you save query results to flat file from SSMS? (Video Demo)
77.  What are partitioned views and where we need to create these views?
78.  To connect to SQL Azure, do you need any different tool or can you use SSMS?
79. What is Key Lookup operator (bookmark lookup) and how can you remove that?
80. What is estimated Execution plan?  How can we display execution plan in text?
81. What is plan cache and how does it relate to Execution plan?
82. Which is better Index Scan or Index Seek? In which scenario Scan is better than Seek?
83.     What type of Trigger(s) can be created on Views?
86.     What is Computed column and have you used it? Are there any disadvantages of using computed column?
87.  Is ORDER BY clause valid in views, Inline functions, Derived tables, Sub queries and common table expressions?
88.     I want to create view with order by clause by any means, how can I do that?
91.     When should you run UPDATE STATISTICS?
92.     To see the total space taken by database which query will you run?
93.     What is data compression? What level of compression is available?  Should we enable on OLTP tables or DWH Tables to get benefit of this feature?
97.     If you add a new column in CDC enabled table, Will it be automatically included in CDC?
98.     If you need to find out, CDC is enabled on Database, how will you do that?
100. What are SQL Constraints and which one have you used often? Explain with scenarios where do you need them?
102. If you need to save more than two 2 GB file in SQL Server Table, which data type will you use?
103. What is the lowest level of transaction lock Table, Row or Field?
104. Can we create Unique Constraint on more than one columns?
105. Can we create more than one Unique Constraint on a table?
106. Can you use these functions in SQL Server and what will they return {fn NOW()},{fn CURRENT_TIMESTAMP()}. In TSQL they are equivalent to which functions?
107. What is the difference between Convert () and format () function?
108. Can you create Primary key Constraint on a column that has one Null value?
109. Can you create Unique Constraint on a column that has one Null value?
110. We can create only one cluster index per table, Is this statement true?
111. What is the difference between a trigger and Stored Procedure?
117. What are different types of cursors available in SQL Server? Which one do you use often? (Video Demo: Playlist contains Types of Cursors)
118. If you do not want to write your code by using cursor, what alternatives do you have in TSQL?
120. What is Columnstore Index? Should we consider using Columnstore indexes in Data Warehousing or in OLTP database?
121. To generate manual checkpoint, which TSQL statement will you use?
122. You are doing performance tuning on your query, can you display  Estimated Execution plan and Actual Execution plan together?
123. Can estimated execution plan be different from Actual execution plan?  And if yes what is the reason behind that?
124. What is the difference between DMV and DMF?
125. Which system table or DMV can you use to find out all the tables those do not have cluster index?
126. What are the benefits of using sp_executesql over EXEC?
127. What is Federated database?
128. Can I update the column on which I have created column store index?
129. What is Sql Variant data type?

130.  Can you use ranking functions without using Order by clause?
131. What is In-Memory OLTP?
132. Where will indexes exist in In-Memory OLTP?
133. Where will data exists in In-Memory OLTP?
134.  What is multi-version optimistic concurrency control in In-Memory OLTP?
135.  Do we have to have all tables of database in in memory for In-Memory OLTP or we can choose few out of all tables?
136. What is Memory Optimization Data FileGroup?
137. Can we write a query to extract records from In-Memory Table and Table which is stored on Disk?
138. What is Non-Durable Table? Do Non-durable tables use transaction log? Are they recoverable or not?
139. Can you create cluster index on memory optimized table (In-Memory OLTP Table)?

140. Does In-Memory OLTP remove lock and latch contention?
141. Which System view can you use to find out if a column exists in a database? (Video Demo)
142. If you need to find out that when an object was modified last time, which system table will you use? (Video Demo)
143.You need to add multiple columns to already existing table, Can you do in one statement or you have to write multiple statement?  (Video Demo)
144. A developer has written all the Stored procedure by using Full qualified name. You have requested to restored the database to DEV machine with different name from production. You start getting errors when tried to execute Stored procedures and you have figured out that you have to remove database name from each of Stored Procedure definition. How would to perform this task?
145. You need to add a column such as ModifiedBy to all the tables in a database, How would you do that? (Video Demo)
146. Explain a scenario, Where do you have used Merge Statement?
147. Explain a situation, Where have you used dynamic SQL?
148. What is the difference between Rank() and Dense Rank()
149. What is the difference between Raiserror and Throw?
150. What is Choose() function in SQL Server 2012? (Video Demo)
151. What type of commands are available in TSQL?
152. What is Logical Query Processing Order in SQL Server?
153. What are Static Type of Cursors in SQL Server? ( Video Demo)
154. What is Dynamic Type of Cursor in SQL Server?  ( Video Demo)
155. What is Forward Only Static Cursor in SQL Server?  ( Video Demo)
156. What is Forward Only Dynamic Cursor in SQL Server? (Video Demo)
157- What is KEYSET Cursor in SQL Server?   (Video Demo)
158- What is the difference between LOCAL AND GLOBAL Cursor in SQL Server?  (Video Demo)

How to Questions:

  1. How would you switch the database inside the Stored Procedure?
  2. How would you reseed identity column in SQL Server Table?
  3. How would you determine if the expression is Numeric?
  4. How does IN Clause affect performance?
  5. How would you find top record from a SQL Server table?
  6. How would you get the last record from a SQL Server table?
  7. How would you optimize a query or Stored Procedure?
  8. How would you concatenate string and integer value in TSQL?  (Video Demo)
  9. How would you import Image from file system to SQL Server Table by using TSQL?
  10. How would you select some random records from a SQL Server Table?
  11. How would you store query results to a file automatically? (Video Demo)
  12. How would you get the list of all local and global temp tables in SQL Server?
  13. How would you get the size of a SQL Server Table?
  14. How would you find out if the table is used by a Stored Procedure? (Video Demo)
  15. How would you get the list of all SQL Server Agent Jobs?  (Video Demo)
  16. How would you find the version of SQL Server?  (Video Demo)
  17. How would you get Month Name from Date in SQL Server?
  18. How would you remove special characters such as $,#,@ from a SQL Server Table Column?
  19. How would you find running queries on SQL Server?
  20. How would you find open Transactions on SQL Server?
  21. How would you find if process is blocked by another process?
  22. How would you disable a trigger in SQL Server? (Video Demo)
  23. How would you import data to a SQL Server table from file?
  24. How would you refresh view to update the metadata?   (Video Demo)
  25. How would you debug Stored Procedure in SQL Server Management Studio?
  26. How would you get time from datetime column values?
  27. How would you get list of all user databases in SQL Server? (Video Demo)
  28. How would you prevent SQL Injection?
  29. How would you replace Null values with 'Unknow'? (Video Demo)
  30. How would you transpose columns to rows in TSQL?
  31. How would you split comma delimited string (e.g value1,value2,value3....) into a table?
  32. How would you transpose rows into columns in TSQL?
  33. How would you prevent Parameter Sniffing?
  34. How would you read uncommitted data in SQL Server?  (Video Demo)
  35. How would you do Fuzzy matching using TSQL?
  36. How would you generate new GUID (unique-identifier) in SQL Server?
  37. How would you set Transaction Isolation Level for a session in SQL Server?
  38. How would you send an email from SQL Server?  (Video Demo)
  39. How would you truncate all the tables in a Database in SQL Server? (Video Demo)
  40. How would you drop identity property of a column? (Video Demo)
  41. How would you execute .sql file form command line?
  42. How would you check if column exists for a table or view in Database? 
  43. How would you get the list of tables with triggers created on them?  (Demo Video)
  44. How to Drop/Delete all the triggers in a SQL Server Database in SQL Server?   (Demo Video)
  45. How to get list of all disabled/enabled triggers in SQL Server Database?  (Video Demo)
  46. How to disable all the triggers in a SQL Server Database in SQL Server?  (Video Demo)
  47. How to Enable all disabled Triggers in a SQL Server Database?   (Video Demo)
  48. How would you add a new column to existing SQL Server table and populate with Default Values?   (Video Demo)
  49. How to search object such as Table/View/Stored Procedure/function etc. in all the database in SQL Server Instance?   (Video Demo)
  50. How to check if value is Numeric by using ISNUMERIC and Try_Convert Function in SQL Server?  (Video Demo)
  51. How to change Database Name inside the Stored procedure in SQL Server?  (Video Demo)
  52. What is Concat Function in TSQL and How it is different from using + for concatenation. (Video Demo)

SSIS - How To Run SSIS Package By Using Excel Macros [ dtexec ]

Scenario:

After developing SSIS Package, The next step is to schedule this package. We can schedule by using SQL Server agent etc.  But how about your company asks you to create Excel Macros with some button and whenever their users want to run the package they click on the button and execute SSIS Package. This might not be requirement most of the time but I have seen these types of requests.

Solution:

To execute our package by using Excel , we have to create Macros.  Follow the steps below to make this work
Step 1:

Open Excel. Go to Developer Tab and then Go to Insert and Insert Command Button as shown in snapshot.

Step 2:
Place the Command Button anywhere you like on Excel sheet. Then Right Click on Command Button and go to Properties and set the Caption for Button as shown below

Step 3: 
Double Click on the Button and write code as shown in snapshot

Code written in snapshot:

Private Sub CommandButton1_Click()

   'Below Three Lines are used to run SSIS Package

'Declare Command as String
    Dim Command As String

'Set the value of Command, that includes dtexec and SSIS Package Location
'Replace C:\Users\ashahzad\Documents\Visual Studio 2008\projects\SSISProjects\Package2.dtsx with your package location

    Command = "dtexec /f ""C:\Users\ashahzad\Documents\Visual Studio 2008\projects\Load_Database_Scrub\Load_Database_Scrub\Package2.dtsx"""
'Run the Package

    Call Shell(Command, 0)

End Sub

After writing code, save it and close the window.

Step 4:
Save the File as shown in snapshot ( Excel Macro-Enabled Workbook)

Step 5:
Open the Book1.xlsm where you saved and Click on Button as shown in snapshot. It will run your SSIS Package that you have provided in VBA Code.

SSIS - Replace Multiple Characters/Words From Column [How to Use Script Component]

Scenario:

Let’s say we need to load data from flat file source. The Name column in flat file has some non ascii characters as well some other words those we do not want to load and want to replace with blank space. To replace these characters and words we used derived column but after few days we got a new list of characters or garbage words those we also have to replace. We do not want to open our package again and write expressions in Derived column to replace. Our goal is to build a SSIS Package where we can create a list of garbage words and then use that to clean data in our Name column.

Solution:

As our garbage words list can change anytime so we will be creating a variable in our SSIS Package to hold list of garbage characters or words. Each character or word will be separated by using Pipe (|). We can use SSIS Configuration to provide values to this variable or we provide the values in SQL Server agent job.
Here are steps how we are going to build our SSIS Package.

Step 1:
Create a variable in SSIS Package and provide the characters/words those you want to replace in your input column with blank space.
I have created “BadWordsList” variable with some characters and words “Shahzad|Test|Å’|Õ|Û|ÞÞ” those are garbage for me and want to replace them in my input column with blank space.




Step 2:
Create connection to your text file by using flat file connection manager. I have used below data for this example in text file.
Name, Age
AamirÅ’ Shahzad Test,33
RazaÛ Ali Shahzad,32
Najaf Ali Shahzad,11
ÕJuily,23
ÞÞRobert,54

Step 3:
Bring Script component in Data Flow Pane and then connect Flat File Source to it and configure as shown in snapshots.





Code that I included in Script Component  can be seen in snapshot.

using System.Text.RegularExpressions;
 string garbagelist = Variables.BadWordsList;
 Row.SCName = Regex.Replace(Row.Name, garbagelist, "", RegexOptions.IgnoreCase);


Final Output : 

Add Multicast Transformation in Data Flow task and Connect Script Task to it( We are using Multicast here just for testing). Add Data Viewer between Script Component and Multicast to see the results.

As we can see in the snapshot that all the garbage characters/words are removed with blank space by using script component and SC_Name column does not have any of those characters/words.



SSIS - Load Data To Multiple Destinations From Single Source [ How To Use Multicast Transformation]

Scenario:

Sometime we have requirement to read the data from single source and then load to multiple destinations. Let’s say we are getting a flat file as source and we have to load this file into one of the SQL Server table as well load into Oracle Database Table.

Solution:

We can use Multicast transformation in SSIS. Multicast transformation takes one input and creates multiple outputs. Each of the output is replica of input.

Step 1:
Create Flat File connection to your source file. In this example I am using below data in text file.
CountryName,SalePersonName,SaleAmount
uSA,aamir shahzad,100
Italy,andy,300
UsA,Mike,500
brazil,Sara,1000
INdia,Neha,200
Brazil,Anna,200
Mexico,Anthony,500
Mexico,Anthony,test500

Step 2:

In data flow task, use the flat file source and then connect flat file source with multicast. we need to load two destination tables ( SQL Server and Oracle). I have used two OLE DB Destinations and configured them. You can use destinations according to your requirements. Connect The output of Multicast to SQL Server Table and then connect other output to second Destination as shown in below snapshot.

Final Output:
As we can see in the final output snapshot, there are 8 rows from source and then two outputs are created by using Multicast Transformation. Each output data is replica of input data.


SSIS - Read SSIS Package File(.dtsx) for Information [Connection Managers,Transformations,Variables,Configuration,Expressions,Log Providers]

Scenario: 

Sometime we create many SSIS Packages and then we do not have any central Database where we can go and find out what Connection Managers are used in my Package X, What and how many variables are used in my Package, Were any expressions written on those variables, Was configuration used for the Package. To find out the answer of these questions and so many other questions; Developers always have to open the package and then find out the information. At one point on one of my assignment we had more than 200 SSIS Packages.I wanted to build a Database where I can save all the information (Metadata) about Package components and with single query bring the information instead of to find answer of any question/s I have to open the package and dig into it.


Solution:

To find the answer of questions, lets say how many connection managers are used in Package X Or were any expressions written on variables in Package X. We need to save different information from Package file(.dtsx).
Here is step by step approach to create complete solution.Sorry at few points I did not keep best practices in mind but headed towards just to complete the code. But I am sure it will work for you to provide Metadata information about your SSIS packages and make investigation quick:)

Step 1: 
Create a database name Sandbox. You are more than welcome to create with any name you like but you have to make small changes in Scripts accordingly.

Create Database Sandbox
go

Step 2: 
Create all given tables , I created Table called dbo.Packages with Primarykey PkgID that I used in all other tables as Foreign key to make the relationship So we can find any information related to package.


--Drop and Create All Tables
USE Sandbox

GO

IF EXISTS (SELECT 1
           FROM   sys.objects
           WHERE  name = 'FK_Packages_PkgVariable')
  ALTER TABLE [dbo].[PkgVariable]
    DROP CONSTRAINT FK_Packages_PkgVariable

IF EXISTS (SELECT 1
           FROM   sys.objects
           WHERE  name = 'FK_Packages_PkgConfiguration')
  ALTER TABLE [dbo].[PkgConfiguration]
    DROP CONSTRAINT FK_Packages_PkgConfiguration

IF EXISTS (SELECT 1
           FROM   sys.objects
           WHERE  name = 'FK_Packages_PkgConnectionManager')
  ALTER TABLE [dbo].[PkgConnectionManager]
    DROP CONSTRAINT FK_Packages_PkgConnectionManager

IF EXISTS (SELECT 1
           FROM   sys.objects
           WHERE  name = 'FK_Packages_PkgLogProvider')
  ALTER TABLE [dbo].[PkgLogProvider]
    DROP CONSTRAINT FK_Packages_PkgLogProvider

IF EXISTS (SELECT 1
           FROM   sys.objects
           WHERE  name = 'FK_Packages_PkgEventHandler')
  ALTER TABLE [dbo].[PkgEventHandler]
    DROP CONSTRAINT FK_Packages_PkgEventHandler

IF EXISTS (SELECT 1
           FROM   sys.objects
           WHERE  name = 'FK_Packages_PkgTransformation')
  ALTER TABLE [dbo].[PkgTransformation]
    DROP CONSTRAINT FK_Packages_PkgTransformation

IF EXISTS (SELECT 1
           FROM   sys.objects
           WHERE  name = 'Pk_Package')
  ALTER TABLE dbo.Package
    DROP CONSTRAINT Pk_Package

IF EXISTS(SELECT 1
          FROM   sys.objects
          WHERE  name = 'Package')
  DROP TABLE dbo.Package

IF EXISTS(SELECT 1
          FROM   sys.objects
          WHERE  name = 'PkgConnectionManager')
  DROP TABLE dbo.PkgConnectionManager

IF EXISTS(SELECT 1
          FROM   sys.objects
          WHERE  name = 'PkgVariable')
  DROP TABLE dbo.PkgVariable

IF EXISTS(SELECT 1
          FROM   sys.objects
          WHERE  name = 'PkgConfiguration')
  DROP TABLE dbo.PkgConfiguration

IF EXISTS(SELECT 1
          FROM   sys.objects
          WHERE  name = 'PkgLogProvider')
  DROP TABLE dbo.PkgLogProvider

IF EXISTS(SELECT 1
          FROM   sys.objects
          WHERE  name = 'PkgEventHandler')
  DROP TABLE dbo.PkgEventHandler

GO

IF EXISTS(SELECT 1
          FROM   sys.objects
          WHERE  name = 'PkgTransformation')
  DROP TABLE dbo.PkgTransformation

GO

CREATE TABLE dbo.Package
  (
     PkgID                    INT IDENTITY(1, 1),
     PackageName              VARCHAR(1000),
     PackagePath              VARCHAR(1000),
     PackageFormatVersion     VARCHAR(1000),
     CreatorName              VARCHAR(1000),
     CreationDate             VARCHAR(1000),
     VersionMajor             INT,
     VersionMinore            INT,
     CreatorComputerName      VARCHAR(1000),
     ProtectionLevel          VARCHAR(1000),
     EnableConfig             INT,
     MaxConcurrentExecutables INT,
     LoadDateTime             DATETIME DEFAULT Getdate(),
     CONSTRAINT [Pk_Package] PRIMARY KEY CLUSTERED (PkgID)
  )

GO

CREATE TABLE dbo.PkgConnectionManager
  (
     ID                    INT IDENTITY(1, 1),
     ConnectionManagerName VARCHAR(1000),
     DelayValidation       INT,
     DTSID                 UNIQUEIDENTIFIER,
     DESCRIPTION           VARCHAR(1000),
     ConnectionType        VARCHAR(1000),
     PkgID                 INT
  )

GO

CREATE TABLE dbo.PkgVariable
  (
     ID                   INT IDENTITY(1, 1),
     VariableName         VARCHAR(1000),
     Expression           VARCHAR(MAX),
     EvaluateAsExpression INT,
     Namespace            VARCHAR(1000),
     ReadOnly             INT,
     RaiseChangedEvent    INT,
     IncludeInDebugDump   INT,
     DTSID                UNIQUEIDENTIFIER,
     DESCRIPTION          VARCHAR(1000),
     CreationName         VARCHAR(1000),
     PkgID                INT
  )

GO

CREATE TABLE Dbo.PkgConfiguration
  (
     ID                    INT IDENTITY(1, 1),
     ConfigurationName     VARCHAR(1000),
     ConfigurationType     VARCHAR(1000),
     ConfigurationString   VARCHAR(1000),
     ConfigurationVariable VARCHAR(1000),
     DTSID                 UNIQUEIDENTIFIER,
     DESCRIPTION           VARCHAR(1000),
     CreationName          VARCHAR(1000),
     PkgID                 INT
  )

GO

CREATE TABLE dbo.PkgLogProvider
  (
     ID              INT IDENTITY(1, 1),
     LogProviderName VARCHAR(1000),
     ConfigString    VARCHAR(1000),
     DelayValidation VARCHAR(1000),
     DTSID           UNIQUEIDENTIFIER,
     DESCRIPTION     VARCHAR(1000),
     CreationName    VARCHAR(1000),
     PkgID           INT
  )

GO

CREATE TABLE dbo.PkgEventHandler
  (
     ID                   INT IDENTITY(1, 1),
     EventName            VARCHAR(1000),
     ForceExecValue       VARCHAR(1000),
     ExecValue            VARCHAR(1000),
     ForceExecutionResult VARCHAR(1000),
     Disabled             VARCHAR(1000),
     FailPackageOnFailure VARCHAR(1000),
     FailParentOnFailure  VARCHAR(1000),
     MaxErrorCount        INT,
     ISOLevel             VARCHAR(1000),
     LocaleID             INT,
     TransactionOption    INT,
     DelayValidation      INT,
     DTSID                UNIQUEIDENTIFIER,
     DESCRIPTION          VARCHAR(1000),
     CreationName         VARCHAR(1000),
     PkgID                INT
  )

CREATE TABLE dbo.PkgTransformation
  (
     ID       INT IDENTITY(1, 1),
     TaskName VARCHAR(1000),
     TaskType VARCHAR(1000),
     PkgID    INT
  )

ALTER TABLE dbo.PkgVariable
  WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgVariable] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)

ALTER TABLE dbo.PkgConfiguration
  WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgConfiguration] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)

ALTER TABLE dbo.PkgConnectionManager
  WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgConnectionManager] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)

ALTER TABLE dbo.PkgLogProvider
  WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgLogProvider] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)

ALTER TABLE dbo.PkgEventHandler
  WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgEventHandler] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)

ALTER TABLE dbo.PkgTransformation
  WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgTransformation] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)

ALTER TABLE dbo.PkgVariable
  CHECK CONSTRAINT [FK_Packages_PkgVariable]

ALTER TABLE dbo.PkgConfiguration
  CHECK CONSTRAINT [FK_Packages_PkgConfiguration]

ALTER TABLE dbo.PkgConnectionManager
  CHECK CONSTRAINT [FK_Packages_PkgConnectionManager]

ALTER TABLE dbo.PkgLogProvider
  CHECK CONSTRAINT[FK_Packages_PkgLogProvider]

ALTER TABLE dbo.PkgEventHandler
  CHECK CONSTRAINT[FK_Packages_PkgEventHandler]

ALTER TABLE dbo.PkgTransformation
  CHECK CONSTRAINT[FK_Packages_PkgTransformation] 

Step 3:
Now lets create SSIS Package to load information in these tables by reading our ssis packages(.dtsx) files.
Create a variable for the folder path where your all SSIS Packages are sitting. The Package is going to read each of package file (XML) and load into dbo.Test table and then parse the xml information and load required information into related tables those we have already created above.


Step 4: 
You have to create two connections to your Database , Create One OLEDB and second ADO.Net. We will use Conn_SQL(OLE DB) in our Execute SQL Task/s and we will use MyADOConn (ADO.NET)  in script task. In Script task we will read Package File ( XML) and load into dbo.Test by using ADO Connection.

Step 5: 
Configure For-each Loop Container as shown below. The purpose of Foreach loop container is to read one package file at a time so we can get metadata information from that file and load into our tables.




Step 6: 
After configuring Foreach Loop, Bring Execute SQL Task inside foreach loop and configure as shown below. We are Truncating dbo.Test Table so we can load XML data( Package File) on each of loop iteration. If you have not create test table yet, use this script

Create table dbo.Test(Col NVARCHAR(MAX))


Step 7: 
Bring Script Task inside your Foreach loop and configure as shown below.

Step 8:
As you have clicked Edit Script. Provide below script to Script task. Script task is going to read the file and load into dbo.Test table.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Xml;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace ST_ec5075ad59b94e3c8c58d4e3fa56755d.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/
     

        public void Main()
        {
            string filepath;
            filepath=Dts.Variables["User::VarFileName"].Value.ToString();
            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)(Dts.Connections["MyADOConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
            StreamReader reader = File.OpenText(filepath);
            string input = null;
            string abc = null;
            string Col;
            myADONETConnection.Close();
            while ((input = reader.ReadLine()) != null)
            {

                abc =abc+ input;

            }
            Col = abc.ToString();
            myADONETConnection.Open();
            string queryString = "insert into sandbox.dbo.Test(Col) Values(@field1)";
            SqlCommand command = new SqlCommand(queryString, myADONETConnection);
            command.Parameters.AddWithValue("@field1",Col.ToString());

command.ExecuteReader();
myADONETConnection.Close();
        }            
    }
  }
       

Step 9:
Bring Execute SQL Task inside Foreach Loop and Configure as shown below. The Code is provided right after snapshot that you need to paste in SQLStatement


SQLStatment Code :

--======================================
--INSERT RECORD IN ALL THE TABLES
--======================================
--Insert into dbo.package
DECLARE @pkgID INT

INSERT INTO dbo.package
            (PackageName,
             PackagePath,
             PackageFormatVersion,
             CreatorName,
             CreationDate,
             VersionMajor,
             VersionMinore,
             CreatorComputerName,
             ProtectionLevel,
             EnableConfig)
SELECT ObjectName AS PackageName,
       ?,
       PackageFormatVersion,
       CreatorName,
       CreationDate,
       VersionMajor,
       VersionMinore,
       CreatorComputerName,
       ProtectionLevel,
       EnableConfig
FROM   (SELECT --Props.Prop.query('.') as PropXml
       Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)', 'nvarchar(max)')  AS PropName,
       Props.Prop.value('.', 'nvarchar(max)') AS PropValue
        FROM   (SELECT Cast(COL AS XML) AS pkgXML
                FROM   dbo.Test) t
               CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Property') Props(Prop)) D
       PIVOT (Min(propValue)
             FOR PropName IN (ObjectName,
                              PackageFormatVersion,
                              CreatorName,
                              CreationDate,
                              VersionMajor,
                              VersionMinore,
                              CreatorComputerName,
                              ProtectionLevel,
                              EnableConfig) ) AS PV

SET @PkgID=Scope_identity()

--print @pkgID
-----------------------------------------------------------------------------------------------------
--Connection Managers
IF Object_id('tempdb..#T') IS NOT NULL
  BEGIN
      DROP TABLE #T
  END

IF Object_id('tempdb..#TG') IS NOT NULL
  BEGIN
      DROP TABLE #TG
  END

SELECT Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)', 'nvarchar(max)')  AS PropName,
       Props.Prop.value('.', 'nvarchar(max)') AS PropValue
INTO   #T
FROM   (SELECT Cast(COL AS XML) AS pkgXML
        FROM   dbo.Test) t
       CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:ConnectionManager/DTS:Property') Props(Prop)

CREATE TABLE #TG
  (
     PropName  VARCHAR(300),
     propValue VARCHAR(300),
     id        INT
  )

DECLARE @propName VARCHAR(300)
DECLARE @propValue VARCHAR(300)
DECLARE db_cursor CURSOR FOR
  SELECT PropName,
         PropValue
  FROM   #T
DECLARE @id INT=0
DECLARE @cnt INT=0

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @propName, @propValue

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO #TG
      VALUES     (@propName,
                  @propValue,
                  @id)

      FETCH NEXT FROM db_cursor INTO @propName, @propValue

      SET @cnt=@cnt + 1

      IF ( @cnt%5 = 0 )
        BEGIN
            SET @id=@id + 1
        END
  END

INSERT INTO dbo.PkgConnectionManager
SELECT ObjectName,
       DelayValidation,
       DTSID,
       Description,
       CreationName,
       @PkgID
FROM   (SELECT *
        FROM   #TG)d
       PIVOT (Min(propValue)
             FOR PropName IN (ObjectName,
                              DelayValidation,
                              DTSID,
                              Description,
                              CreationName) ) AS PV

CLOSE db_cursor

DEALLOCATE db_cursor

-----------------------------------------------------------------------------
--Insert Tasks Information
INSERT INTO dbo.PkgTransformation
SELECT TaskName,
       TaskType,
       @pkgID
FROM   (SELECT Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            ./p1:Property[@p1:Name=''ObjectName''][1]', 'nvarchar(max)') AS TaskName,
               Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            ./@p1:ExecutableType', 'nvarchar(max)')                      AS TaskType
        FROM   (SELECT Cast(COL AS XML) AS pkgXML
                FROM   dbo.Test) t
               CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                            //DTS:Executable[@DTS:ExecutableType!=''STOCK:SEQUENCE''
                        and    @DTS:ExecutableType!=''STOCK:FORLOOP''
                        and    @DTS:ExecutableType!=''STOCK:FOREACHLOOP''
                        and not(contains(@DTS:ExecutableType,''.Package.''))]') Pkg(props)) D

------------------------------------------------------------------------------------------------
--Insert into dbo.PkgVariable
IF Object_id('tempdb..#T1') IS NOT NULL
  BEGIN
      DROP TABLE #T1
  END

IF Object_id('tempdb..#TG1') IS NOT NULL
  BEGIN
      DROP TABLE #TG1
  END

SELECT Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)', 'nvarchar(max)')  AS PropName,
       Props.Prop.value('.', 'nvarchar(max)') AS PropValue
INTO   #T1
FROM   (SELECT Cast(COL AS XML) AS pkgXML
        FROM   dbo.Test) t
       CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Variable/DTS:Property') Props(Prop)

CREATE TABLE #TG1
  (
     PropName  NVARCHAR(MAX),
     propValue NVARCHAR(MAX),
     id        INT
  )

DECLARE db_cursor CURSOR FOR
  SELECT PropName,
         PropValue
  FROM   #T1

SET @id =0
SET @cnt=0

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @propName, @propValue

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO #TG1
      VALUES     (@propName,
                  @propValue,
                  @id)

      FETCH NEXT FROM db_cursor INTO @propName, @propValue

      SET @cnt=@cnt + 1

      IF ( @cnt%10 = 0 )
        BEGIN
            SET @id=@id + 1
        END
  END

INSERT INTO dbo.PkgVariable
SELECT ObjectName,
       Expression,
       EvaluateAsExpression,
       Namespace,
       ReadOnly,
       RaiseChangedEvent,
       IncludeInDebugDump,
       DTSID,
       Description,
       CreationName,
       @pkgID
FROM   (SELECT *
        FROM   #TG1)d
       PIVOT (Min(propValue)
             FOR PropName IN (Expression,
                              EvaluateAsExpression,
                              Namespace,
                              ReadOnly,
                              RaiseChangedEvent,
                              IncludeInDebugDump,
                              ObjectName,
                              DTSID,
                              Description,
                              CreationName) ) AS PV

--SELECT * From ##TP
CLOSE db_cursor

DEALLOCATE db_cursor

---------------------------------------------------------------------------------------------
--Insert configuration
IF Object_id('tempdb..#TC') IS NOT NULL
  BEGIN
      DROP TABLE #TC
  END

IF Object_id('tempdb..#TCG') IS NOT NULL
  BEGIN
      DROP TABLE #TCG
  END

SELECT Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)', 'nvarchar(max)')  AS PropName,
       Props.Prop.value('.', 'nvarchar(max)') AS PropValue
INTO   #TC
FROM   (SELECT Cast(COL AS XML) AS pkgXML
        FROM   dbo.Test) t
       CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Configuration/DTS:Property') Props(Prop)

CREATE TABLE #TCG
  (
     PropName  NVARCHAR(MAX),
     propValue NVARCHAR(MAX),
     id        INT
  )

DECLARE db_cursor CURSOR FOR
  SELECT PropName,
         PropValue
  FROM   #TC

SET @id =0
SET @cnt=0

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @propName, @propValue

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO #TCG
      VALUES     (@propName,
                  @propValue,
                  @id)

      FETCH NEXT FROM db_cursor INTO @propName, @propValue

      SET @cnt=@cnt + 1

      IF ( @cnt%7 = 0 )
        BEGIN
            SET @id=@id + 1
        END
  END

INSERT INTO dbo.PkgConfiguration
SELECT ObjectName,
       ConfigurationType,
       ConfigurationString,
       ConfigurationVariable,
       DTSID,
       Description,
       CreationName,
       @pkgID
FROM   (SELECT *
        FROM   #TCG)d
       PIVOT (Min(propValue)
             FOR PropName IN (ConfigurationType,
                              ConfigurationString,
                              ConfigurationVariable,
                              ObjectName,
                              DTSID,
                              Description,
                              CreationName) ) AS PV

CLOSE db_cursor

DEALLOCATE db_cursor

--------------------------------------------------------------------------------------------------------------------------
--Insert EventHandler
IF Object_id('tempdb..#TE') IS NOT NULL
  BEGIN
      DROP TABLE #TE
  END

IF Object_id('tempdb..#TEG') IS NOT NULL
  BEGIN
      DROP TABLE #TEG
  END

SELECT Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)', 'nvarchar(max)')  AS PropName,
       Props.Prop.value('.', 'nvarchar(max)') AS PropValue
INTO   #TE
FROM   (SELECT Cast(COL AS XML) AS pkgXML
        FROM   dbo.Test) t
       CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:EventHandler/DTS:Property') Props(Prop)

CREATE TABLE #TEG
  (
     PropName  NVARCHAR(MAX),
     propValue NVARCHAR(MAX),
     id        INT
  )

DECLARE db_cursor CURSOR FOR
  SELECT PropName,
         PropValue
  FROM   #TE

SET @id =0
SET @cnt =0

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @propName, @propValue

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO #TEG
      VALUES     (@propName,
                  @propValue,
                  @id)

      FETCH NEXT FROM db_cursor INTO @propName, @propValue

      SET @cnt=@cnt + 1

      IF ( @cnt%16 = 0 )
        BEGIN
            SET @id=@id + 1
        END
  END

INSERT INTO dbo.PkgEventHandler
SELECT EventName,
       ForceExecValue,
       ExecValue,
       ForceExecutionResult,
       Disabled,
       FailPackageOnFailure,
       FailParentOnFailure,
       MaxErrorCount,
       ISOLevel,
       LocaleID,
       TransactionOption,
       DelayValidation,
       DTSID,
       Description,
       CreationName,
       @pkgID
FROM   (SELECT *
        FROM   #TEG)d
       PIVOT (Min(propValue)
             FOR PropName IN (EventName,
                              ForceExecValue,
                              ExecValue,
                              ForceExecutionResult,
                              Disabled,
                              FailPackageOnFailure,
                              FailParentOnFailure,
                              MaxErrorCount,
                              ISOLevel,
                              LocaleID,
                              TransactionOption,
                              DelayValidation,
                              DTSID,
                              Description,
                              CreationName) ) AS PV

CLOSE db_cursor

DEALLOCATE db_cursor

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Insert LogProvid
IF Object_id('tempdb..#TL') IS NOT NULL
  BEGIN
      DROP TABLE #TL
  END

IF Object_id('tempdb..#TLG') IS NOT NULL
  BEGIN
      DROP TABLE #TLG
  END

SELECT Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)', 'nvarchar(max)')  AS PropName,
       Props.Prop.value('.', 'nvarchar(max)') AS PropValue
INTO   #TL
FROM   (SELECT Cast(COL AS XML) AS pkgXML
        FROM   dbo.Test) t
       CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:LogProvider/DTS:Property') Props(Prop)

CREATE TABLE #TLG
  (
     PropName  NVARCHAR(MAX),
     propValue NVARCHAR(MAX),
     id        INT
  )

DECLARE db_cursor CURSOR FOR
  SELECT PropName,
         PropValue
  FROM   #TL

SET @id =0
SET @cnt =0

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @propName, @propValue

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO #TLG
      VALUES     (@propName,
                  @propValue,
                  @id)

      FETCH NEXT FROM db_cursor INTO @propName, @propValue

      SET @cnt=@cnt + 1

      IF ( @cnt%6 = 0 )
        BEGIN
            SET @id=@id + 1
        END
  END

INSERT INTO dbo.PkgLogProvider
SELECT ObjectName,
       ConfigString,
       DelayValidation,
       DTSID,
       Description,
       CreationName,
       @pkgID
FROM   (SELECT *
        FROM   #TLG)d
       PIVOT (Min(propValue)
             FOR PropName IN (ConfigString,
                              DelayValidation,
                              ObjectName,
                              DTSID,
                              Description,
                              CreationName) ) AS PV

CLOSE db_cursor

DEALLOCATE db_cursor

Step 10: 
 You are done with package creation. The Package should look like below snapshot

Final Output:
Run your package, Package is going to read the SSIS Package Files(.dtsx) from given folder in VarFilePath and then information for each of the package is going to be loaded to related table. The Package name will be loaded into dbo.Package. PkgID is used in all other tables for relationship. I have done a quick test and it works(SSIS 2008,SSIS 2008 R2). There could be things I might missed;)

Here are select queries for tables. You can join on PkgID all these tables

SELECT * FROM Sandbox.dbo.package
SELECT * FROM Sandbox.dbo.PkgConnectionManager
SELECT * FROM Sandbox.dbo.PkgTransformation
SELECT * FROM Sandbox.dbo.pkgVariable
SELECT * From Sandbox.dbo.PkgConfiguration
SELECT * FROM Sandbox.dbo.PkgEventHandler
SELECT * FROM Sandbox.dbo.PkgLogProvider