SSIS Error -- Exception deserializing the package "Access to the path ...... denied'

I was trying to build an SSIS package that one of my colleagues has created.

The first error that was thrown was  as below:
Exception deserializing the package "Access to the path 'C:\BACKup\Test\Test\bin\Development\Tcopy.ispac' is denied.".

Here are the steps that I have followed to resolve this.

Step 1  Check whether the package user has access to the path of the solution.
Step 2. Close the solution.
Step 3  Open Task Manager and end any process of DTS.  as shown below.

Step 4  Delete the .ispac file that is created in the bin/Development folder
Step 5  Reopen the solution and rebuild the package.

Hope these steps will help you resolve the build issues 

SSIS Free Expo Event

Here is another Free Expo Event from SSWUG that covers Basic and Complex SSIS Features. The event is scheduled for February 18th 2011 from 9 am to 1 pm PST.

Sessions will cover the following topics:

• SSIS Sorting and Package Protection
• SSIS Package Checkpoints and Transactions
• SSIS Native Logging Features
• Best Practices with SSIS Package Design
• Deploying, Scheduling and Administering SSIS in Production
• Real-world Business Scenarios with SSIS

Speakers include the Knights of Pragmatic Works, a very good reason to attend :)
Register now

SSIS 2008: Tips and Tricks video by Steve Swartz

Today I listened to this very good informative video by Steve Swartz which I wanted to share with you all.

This is a presentation by Steve Swartz in the Microsoft TechEd Europe 2010. In this presentation Steve says that he desires to teach you how to fish rather than give the fish itself.

Thanks to Steve for posting this.

Using Checkpoints in SSIS By Brian Knight

Here is the link of today SQL Share / Jumpstart TV video. Prior to viewing this video I had no knowledge of what checkpoints are.... here are my learnings out of this video.
What are checkpoints?

Checkpoints are setup to ensure that you can run the package from the failure point. These checkpoints are particularly useful when packages take a very long time to run.

When the package fails -- the problem can be fixed and the package is restarted. When checkpoints are used when the package is restarted the package restarts from the point where the package has failed. This will save alot of time for the DBAs and Developers.

Here is the process for creating simple check points by Brian

Step 1 Configuring the packageIn the package properties pane give a name to the property -- checkpointfilename The next property to be set is the CheckPointUsage -- Choose the If Exists option.The next property to be set is the SaveCheckPoints -- set it True
Step 2 Task propertiesChoose the task in the control flow and under the Execution group of the task properties falipackageonfailure -- TrueSet this property to True for all the tasks in the package.
This is the process of creating the checkpoints in the control flow layer in SSIS packages.

SSIS Expression Tester

The expression tester tool is a standalone application that allows you to easily develop and test expressions. It is like a workbench to quickly try things without having to open a package or mess up the one your currently working on. It is designed to be easily portable, just extract the files and run, but it does require SQL Server Integration Services to be installed on the machine. The download contains both SQL Server 2005 and SQL Server 2008 versions of the tool and control.

You can download this tool at the following link


SSIS Expression Tester

SSIS session in Auckland by Patrick

We had a really interesting session with Patrick of Pragmatic Works yesterday on the topic "Introduction to SSIS".

He has covered the following basic aspects of SSIS

• Opening BIDS environment with a -NOSPLASH
• Creating a small package with some package requirements -- Control flow, Data flow, Event Handler, package explorer
• Deplying the created package
• Scheduling a package

Here are my takeways from this session

Tip 1

You can use -NOSPLASH in the shortcut of the Visual studio icon to avoid the initial start up screen when you open Visual Studio

Tip 2

Data Connections can be created in two ways --

• Via the Data Source on the right hand side of the screen of the solution. If the data source is created this way, it can be used by all the packages in the project.
• Via the connection manager within the package itself. If this is used, the connections can be used only within that package.

Tip 3

Data Flow Taks can be created in two ways

• Via the Control flow tab -- by dragging the Data flow task from the toolbox
• Via the Data Flow Tab -- by just clikcing on the link available in this tab.
  

Tip 4

Always use OLEDB source instead of ADO.NET source for greater performance

Tip 5

In the OLEDB source tool always use SQL command instead of Table/view. Using the Table/View can slow down the SSIS package considerably.

Tip 6

When sql command in used, use the Preview button instead of the Parse Query button

Tip 7

You can use look up task and derived column task to redirect the un match rows to an error output.

Tip 8

If you use the row count task in the data flow tab that creates an unnamed variable, to name this varaible right click on the grey space on the control flow tab and name the variable.

Tip 9

You can right click on the control flow tab and click on the Package Configurations to configure the package. These configurations can be created as an XML file which can then be modified at deployment.

Tip 10

When you build a project and deploy, the package will be in the MSDB database of the sql server.

Tip 11

You can schedule an SSIS package using the SQL SERVER Agent or using dt exec command line.

Recap from SQL Saturday Auckland event -- 10th April 2010

This past weekend I have attended the SQL Saturday event in Auckland.

The turnout for this event was really good with close to 100 people attending including the organizers and presenters. Thanks to everyone that helped out to put on a very successful event.

Here is a list of the presenters and the topics presented. We had to cancel the main organizer’s (Dave Dustin) due to lack of time.

Session Title	My learning’s in brief
Analysis Services - Introduction to Business Intelligence with SQL 2008 Justin King	The demonstration was really good as he took us through the entire cycle of creating an SSAS project. Also covered the basics of terminology in his presentation.
What is LINQ – and what the heck is it doing to my database? Ivan Towlson	Didn’t know what LINQ was before and now I know what it is and what advantages we can have with it.
Database Index investigation with DMVs Leo Miller	Learnt a whole heap of stuff on DMVs from DMV concepts, DMV groups to how to use them. Very valuable session.
SQL Server Integration Services 2008 Ben Gracewood	Learnt about SSIS custom templates which was new to me even though I had worked on SSIS and didn’t know anything about them.
36 to 6 SQL Servers – Consolidation from the Trenches Peter Ward	Peter is a very good presenter and I am impressed with his presentation that analogy he gives are realy amazing. Learnt what consolidation is and why we need to consolidation.
Common Misconceptions about Clustering Leo Miller	Why clustering is done and what the actual misconceptions.
Data warehousing & BI on SQL Server 2008 Douglas Barrett	Learnt what’s new in SQL SERVER 2008 R2 in the data warehousing world.
Comparing SQL in large vs small environments James Stewart	Learnt about the various performance tools and monitoring models. Also the various types of alerts.
Training for SQL Server 2008 certification, and why it's worth it Amanda Jackson	I am planning on to do the Business Intelligence certification which was very clear in Amanda’s presentation. Also information about what is available in Microsoft Elearning portal.

The lunch was Pizzas and subway sandwiches which was delicious. We also had an afternoon tea of Giapo Gelato Ice cream.

During every session there were various prizes for the people who were asking questions and also at the end there were some prizes from the sponsors.

I will try to blog on each presentation in detail a bit later.

Checksum Vs. Hashbytes

What is Checksum?
Checksum is a function available in sql server 2005 and sql server 2008. It is intended to build a hash index based on an expression or column list.

Determining if two rows or expressions are equal can be a difficult and resource intensive process. This can be the case where the update was conditional based on all of the columns being equal or not for a specific row. Without checksums it is a long process of using innerjoins to identify the exact row to update in the update statements.

When is Checksum used?
When you need to compare the unique characteristics of an expression, columns or a table consider using the Checksum function.

When you have to update a row where many columns are compared to determine if the data is unique, use CHECKSUM function to build a unique value using checksum function and then compare the CHECKSUM values.



Example:

Hashbytes

What is hashbytes function?
HashBytes function was introduced in SQL server 2005 to simplify creating hashes in the database. It can convert values to MD2, MD4, MD5, SHA, or SHA1 formats.

When can you use a hash function?
Hash functions are a useful option to improve the efficiency of particular queries on large volumes of data. For example, on long strings of text, you can build a hash-index to perform efficient lookups or to speed up aggregate operations.


Differences between checksum and Hashbytes

Checksum	Hashbytes
Faster but can produce lot of duplicate values	Slower but efficient than checksum
Returns an int value	Returns a varbinary(8000)
Microsoft does NOT recommend using CHECKSUM for change detection purposes	Use Hashbytes for change detection purposes