Create MSDB directory for storing SSIS packages from multiple instances (located in the msdb database of each of the instance):

Go to C:\Program Files\Microsoft SQL Server\100\DTS\Binn\ folder (SQL Server 2008) and edit the MsDtsSrvr.ini.xml file:

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB INSTANCE_1</Name>

      <ServerName>SERVER_NAME\INSTANCE_1</ServerName>

    </Folder>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB INSTANCE_2</Name>

      <ServerName>SERVER_NAME\INSTANCE_2</ServerName>

    </Folder>

     <Folder xsi:type="FileSystemFolder">

      <Name>INSRANCE_1 File System</Name>

      <StorePath> C:\FOLDER_SSIS\INSTANCE_1</StorePath>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>INSRANCE_2 File System</Name>

      <StorePath>C:\FOLDER_SSIS\INSTANCE_2</StorePath>

    </Folder>

  </TopLevelFolders> 

</DtsServiceConfiguration>

After modifying the file according to your needs (instance name, path to locally stored package), restart the SQL Server Integration Services service.

If after the restart, the directory structure is not visible go the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ServiceConfigFile. This key specifies the location and name for the configuration file that Integration Services service uses.  If value is empty then add in:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

You can also update the value of the registry key to use a different name and location than the default one for the configuration file.

Any changes to the Registry entry require a restart of the Integration Services service in order to reload the configuration file.

If after this new restart the directory structure is still not visible you must check the event viewer for any error messages. Usually the configurations file (MsDtsSrvr.ini.xml) has typing errors:

?Microsoft SSIS Service configuration file is incorrect.

Error reading config file: There is an error in XML document (1, 5).

 Loading server with default settings.?  

Check the configuration file and correct any possible typing errors, then restart the SQL Server Integration Services service.

For more details regarding the Integration Services Service configuration you can access the following link:

http://msdn.microsoft.com/en-us/library/ms137789(v=sql.105).aspx

When trying to execute a stored procedure that load a CLR assembly, the following error may appear:

EXEC sp_my_procedure

 Msg 10314, Level 16, State 11, Line 1

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65543. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly 'assembly_name, Version=1.0.0.0, Culture=neutral, PublicKeyToken=NULL' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException:

   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   at System.Reflection.Assembly.Load(String assemblyString)

 Solution:

Use the sp_changedbowner stored procedure to change the database owner to sa:

USE DATABASE_NAME
GO
EXEC sp_changedbowner 'sa'

OR:

Then, you need to alter the database by setting the TRUSTWORTHY database property to ON:

ALTER DATABASE DATABASE_NAME
SET TRUSTWORTHY ON;
GO

For more information regarding the TRUSTWORTHY Database Property you can access this link:

http://technet.microsoft.com/en-us/library/ms187861.aspx

Problem

When executing an SSIS package within a SQL Server job the following error can appear:

Source: Exec ?TASK_NAME? Description: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. 

Solution

Open the package in BIDS and go to the properties window of the corresponding task:

To solve this you must change the CommandTimeOut to 0 (for example). The default value is 30.

Problem

When trying to import a SQL Server 2000 DTS package to SQL Server 2008 R2 using SSMS, the following error occurs:

Managing DTS packages in SQL Server Management Studio requires the SQL Server 2000 Data Transformation Services (DTS) runtime. To install the DTS runtime, on your SQL Server 2008 R2 installation media, locate and run the Windows Installer package for SQL Server 2005 Backward Compatibility (SQLServer2005_BC*.msi). You can also download the DTS runtime from the SQL Server Feature Pack page on the Microsoft Download Center. (Microsoft.SqlServer.DtsObjectExplorerUI)

Solution

As suggested, the ?Microsoft SQL Server 2005 Backward compatibility? package must be installed. It can be found on this location:

http://www.microsoft.com/en-us/download/details.aspx?id=3522

Full story »

When trying to install SQL Server 2008R2 SP1 the following error can appear:

Full story »