Wednesday, December 24, 2014

Temporary Stored Procedures in SQL Server 2008

 Today I discovered a small feature of Temporary Stored Procedures in SQL Server.

Temporary Stored Procedures are similar to normal Stored Procedures, but as their name suggests, have a fleeting existence. There are two kinds of temporary Stored Procedures, local and global. Temporary Stored Procedures are created just like any other SP but the name must be prefixed with a hash (#) for a local temporary SP and two hashes (##) for a global temporary Stored Procedure.

A local temporary Stored Procedure is available only in the current session and is dropped when the session is closed or for a different session.

For Example: I have created a temporary Stored Procedure in a query window with session Id: (75).

SQL1.jpg

When I execute the same session then the output is as below:

SQL2.jpg

When I execute a different session the error is shown below:

SQL3.jpg

A global temporary Stored Procedure is visible to all sessions and is dropped when the session of the user that created it is closed. 

For Example: I have created a temporary Stored Procedure in the query window with session Id: (68).

SQL4.jpg


When I execute the same session the output is as shown below:

SQL5.jpg

When I execute a different session the output is as shown below:

SQL6.jpg

If there are any executing versions of the global Stored Procedure when the creator session is closed.

Apart from local and global temporary Stored Procedures, you can also create a regular stored process in the system database tempdb.

You should see this in Management Studio:

SQL7.jpg

This picture displays a Stored Procedures in the tempdb database while the session is active. Notice the long postfix that SQL Server automatically adds to the local temporary Stored Procedure. SQL Server adds some postfixes to local temporary objects to differentiate objects with the same name from different sessions.
 

Saturday, December 13, 2014

SQL Server 2012 SSIS Enhancements

SQL Server 2012 Undo and Redo Features


Parameters can be defined at 2 different levels:
Project. These type of parameters are available to all packages within the SSIS project. Think about them as global parameters. They come handy as it makes really easy to share a given value, such as the path to a file share or the name of a server, across all packages in a project.
         Package. These type of parameters are meant to affect only the package on which they were defined.
-------------------------------------------------------------

Very often I had difficult a time creating SSIS packages.  Unfortunately, when I did something wrong and wanted to revert the last change, I was unable to do so.  It was very frustrating.   As an example, sometimes I unintentionally deleted a task, which I wanted to get back. At times, I had to revert back to the SSIS package version in the VSTF version store. This was all because we did not have any way to undo or redo our changes. Now with SQL Server 2012, we have this feature in the SSIS designer.
Edit Menu SSIS Undo and Redo
As you can see in the image above, you can now use Undo or Redo commands using the menu bar, toolbar or by pressing CTRL+Z for "undo" and by pressing CTRL+Y for "redo".
SSIS Undo Drop Down List
The designer now tracks all the actions that you perform, which you can see in the image above and below.  You have the ability to Undo or Redo as many actions as you want.
SSIS Redo Drop Down List


SQL Server Integration Services Toolbox Enhancements

Now the components inside the toolbox have been placed in few more categories (Favorites and Common) for ease of use.  These categories are not fixed and you can customize it as per your need. For example, you can place all your frequently used components inside "Favorites" section which appears at the top of the Toolbox as shown below:
SSIS Toolbox
To move a component to some other location/category, right click on the component and click on the location where you want that component to be placed as shown below.
SSIS Toolbox move to favorites
On a final note, one really good aspect of the enhanced toolbox is the information section on the bottom of the toolbox.  This area displays details about selected component and provides an option to directly jump to the documentation for that selected component. This is really useful for someone new to SSIS development and/or someone that does not have prior knowledge about the component.

SQL Server Integration Services Solution Explorer Enhancements

There are some noticeable changes in the SSIS Solution Explorer. Deployment of SSIS packages has been a challenge as we had to deploy each SSIS package individually. There was no way to create a deployment package containing all the SSIS packages similar to some of the SSRS and SSAS functionality to deploy all objects. SQL Server 2012 now supports a new project deployment model as well as the legacy deployment model. Discussion about new Project Deployment Model in itself is a big topic, so check out the SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2) and SSIS Package Deployment Model in SQL Server 2012 (Part 2 of 2) tips.  In a nutshell, the Legacy Deployment Model lets you deploy each individual package separately whereas the new Project Deployment Model lets you create deployment packet (which is an *.ispac file) from the SSIS project. Each SSIS project which you create in SQL Server 2012, by default gets created in Project Deployment Model which you can change if needed as shown below:

SSIS Solution Explorer Convert to Legacy Deployment Model
As previously indicated the new SSIS projects get created in the Project Deployment Model. When you right click on the project you will notice two more options. The "Project Parameters" let's you define the parameters in the project and the "Deploy" option let's you create the deployment package or deploy to the Integration Services Catalog database.
SSIS Deployment
If you right click on the package in the Solution Explorer, you will notice one more option (Entry-Point Package). This option is helpful in the scenario when you have a parent package which calls child packages. You can set the Entry-Point Package option for the parent package.
Integration Services Entry-point Package


Friday, December 12, 2014

Certifications for SQL 2008 R2 or SQL Server 2008

No new certifications for SQL 2008 R2

Some of you may be wondering if you should get the SQL Server 2008 certifications or wait for new SQL Server 2008 R2 certifications. There's no point in waiting, because there will not be any new exams for R2. See the blog below for the reasons why. If you want to get a SQL Server certification, go ahead and prepare for the SQL Server 2008 exams.

Bellow are BI Certifications for SQL Server 2008

MCITP: Business Intelligence Developer 2008:
Set of 2 exams:
1.)Exam 70-448- SQL Server 2008, Business Intelligence Development and Maintenance
2.)Exam 70-452- PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008



Suppose, if you complete "MCITP: Business Intelligence Developer 2008" certification exam. If you want to upgrade to MCSE: Business Intelligence 2012 then you have to write only one exam i.e is 70-460.

70-460Transition Your MCITP: Business Intelligence Developer 2008 to MCSE: Business Intelligence



SQL Server 2012 BI Fresh Path Certifications:

The common exams for both tracks will be:
70-461: Querying Microsoft SQL Server 2012
70-462: Administering Microsoft SQL Server 2012 Databases
70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012


The MCITP level exams for Business Intelligence will be:
70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Wednesday, December 3, 2014

What is codename to a software..?

Source for this topic is :

At the 2011 Professional Association for SQL Server (PASS) summit on October 11, Microsoft announced that the next major version of SQL Server (codenamed "Denali"), would be SQL Server 2012.




In software and hardware terminology, a codename is a name given to a project to identify it while it is still in development. Once the project has been completed, just before it is released to the public and retail channels in its final packaged version, the developing company will release its commercial name for the product and the the codename will no longer be used.

Tuesday, December 2, 2014

One Variable for more than one Parameters in ExecuteSQL Task

I have multiple UPDATE statements inside my ExecuteSQL Task. Each one is dependent on one Vairiable e.g. MyId
UPDATE TABLE_A SET COL_A={Something} WHERE ID=?
UPDATE TABLE_B SET COL_B={SomeTHing} WHERE ID=?
This Query takes MyId Variable as Parameter.
Do i need to have as many parameters as my Update Statements are OR there is a way to Have one shared parameter defined inside my ExecuteSQL Task


Answers

Try this:
DECLARE @id int

SELECT @id = ?

UPDATE TABLE_A SET COL_A={Something} WHERE ID=@id
UPDATE TABLE_B SET COL_B={Something} WHERE ID=@id

Monday, December 1, 2014

SSIS: File System Task Move and rename files in one step

In some ETL scenarios, when processing files, it is necessary to rename the already processed files and move them to a different location. In SSIS you can accomplish that in a single step using the File System Task. The example I have prepared assumes the package will process a set of files using a ForEach Loop container; then for each file, using the 'Rename' operation in File System Task will do both; rename and move the file.

Here are some screen shots and notes about the package:

First of all, the big picture. The control flow has a ForEach Loop Container with a File System Task inside. Notice that the DataFlow task is empty and it is intended to show where the real ETL work should go; but this can be different or not required at all.


























Then details about the ForEach Loop container. Basically ,this container is configured to process all *.txt files in C:\Temp\Source folder, where all the files 'to be processed' are expected to be.










































Now the trick, few variables, some of them using expressions:









The expressions are:

in FullSourcePathFileName:
@[User::SourcePath] + @[User::MyFileValue]

in FullArchivePathFileName:
@[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + (DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 4, 1252) Year( @[System::StartTime] )+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

Notice that SourcePath and ArchivePath variables hold only the origin and destination paths of the files.
Note: Make sure you set EvaluateAsExpression property of the variable as TRUE.

Lastly, the File System Task should be configured like this:





I am pretty sure there are different ways of accomplishing this simple task; but I like this one because it does not require writing custom code and relies on expressions.

You can download the sample package from here:

Tuesday, November 25, 2014

How to find particular type of files in Windows OS


/*******************************************Step Starting*****************************************************/


Step1:


open
cmd, then execute bellow commands one by one.

comand1
C:
comand2:
dir *.bak /s >C:satish_serarch_results.txt

comand3:
D:

comand4:

dir *.bak /s >>C:satish_serarch_results.txt

comand5:

E:

comand6:
dir *.bak /s >>C:satish_serarch_results.txt







/*******************************************Step Starting*****************************************************/


Step2:


Create table testimport (header varchar(1000))





BULK

INSERT testimport

FROM 'c:\satish_search_result.txt'




select
* from testimport



/*******************************************Step Starting*****************************************************/


Step3:


select
* from testimport where header is null or header like '%File(s)%' or header like' Volume%' or header like '%Dir(s)%'

delete
from testimport where header is null or header like '%File(s)%' or header like ' Volume%' or header like '%Dir(s)%'








/*******************************************Step Starting*****************************************************/


step4:

alter

table testimport add jaff varchar(400)

alter
table testimport add id int identity(1,1)

update
testimport set jaff= header where substring(header,1,2)=' D'












/*******************************************Step Starting*****************************************************/


step5:
Execute the bellow cursor.




Declare
@header varchar(1000), @jaff varchar(400), @temp_val varchar(500),@id int

DECLARE
cur_emp CURSOR STATIC

FOR
SELECT header,jaff,id from testimport

OPEN
cur_emp

IF
@@CURSOR_ROWS > 0

BEGIN


FETCH
NEXT FROM cur_emp INTO @header,@jaff,@id

WHILE
@@Fetch_status = 0

begin

if

(@jaff is not null)

set
@temp_val=@jaff

else

update

testimport set jaff=@temp_val where header=@header and id=@id

FETCH
NEXT FROM cur_emp INTO @header,@jaff,@id

end

end

CLOSE

cur_emp

DEALLOCATE
cur_emp









/*******************************************Step Starting*****************************************************/


step6:


delete
from testimport where header like ' Directory of%'




















/*******************************************Step Starting*****************************************************/


Step7:

update

testimport set jaff=REPLACE(jaff,' Directory of ','')
























/*******************************************Step Starting*****************************************************/


step8:
Final Query.

select
header,SUBSTRING(header,1,20) as date,

cast
(replace(substring(ltrim(SUBSTRING(header,21,LEN(header))),1,CHARINDEX(' ',ltrim(SUBSTRING(header,21,LEN(header))))),',','') as float)

as
size_In_Byte,

case
when substring(reverse(jaff),1,1)<>'\' then jaff+'\'+ltrim(substring(ltrim(SUBSTRING(header,21,LEN(header))),CHARINDEX(' ',ltrim(SUBSTRING(header,21,LEN(header)))),LEN(header)))

else
jaff+ltrim(substring(ltrim(SUBSTRING(header,21,LEN(header))),CHARINDEX(' ',ltrim(SUBSTRING(header,21,LEN(header)))),LEN(header)))

end
as file_full_path

from
testimport























/*******************************************Step Starting*****************************************************/


Step9:
At last execute the bellow commnads to remove extra columns.

alter
table testimport drop column jaff

alter
table testimport drop column id

Tuesday, November 18, 2014

ssis package - PackageFormatVersion

To know more about PackageFormatVersion pls read bellow post.

http://msbiusefulpostscopynpaste.blogspot.com/2014/10/ssis-how-to-find-version-of-ssis.html



In SQL Server 2005 Integration Services, the PackageFormatVersion property of a .dtsx file is set to 2; in SSIS 2008 and SSIS 2008 R2, the PackageFormatVersion value is to set to 3. In SSIS 2012, the value of the PackageFormatVersion property is 6.

List All SSIS Packages Deployed On Your Integration Server

When deploying packages to SQL Server Integration Services, it’s advisable to set up a folder structure so that you can easily distinguish packages belonging to different projects. Furthermore it may be interesting to create subfolders under the main project folder to separate packages according to the different phases in your ETL (Extract, Transform, Load) process. When loading a data warehouse, interesting folder names are Dimensions for your dimension ETLs and Facts for the packages that load the fact tables.
After a while you end up with lots of packages spread over lots of folders. To get a good view of what is deployed on your server, it may be interesting to find a way to list all the packages. And that’s exactly the reason why I’m writing this article.
The query further down generates a list of all packages deployed in the MSDB database on your SQL Server. What you get is the name of the packages, their location and version-related information. I’ve also created a RootFolder column so that it’s easy to filter on project. (See now why it’s interesting to create separate folders per project?)
It’s important to note that packages deployed to the File System will not be shown in the list. After all, they are not stored in the MSDB database but in a folder somewhere on the server’s hard drive, more precisely in a subfolder of where you’ve installed your SQL Server. In case you’ve forgotten where that was, here’s a small tip. On your server, open up the list of Windows Services (Start > Run > type “services.msc” > enter) and locate the service called SQL Server Integration Services 10.0. Open the properties of that service and have a look at the Path to executable value in the General tab. Take the path, drop the \Binn part and add \Packages instead. That is where, by default, the packages are deployed. (If you’re running SQL Server 2005, apply the same procedure but look for a service called SQL Server Integration Services.)
On my system, this is where the packages are located: D:\Program Files\Microsoft SQL Server\100\DTS\Packages. I will also prove it with following screenshot:
Packages deployed to the file system on SSIS 2008
If you’re looking for a way to list the packages deployed to the file system by using a T-SQL statement, check out the following article by Phil Factor: The TSQL of Text Files.
Okay, time for the real stuff, the query:
/*
    DESCRIPTION: Lists all SSIS packages deployed to the MSDB database.
    WRITTEN BY: Valentino Vranken
    VERSION: 1.1
    COPIED FROM: http://blog.hoegaerden.be

    Note: this query was written for SQL Server 2008. For SQL2005:
        o sysssispackagefolders => sysdtspackagefolders90
        o sysssispackages => sysdtspackages90
*/
with ChildFolders
as
(
    select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
        cast('' as sysname) as RootFolder,
        cast(PARENT.foldername as varchar(max)) as FullPath,
        0 as Lvl
    from msdb.dbo.sysssispackagefolders PARENT
    where PARENT.parentfolderid is null
    UNION ALL
    select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
        case ChildFolders.Lvl
            when 0 then CHILD.foldername
            else ChildFolders.RootFolder
        end as RootFolder,
        cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
            as FullPath,
        ChildFolders.Lvl + 1 as Lvl
    from msdb.dbo.sysssispackagefolders CHILD
        inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
)
select F.RootFolder, F.FullPath, P.name as PackageName,
    P.description as PackageDescription, P.packageformat, P.packagetype,
    P.vermajor, P.verminor, P.verbuild, P.vercomments,
    cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData
from ChildFolders F
    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
order by F.FullPath asc, P.name asc;
The query uses a recursive CTE (Common Table Expression) to get data out of a system table called sysssispackagefolders, located in the MSDB system database. The CTE gives us a list of all folders stored in the database and at the same time uses the hierarchical structure of the table to build the FullPath and the Lvl columns.
Note: the CAST() calls are needed because the data type of the foldername column is sysname. And sysname does not implicitly convert to varchar, which is needed for the concatenation building the FullPath column.
The CTE is joined with another system table called sysssispackages, also located in MSDB. Not all columns are being retrieved from that table but I believe I’ve selected the most important ones. Have a look in the Books Online for more info on the columns available.
There’s one column however on which I’d like to add some additional info myself. That column is called packagedata and it contains the actual SSIS package. The data type of this column is image, not sure why because after all, an SSIS package (or .dtsx file for that matter) is pure XML. So why isn’t it stored as XML? If anyone knows the reason: post a comment!
Update: since I wrote the above paragraph I’ve come across the answer myself. The reason that the XML is not stored as xml datatype is because of the overhead that this would cause. So there you go, use image instead of xml if you’re not going to query the xml structure itself.
Anyway, as you can see in the query, to get it converted from image to XML you need to go through varbinary. The image datatype cannot convert directly to XML. See the Books Online here on what casts are allowed: http://msdn.microsoft.com/en-us/library/ms187928.aspx
Note for SQL Server 2005 users: as mentioned in the query’s comments, these tables don’t exist in SQL Server 2005. Well, actually they do, they just have different names. See the comment in the code for their equivalent.
To finish off I’ll show you what the results look like when executing the query on my test system. But first, following screenshot shows all deployed packages as reported by the Management Studio. As you can see, two packages are deployed to the File System. These two packages were shown earlier in the first screenshot. Some other packages have been deployed to the MSDB database.
Object Explorer showing all deployed SSIS packages
And here are the results of the query:
A list of all SSIS packages deployed to my SQL Server 2008 MSDB database
To be honest, I added a little filter to keep the results clean. The Data Collector, a new feature of SQL Server 2008, also uses some packages so I’ve filtered those out by adding a WHERE clause to the SELECT statement at the bottom of the full query:
select F.RootFolder, F.FullPath, P.name as PackageName,
    P.description as PackageDescription, P.packageformat, P.packagetype,
    P.vermajor, P.verminor, P.verbuild, P.vercomments,
    cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData
from ChildFolders F
    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
where F.RootFolder <> 'Data Collector'
order by F.FullPath asc, P.name asc;


If you’ve been paying attention, you’ve noticed that the two packages deployed to the File System are not mentioned in the output of the query, as expected.
Now that you know how to list your packages, check out my article on deleting them.
That’s all for now folks, have fun!
References
BOL 2008: Tutorial: Creating a Simple ETL Package
BOL 2008: sysssispackagefolders (Transact-SQL)
BOL 2008: sysssispackages (Transact-SQL)
BOL 2008: Recursive Queries Using Common Table Expressions