SSIS : ExecuteSQLTask and C# troubles (Part III)

This is the third post (Part I, Part II) in about programming SSIS with C#. It was been a while that i pulled out my hair. Yesterday and today were one of those days. The following post will show you why. I was trying to generate a Execute SQL Task in SSIS from C# and i couldn’t get it work right. The code shown below was the starting point:

DTS.Executable ExeForSQLTask = pkg.Executables.Add("STOCK:SQLTask");
DTS.TaskHost TKHSQLHost = (DTS.TaskHost) ExeForSQLTask;
TKHSQLHost.Name = "This is a programmed SQL Task";

ExecuteSQLTask MySQLTask = (ExecuteSQLTask)TKHSQLHost.InnerObject;


The last step is optional (i call this design time validation) . This uses the ExecuteSQLStep object for adopting the properties and methods of the Execute SQL task. It’s also possible to do something like this:

taskHost.Properties["SqlStatementSource"].SetValue(taskHost, "SELECT * FROM sysobjects");  
 
This is what i would define as "runtime validation" of the properties. So the runtime validation method was my plan B but i didn’t want to give up that soon. I finally managed to get some answers.

First, Someone at the MSDN SSIS forum came up with the idea that i had to add a reference to the right dll for the ExecuteSQLTask. So i did.

Also i had to add the following using command. "using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;" Still i wasn't finished.After this correction the following error occured:

Unable to cast COM object of type 'System.__ComObject' to class type 'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Finally James Beresford on MSDN forums came up with the answer i was looking for. I was using the ‘wrong’ .NET framework (4 instead of 3.5)! So i changed the .NET framework to 3.5 and it worked great! There were some warnings about System.Csharp reference. I removed it (fingers crossed) and the warnings disappeared. 

Hennie

SSIS : Must declare the table variable "@P1" problem

Hi,

In this post i descibe a real world example which i encountered during building SSIS packages. Initially, i built a stored procedure "uspGetRowCount" that calculates and returned the number of record in a table. This is calculated first, a data transfer takes place and then the records in table is calculated again. This works great when all of your databases resides on the same server but in most of the real world cases, databases will not placed on the same server. So i decided to replace the stored procedure with a simple SQL statement: SELECT COUNT(*) FROM  ? and replace ? with a parameter. But this gives an error:


[Execute SQL Task] Error: Executing the query "SELECT COUNT(*) FROM  ?" failed
 with the following error: "Must declare the table variable "@P1".". Possible failure reasons:
 Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.


So best practice in such cases is to create a variable. In my case: varSQLGetRowcount and give it the following value : "SELECT COUNT(*) FROM " + @[User::varDestinationObjectName]. Assign this variable to the Execute SQL task like this :

A small tip about SSIS but hopefully helpful.

Gr,
Hennie

SSIS : Adding a connectionmanager in C# (Part II)

In my former post about generating a package with C#, i discovered the various properties of a simple SSIS package. In this post i’ll add a connection to a package with C#. The following code is needed for this (don't forget adding this on top of script: using DTS = Microsoft.SqlServer.Dts.Runtime;):

// Create an application
DTS.Application app = new DTS.Application();
// Create a package
DTS.Package pkg = new DTS.Package();
//Setting some properties
pkg.Name = "MyProgrammedSQLTaskPAckage";
         
//Adding a connection to the package
DTS.ConnectionManager ConnMgr = pkg.Connections.Add("OLEDB");
ConnMgr.ConnectionString = "Provider=SQLOLEDB.1;" +
    "Integrated Security=SSPI;Initial Catalog=AdventureWorksLT2008R2;" +
    "Data Source=(local);";
ConnMgr.Name = "AdventureWorksLT2008R2";
ConnMgr.Description = "SSIS Connection Manager for OLEDB Source AdventureWorksLT2008R2";
app.SaveToXml(String.Format(@"E:\\SSISProgram\\{0}.dtsx", pkg.Name), pkg, null);
Console.WriteLine("Package  {0} created", pkg.Name);
pkg.Dispose(); 


When i compare the XML schema of the generated SSIS package with the generated SSIS package itself, the following relations can be drawn.

Greetz,
Hennie

SSIS : Create a simple SSIS package in C# (Part I)

In this post an example on how to build a SSIS with a piece of C# code.For building SSIS packages in C# you need to install visual studio 2010 for C# and add the following references to your project (Project->Add Reference):

• Microsoft.SqlServer.DTSPipelineWrap.dll
• Microsoft.SQLServer.DTSRuntimeWrap.dll
• Microsoft.SQLServer.ManagedDTS.dll
• Microsoft.SqlServer.PipelineHost.dll

Now it’s time to write our first C# program generating a SSIS package. In this C# program only the general properties are programmed. The following (simple) properties of SSIS packages can be programmed:

• VersionComments (“Some Version comments”)
• CreatorName(“Hennie de Nooijer”)
• CreatorComputerName(“HenniePC”)
• CreationDate("01/01/2011")
• PackageType(DTS.DTSPackageType.DTSDesigner100)
• ProtectionLevel(DTSProtectionLevel.DontSaveSensitive)
• MaxConcurrentExecutables(5)
• PackagePriorityClass(DTSPriorityClass.AboveNormal)
• VersionMajor(2)
• VersionMinor(3)
• VersionBuild(7)
• VersionGUID(Readonly)
• EnableConfigurations(false)
• CheckpointFileName(<packagename>.chkpnt)
• SaveCheckpoints(true)
• CheckpointUsage(DTSCheckpointUsage.Always)
• SuppressConfigurationWarnings(false
• LastModifiedProductVersion(“”)
• ForceExecValue(false)
• ExecValue(“Test”)
• ForceExecutionResult(DTSForcedExecResult.Completion)
• Disabled(false)
• FailPackageOnFailure(“false”)
• FailParentOnFailure(“true”)
• MaxErrorCount(2)
• ISOLevel(“IsolationLevel.Snapshot”)
• LocaleID(“8201”). English jamaica
• TransactionOption(“DTS.DTSTransactionOption.Supported”)
• DelayValidation(“true”)
• LoggingMode(“DTS.DTSLoggingMode.Disabled”)
• FilterKind. Has something to do with logging
• EventFilter. Has something to do with logging.
• ObjectName("MyProgrammedSSISPackage"). = Name
• DTSID. UnclearDescription(“This is created with SSIS API”)
• CreationName (Readonly).
• DisableEventHandlers (true)

Off course there is more to set but that will shown in future posts on my blogs.  Here is the snippet from my C# code:

// Create an application
DTS.Application app = new DTS.Application();

// Create a package
DTS.Package pkg = new DTS.Package();

//Setting some properties
pkg.VersionComments = "Some Version comments";
pkg.CreatorName = "Hennie de Nooijer";
pkg.CreatorComputerName = "HenniePC";
pkg.CreationDate = DateTime.Parse("01/01/2011");
pkg.PackageType = DTS.DTSPackageType.DTSDesigner100;
pkg.ProtectionLevel = DTS.DTSProtectionLevel.DontSaveSensitive;
pkg.MaxConcurrentExecutables = 5;
pkg.PackagePriorityClass = DTS.DTSPriorityClass.AboveNormal;
pkg.VersionMajor = 2;
pkg.VersionMinor = 3;
pkg.VersionBuild = 7;
//pkg.VersionGUID (is readonly)
pkg.EnableConfigurations = false;
pkg.CheckpointFileName = String.Format(@"E:\\SSISProgram\\{0}.chkpnt", pkg.Name);
pkg.SaveCheckpoints = true;
pkg.CheckpointUsage = DTS.DTSCheckpointUsage.Always;
pkg.SuppressConfigurationWarnings = false;
pkg.ForceExecutionResult = DTS.DTSForcedExecResult.Completion;
pkg.ForceExecutionValue = false;        // without d
pkg.ForcedExecutionValue = "Test";      // with d
pkg.Disable = false;
pkg.FailPackageOnFailure = false;
pkg.FailParentOnFailure = true;
pkg.MaximumErrorCount = 2;
pkg.IsolationLevel = IsolationLevel.Snapshot;
pkg.LocaleID = 8201; //English.Jamaica
pkg.TransactionOption = DTS.DTSTransactionOption.Supported;
pkg.DelayValidation = true;
pkg.LoggingMode = DTS.DTSLoggingMode.Disabled;
pkg.Name = "MyProgrammedSSISPackage";
pkg.Description = "This is created with SSIS API";
pkg.DisableEventHandlers = true;

app.SaveToXml(String.Format(@"E:\\SSISProgram\\{0}.dtsx", pkg.Name), pkg, null);

Console.WriteLine("Package  {0} created", pkg.Name);

pkg.Dispose();

This will create a SSIS package in the SSISprogram folder. When you open this SSIS package the following XML is shown. On the right i’ve shown the properties as available in the generated SSIS package. I’ve drawn red lines to show the relations.

 There are some properties that can't be set with the SSIS API (as far as i know now):

• PackageFormatVersion.
• PackageType.
• VersionGUID.
• CreationName.

The one i'm unsure about is (These seems properties for the logging. I'll write about them in the future):

• Loggingmode
• Filterkind
• EventFilter

ForceExecutionValueType is set automatically by API.


OffLine mode is greyed out in the properties window.

UpdateObjects is a property that will be removed in a futureversion of SSIS. This is what i found on MSDN : This member will be removed in a future version of MicrosoftSQL Server. Avoid using this member in new development work, and plan to modify applications that currently use this member.
Whether i  set this property or not it will  not be shown in the XML schema of the SSIS package.


Gr,
Hennie

Quipu : Groovy templates

In a discussion on the Sourceforge.net website, i learned that Quipu uses the GStringTemplateEngine of Groovy. The GStringTemplateEngine processes template sourcefiles (but in case of Quipu it’s stored in a table)  by substituting variables and expressions into placeholders to produce the desired output using a streaming approach.

The template engine uses JSP style <% %> and <%=%> expression or Gstring style expression. The JSP style/Gstring Style coding reminds me of asp/php- and C coding. On one of the webpages , i found an example. For instance <%= firstname %> is replaced with a instance of a firstname and ${accepted ? ‘accepted’ : ‘rejected’} reminds me of C coding ( if accepted then ‘accepted’ else ‘rejected’).
When you want to edit the templates it's not advisavle doing this directly in the database (like i did initially) but with the ‘back end web GUI’ (http://localhost:8080/qp/). Starting this url will show the following window:

With this window you can browse the different available controllers in Quipu. On the left screen the numbers of controllers and the number of domains are shown. The number of available controllers on the window, is equal to the number of domains (33). Domains are a subset of controllers?




I already inserted a record (808) in the database (before i knew that there was a front end available) : SQL dialect ANSI SQL 92 Custom. Lets take a look into this template by clicking on ‘808’:



And here you can see the template. The next thing we can do is trying to modify the Groovy template by pressing ‘Wijzigen’.



And now it’s possible to edit the template text. I installed notepad++ on my VM and copied the Groovy template in notepad++:



And this template generates the following ANSI SQL code:



Okay, let’s try to change this script into something like this (DROP/CREATE Stored procedure):



At first i was a bit ambitious about creating to many code at once and the following error occurred :



After some debugging of the code, i managed to create a new template with the DROP/CREATE Stored procedure.



So this template creates stored procedures in SQL Server.

Conclusion:

• Debugging the code is a problem. Editing the code in notepad++, copy the code in the Quipu back end, generating the stage (or datavault) and then hoping that it will execute, is a labour full job. If a name of a object is miss spelled, it will still execute and a null appears in the generated code. The coding would speed up, if there is some sort of direct feedback whether the code is correct (intellisense?).
• I think that the names (of the fields of the table) are equal to the objects/properties. Still, it would help if the objectmodel and their properties is described.

Overall, it is great to have the ability to edit the templates and generating all kinds of code. Anything is possible! Mastering Groovy is inevitable to create powerful scripts.

Perhaps it should be possible to write the generated code into a file or a database. At this moment, there is an extra step involved: retrieving the generated code from the qp_sql_step table or copy the generated code in a tool, for instance Integration Services.

Greetz,
Hennie

Crappy SSMS Solution explorer

 
In SQL Server Management Studio (2005, 2008, 2008 R2) it’s possible to create projects and at a first glance it seems great, but after working with the solution explorer in some projects it started to annoy me. Why? Well, I’ll describe this in my post below about this crappy solution explorer. I’ve created in SSMS the following template which i want to use for my projects.



In one of my projects, we want to organize all the SQL server objects in scripts and version them. So I decided to use SSMS solutions. That’s the way because Microsoft has added this functionality to the SSMS? Right?
 
#1 Crappy user interface
The first crappy thing is the creation of a new project. When you create this via SSMS “new project” you are first asked to choose which kind of project. In this case SQL Server Scripts and that seems to be the case for me. There are some options:

• Name. This is the first confusion thing. This is the name of the project and not of the solution.
  
• Solution name. So when you create a new project you are also asked to give the name to a solution. From my viewpoint (and that’s how SSIS works) SSMS should a solution and then you add the files (and perhaps folders). But this is the other way around. You create a project and get a solution for free.
  
• The next confusing thing is the option : “Create directory for solution”. At first you would say “Yes, I want it in a folder” but whether you set this option or not, a directory is created for a solution. Anyway, it seems that it has to do, with creating a project folder with the same name you specified at ‘Name’ e.g. Database in the solution folder. Are you still there? I think it has something to with how you want to organize your project (uhm solution). Do you want to create a solution with only scripts or do you want to organize your solution in projects.

#2 Crappy change connection
The next thing I would to do with the solution explorer is executing a query against different connections. If you click in the query file, there is a property ‘connection’. That options seems that it’s possible to execute queries on different connections. First I create a query:




Right click in the query and in the properties window you see the property connection:



It seems that it’s possible to change connection here. So let’s try to add a connection to the project.




So now there are two connections. Let’s choose one of these connections in the query:




Great! The connection does not appear in this pull down box. Why ? Well let’s try another thing. Change connection in the query window. Choose connection and change connection in the context menu:




 And hey(?) the connection appears now in the solution explorer:



And the property of the query window has changed to this new connection. So what’s point of the storing connections in the solution explorer?
 
#3 Crappy delete/remove of files/projects
The next thing is deleting files and projects in the solution explorer. So I have this situation in the solution explorer:



First let’s remove the file in the project. So right click in the solution explorer and press remove the file :




The next thing is the following window where you have the option to remove or delete. Intuitively this is a bit strange. What’s the difference between remove or delete? After some experimenting you’ll find out. When you press remove it will not physically remove the file.



To me it’s not clear why this behaviour happen when you choose remove. Why do you want this? The solution folder and projectfolders will be wasted with virtually removed files and even worse when you use version software it will fill your repository with old files.
Deleting the project is another thing. The option ‘delete’ (you know the physical delete) is not available here (?). It’s not clear to me why you can’t physical delete a project.



And the following option appears :



But the files/folders are still there?



So again, why the option to virtually remove a project is available, is not clear to me. Choosing this will not clean up the project folder and metafiles. So the solution folder is filling up with wasted projects. This is not a structured way, how to work with solutions/projects. When you choose to remove a project, you have to physical delete the folder too.
 
Conclusion
Currently we are investigating how to sset up a DTAP (Dev, Test, Acc and prd) environment and we wanted to use solutions in SSMS but now we are not sure. I’ve heard that perhaps visual studio for database developers is a better tool for doing this.

Data is an asset of IT?

I’ve read an interesting article on Information Management about Data is an asset.  In this article Jim Ericson states that data is an asset and there is more than just an intrinsic value of data. It can be made explicit. Jim talks about taking the net value of the projects that uses the data and this is the gain you can get of using data.  In my opinion, these thoughts are very IT driven.
In Erik’s assumption he talks about projects done by IT and summarizing the net values of these projects and there you are, you have the value of data. Is this really the case? What about business opportunities what  data can achieve? What if a report is badly designed in case of usability or gives a poor insight?  What if a BI developer created an awesome analytical insight what assists the business in a very helpful manner? This kind of advantages is very implicit. You can’t calculate adding up the net values of projects!

Off course, I've been in the situation when a business user said to me : “Just give me the data and I'll build something in Excel” and yes, I think that there should be ways to enable this. I’ve seen some great work with excel worksheets in one of my former projects. Those were people who know what data means and how to handle this. There's another side to the matter (and that’s when IT should be in sight) that sometimes these solutions were developed over and over again (with some adjustments) for different departments. When standardization, skills or scalability is needed, IT comes into play. These excel implementations can be used for prototyping, but in the end you need to standardize the general parts.

This discussion reminds me of another discussion about power users, casual users and Managed Self service BI. Power users, like business analysts, wants data to explore regarding correlations, insights and opportunities. For casual users standard reports are quite enough to do their job.  So give the power users freedom to develop great visualizations but don’t let them free in doing their excel/access habit. Look over their shoulder and standardize the general parts.

Another thing that is involved here is the ‘one single version of the truth’ principle. Bringing all of the disparate data into a data warehouse, has created the IT backlog in last decade. This generated the Self Service BI movement. Making IT responsible for delivering all of the information would create an IT backlog.

So should data/information be an asset or should data/information be an asset of IT  (from data to information to knowledge to wisdom)? It has some nuances. IT (!) depends - On:

• Maturity of the IT department.
• On the BI/DWH knowledge of the IT department.
• Depends on the strategy of the organisation (and maturity). Do you want to enhance a smarter organisation? What responsibilities should IT have and what should be the responsebility of  the business user (power and casual).
• Kind of data.
• Depends on the ease of understanding of data structure.
• Depends on the data quality. If the data quality is poor, you need some specialized personnel to clean up the mess.
• Data governance. Can you trust your data?
• Master Data Management (sort of a quality check of data).
• Security. Do you want to show all the data to your employees.

So there is no silver bullet for this. There are too much interdependencies between all these subjects. My advice in these cases would be: write a BI strategy document and take all these subjects into account. Define a far sight and create a road map to get there.

Hennie

Quipu : Discovering the metatables.

In my former posts about Quipu i already blogged about the installation process, generated datavault model and one post about installing Quipu meta database on SQL Server. Today i want you to show something about the metatables of Quipu. In the diagrams below only the interesting tables are shown. So don’t blame me that i didn’t show all the tables;-)

Import schema
When i imported the schema SalesLT of the AdventureWorksLT2008R2 database in Quipu ‘something’ happened in the following tables:

Specially the tables with numbers are interesting:

• qp_schema
• qp_index
• qp_identifier
• qp_column
• qp_table
• qp_relationship
• qp_reference

In these tables the  metadata is stored about the datamodel in the source database. All seems very logical but the relation ship between qp_relationship and qp_reference seems an 1:1 but i’m not sure (yet).

Generate stage
The next step what i’ve done is generating the staging database with the option ‘Generate staging’ and when i executed this option the following changes were discovered in the tables op de metadata database:

Some changes appeared in the already inventoried tables in the Quipu metadata tables. Two extra tables are filled with data:

• qp_sql_step
• qp_mapping

In table 'qp_sql_step' i’ve found the generated DDL statements. The 'gp_mapping' table is connected with the columns table and the table 'qp_sql_step'. I’m not sure what role is of this table. The table 'qp_sql_step' contains the generated SQL statements per table. With my limited knowledge (yet), i would think that it should be connected to the table 'qp_table'. May be i find out in the future. Another thing i remarked is that the ETL code is stored in the 'qp_sql_step table' and not the DDL code.

Generating Datavault
The last step i’ve executed is generating the datavault model. When i generated the datavault, the following model is a representation of changed number of records in the tables.

There are no new tables used for filling new records, so this seems it concerning the dynamic metadata of generating the data vault. The following things catched my eyes:

• The SQL code is generated from templates stored in the table 'qp_sql_template'.
• There is no sql code in 'qp_sql_step' table for generating ETL/DDL for the datavault in contrast with the staging scheme. May be the code of the staging area is a mistake? Logically it would be best to generate the SQL code right from the metatables and the templates.

So that’s all folks..

Gr,
Hennie


----------------------------------

Update after comments of The Los Tilos.

The above diagrams have more relationships. In the diagram below relationships are shown better.

Quipu : Installing 1.0.4 with SQL Server

Qosqo developed a new version of Quipu and today i installed it on my VM. I decided to install the metadata in SQL Server so i could look in the tables what was going on in Quipu. Below you can read my findings.

In order to install Quipu in SQL Server you need to set a environmentvariable: QUIPU_CONFIG and give it a proper value: A path to file where connectionstring resides. Accidentally (or on purpose) Qosqo already defined an example for an connection on the following location :.\webapps\quipu-config.properties. So the environmentvariable should be pointed to this location.

In this location there is a file in which the following information is stored (use wordpad):

dataSource.dbCreate=update
dataSource.driverClassName=net.sourceforge.jtds.jdbc.Driver
dataSource.url=jdbc:jtds:sqlserver://localhost:1433/qpdb
dataSource.username=sa
dataSource.password=sa

When all is well, the metatables are created in the SQL Server database qpdb.

Gr
Hennie

Managed Self Service BI

For a while it was on my mind to write somehing about Managed Self Service BI. After presenting this subject, it became clear to me that this subject is a good candidate for a post on my blog. Managed Self Service BI, as i define it, is a framework of doing things right on the fields of organisation, processes and technology. I would like to define organisation as what can an organisation do to organize themself in a manner that it is efficient and effective (when employing Self Service BI), technology is everything technical enabling Self Service BI and processes is the oil between the organisation and technology. Processes (procedures) will enhance the possibilities of technology and organisations. These three fields can be aligned or they are not aligned. Perhaps this is comparable with the CMM levels where CMM level 0 is total chaos and CMM level 5 is that everything is under control. Let's take a look at the diagram below:

 

Organisation

The organisational part is about how to organize your organisation, team or business to aid Self Service BI in the best optimal way. At first you need to have a clear vision on what your goals are regarding Self Service BI. What do want to achieve? Define a roadmap to achieve to meet that goal. It's better to take multiple steps than one giant step.

Self Service BI for everybody in your organisation is a tricky thing. What are the risks if everyone 'does' Self Service BI? If there are risks perhaps it's better to make a distinction between power users and casual users. It's very important defining who are casual users and who will be the power users. Power users are the users who have the responsibility and freedom developing BI solutions.

The next best thing is training your employees about Self Service BI. Understanding the principles about Self Service BI, best practices, technologies to be used, etc are important issues here.

Technology

On the technology side care should be taken for a robust BI infrastructure. The BI infrastructure should support Self Service BI as good as possible. Complex queries or queries from unexperienced users can pull down your BI infratructure. Off course select BI tools that support Self Service BI in the best possible way. To often suppliers claim that they have tooling with Self Service BI support but at the end the tooling is not intuitive and users still need support from IT.

Process

The process focus area  is about organizing the procedures and processes around technology, organisation and people. When people building Self Service BI solutions it's difficult to determine whether the report is trustworthy. So need to make sure that users can rely on datasources that is reliable. I think that you have certify data in a way that users can trust this data. Users can compare figures, counts or whatever of their reports with the certified reports, datasources or report parts (usable in mashups). 

Also think about creating a sandbox where users can play, try, explore with data and the tooling.

Greetz,

Hennie

SSIS : WITH RESULT SETS in Denali

In a blog of Derek Dieter i've read something about executing stored procedures with result sets. As it seems now to me, the WITH RESULT SETS clause of the EXEC statement makes it possible to specify explicitly the result set of a stored procedure (and not change it with casting). At this moment the following websites seems to have some interesting information about this option:

• Microsoft SQL Server forum.
• James Rowland-Jones.
• MSDN.

The EXEC statement is enhanced with the following clauses:

• RESULT SETS UNDEFINED 
• RESULT SETS NONE
• RESULT SETS ( <result_sets_definition> [,...n ] )

And the <result_set_definition> does have the following possibilities specifying the result:

• Datatype.
• Length.
• NOT NULL| NULL 
• Collation.

Although it's possible to do some casting of datatypes of fields, it's only possible when the data fits into this sort of a cast. Below you can see some examples i experimented with.

First let's create a stored procedure:

CREATE PROCEDURE spGetPerson
AS
BEGIN
    SELECT FirstName, MiddleName, LastName FROM Person.Person
END 

and execute it :

EXEC spGetPerson
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameMiddle nVarchar(50),
        NameLast NVarchar(50)
    )
);


Execute this will give an error:

EXEC spGetPerson
WITH RESULT SETS NONE
GO

Msg 11535, Level 16, State 1, Procedure spGetPerson, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.


And executing this is ok :


EXEC spGetPerson
WITH RESULT SETS UNDEFINED
GO

You can also use this for executing this :

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameMiddle nVarchar(50) NOT NULL,
        NameLast NVarchar(50)
    )
);


And this will give an error:

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameLast NVarchar(50)
    )
);

Msg 11537, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.


And  the following script shows all the possibilities you have with RESULT SETS (except multiple Result sets):

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50) NOT NULL,
        NameMiddle nVarchar(50) NULL,
        NameLast NVarchar(50) COLLATE Latin1_General_100_CI_AS
    )
);


And the final example shows multiple result sets:

--Suppose you have two resultsets:
CREATE PROC Production.ProductList @ProdName nvarchar(50)
AS
-- First result set
SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE Name LIKE @ProdName;
-- Second result set
SELECT Name, COUNT(S.ProductID) AS NumberOfOrders
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS S
        ON P.ProductID  = S.ProductID
    WHERE Name LIKE @ProdName
    GROUP BY Name;
GO

-- Execute the procedure
EXEC Production.ProductList '%tire%'
WITH RESULT SETS
(
    (ProductID int,   -- first result set definition starts here
    Name Name,
    ListPrice money)
    ,                 -- comma separates result set definitions
    (Name Name,       -- second result set definition starts here
    NumberOfOrders int)
);


Conclusion:
It sounded as a promising feature to me but i'm a bit disappointed. There are only four options available and i realize that it has some potential. But selecting some columns is not possible, and many more things.

Greetz,
Hennie

SSIS : DTEXEC CHECKPOINITNG ON/OFF

When a job with a SSIS package execution is scripted i've noticed that the DTEXEC commandline option is set to /CHECKPOINTING OFF. In my packages i've built checkpoints so this does alarms me a bit. I decided to find out more about this.

In BOL the following fragment of text can be found: "Optional). Sets a value that determines whether the package will use checkpoints during package execution. The value on specifies that a failed package is to be rerun. When the failed package is rerun, the run-time engine uses the checkpoint file to restart the package from the point of failure.

The default value is on if the option is declared without a value. Package execution will fail if the value is set to on and the checkpoint file cannot be found. If this option is not specified, the value set in the package is retained. For more information, see Restarting Failed Packages by Using Checkpoints.

The /CheckPointing on option of dtexec is equivalent to setting the SaveCheckpoints property of the package to True, and the CheckpointUsage property to Always."

This phrase only describes the ON option. The OFF option is not described anyway!

Scenario 1 
In this scenario lets try the option /CHECKPOINTING OFF when the checkpoints are enabled in the SSIS package. In the snippet below you can see that i've set the checkpoint properties:

I opened a DOS window and i executed the following command: DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING OFF and below you can see the result:

and the checkpoint file is created:

Conclusion: the option CHECKPOINTING OFF doesn't turn off checkpointing (?!), strange and not very intuitive.

Scenario 2
In the next scenario i tried the following setting:

I open a DOS window and i executed the following command: DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING OFF and below you can see the result:

and no checkpointfiles are created.

Conclusion: as i expected no checkpointfile is created.

Scenario 3
In this scenario let's try the CHECKPOINTING ON option. As in the former scenario the properties for the checkpoints stays the same:

 

and executing  DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING ON in the DOS windows shows this:

And the checkpointfile is created:

Conclusion: the option CHECKPOINTING ON overrrides the properties 'CheckpointUsage' and 'SaveCheckpoints' in the SSIS packages.

Scenario 4
In this scenario i left property CheckpointFileName blank. Something like this :

and executing  DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING ON in the DOS windows shows this:

and offcourse no checkpoint file exists in the folder and that means that it is a goed a idea of specifying the checkpointfile in commandline options of DTEXEC: DTEXEC /FILE CheckpointDTEXEC.dtsx /CHECKPOINTING ON /Checkfile E:\tmp\NowviaCommandline.chkpnt and the properties stays the same. The output of the commandline executions shows this :

 and the checkpoinfile is created:

Conclusion
The option CHECKPOINTING OFF doesn't turn of the checkpointing in the SSIS package, but CHECKPOINTINGON does turn on checkpointing!

Greetz,
Hennie

SSIS : Setting a database connection with parameters in Denali

Parameters is a new feature in Denali and they are a replacement of package configurations. Package configurations will stay in Denali but now there is another approach available. They are a fundamental new way of how SSIS will handle, manage and execute packages  inside the catalog.  They look al lot like variables but they are slightly different. There are two types of variables: Project- and package parameters. For this blog w'll use the projectparameters.

1. First w'll create a demopackage in SSIS. In this package i've dragged an Execute SQL Task on the control flow and named it something like this:

 I created an connection and added the following SQL code at the Execute SQL task.

CREATE TABLE YES(
    [YES] [int]
) ON [PRIMARY]

And i added an project parameter to the project:

Assigned the project parameter to the connection in the expression builder:

2. I created four databases in SSMS (for this demo we won't be using them all, just Dev and Test):

3. Now let's deploy the package at the Catalog. First build it and then Deploy this project. Below you can see that the parameter is deployed together with the project.

 

 4. Okay now lets run SSMS. and go the catalog and now the project/solution and the package is deployed in the catalog:

 5. Add the variable (now its called a variable) parCONN to the environment properties of the Dev environment (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive)
 

 
 6.  Add the variable (now its called a variable) parConn to the environment properties of the Test environment (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive).

 7. Add references to the project properties:

 8.  The next step is to set the parameter value with the environment variable that is created earlier (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive)

9. We are are now ready to test this package. So when i run this package against the Dev environment the table should be created in the Dev environment. Let's check this first:

And the results are shown below:

And the table is created in the Dev database:

10. The next step is to check out whether the package created the table 'YES' in the Test database. Lets found out.

Running the package will show the following details:

 

And here are the results. The table is created in the Test database!!

Conclusion:
So this is an example of setting a connection with parameters and enviroments. We have created an projectparameter in the package, created environments, pointed the projects to the enviroments and finally i ran the packages against two environments.

Greetz,
Hennie