Unable to execute SSIS packages containing script tasks developed in 32 bit and deployed in 64 bit environments

I started to run into this error in the last month or so. I’ve been developing SSIS 2005 packages on my desktop which is an XP Professional and deploying them to Windows Server 2003 R2 Enterprise x64 edition. All was well until recently, according to Microsoft one of the updates broke compatibility on a dll and caused this issue (More details here in the Microsoft KB 931846). There were a few suggestions such as open the packages on the target server and make an update and save them to force recompile. This is not fun when you have multiple packages and in some instances did not do the trick.

My solution which has worked consistently so far is to restart the Integration Services after you deploy the  updated packages on the target server.

Dynamic SQL in SSIS Data Flow task

The Data Flow Source in the Data Flow tab extracts the metadata from the source to populate the column information. In the case of a stored procedure that uses dynamic SQL within, this can pose a problem in getting the metadata. Depending on the situation here is one way to get around the issue. Below is sample stored procedure that works with the pubs database. Comment the mentioned block in the stored procedure to see the difference in the SSIS package.

With the block commented, the Data Flow Source does not have any columns to extract and map. With the dummy block in place the Data Flow Source extract the required metadata to display columns. Note that the code block will not execute at runtime but it allows you to call a stored procedure with dynamic SQL in it from the Data Flow source in a SSIS package.


    CREATE PROCEDURE [dbo].[GetAuthorsByState]
        -- Add the parameters for the stored procedure here
        @States varchar(10)
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE @strSQL nvarchar(1000)

        -- Start of the block to enable the Data Flow source to work
        -- Comment this block to see the effect on the Data Flow source in the SSIS package
        IF 1 =2
        BEGIN
            SELECT [au_id]
                ,[au_lname]
                ,[au_fname]
                ,[phone]
                ,[address]
                ,[city]
                ,[state]
                ,[zip]
                ,[contract]
                ,[DateAdded]
            FROM [dbo].[tblAuthors]
        END
        -- End of the block to enable the Data Flow source to work

        SET @strSQL = '
            SELECT [au_id]
                  ,[au_lname]
                  ,[au_fname]
                  ,[phone]
                  ,[address]
                  ,[city]
                  ,[state]
                  ,[zip]
                  ,[contract]
                  ,[DateAdded]
              FROM [dbo].[tblAuthors]
            where state = ''' + @States + ''''

        EXEC dbo.sp_executesql @strSQL

    END

SQL CONVERT function and SQL dates

I recently had a colleague stop by asking how he could eliminate the time portion of a date when he extracted the data from a SQL table to MS-Excel. He was having trouble grouping dates in Excel because the data contained the time part as well. My first thought was using DATEPART but my wonderful DBA team member Mary T. suggested using the CONVERT function with the style 101. Worked out perfectly. Here is a sample code displaying the results. There are other styles as well with the CONVERT function.

SELECT 
	[OrderDate], 
	CONVERT(varchar(10), [OrderDate], 101) AS JustOrderDate 
FROM [Northwind].[dbo].[Orders]

Results:

OrderDate                                  JustOrderDate
1996-07-04 00:00:00.000        07/04/1996
1996-07-05 00:00:00.000        07/05/1996

SSIS packages, configuration settings and multiple servers

When I started building SSIS packages I frequently ran into the issue of having to maintain configuration files across different environments. The way I handled it was to use SQL server as the storage place for my package configuration. With that comes the need to be able to point to the appropriate SQL server and database depending on the environment or server. This is where the environment variable comes in handy. With that being said here are the steps to create a package and associated elements to accomplish a flexible configuration setup across servers.

  1. Setup the configuration database. The configuration table can reside in an existing database or in a new one. For this exercise let’s create a new database called “ConfigDB”.
  2. Create an environment variable “SSIS_CONFIG_DB” and set the value to the ConfigDB database. For example, Data Source=MySQLServer;Initial Catalog=ConfigDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False; 
  3. Set the connection string to the ConfigDB from the environment variable.
    1. Create a new package.
    2. Create a new variable called “EnvName” as a string. You can set the value to “Development”.
    3. Create a new OLE DB connection and set the connection to the ConfigDB database.
    4. Open the Package Configurations window.
    5. Check the Enable package configurations.
    6. Click the “Add…” button to start the Package Configuration Wizard.
    7. Choose “Environment variable” and then select the “SSIS_CONFIG_DB” variable from the dropdown.
    8. For the target property, select the ConnectionString for the above created connection manager and then click Next.
    9. Assign a name for the configuration and finish.
  1. Generate and save configuration settings.
    1. Open the Package Configurations window.
    2. Click the “Add…” button to start the Package Configuration Wizard.
    3. Choose “SQL Server” in the Configuration Type.
    4. Select the database connection to the ConfigDB and choose the default configuration table name “SSIS Configurations”.
    5. Enter “MySQLConfig” in the Configuration Filter. This could be any meaningful name.
    6. Click the Next button.
    7. In the objects locate the variable “EnvName” and check the Value option.
    8. Click Next, and then give a name to the configuration. For example, “Init_Settings”.
    9. Click Finish to generate the configuration settings and then close.
    10. To verify that the settings have been created, look at the table rows for “SSIS Configurations” in the ConfigDB database. There should be a row with the ConfigurationFilter “MySQLConfig”.
  1. To verify the working of this setup create a Script task to display the value of the variable EnvName. Make changes to the variable in the ConfigDB database and will see it reflected in the package execution.
  2. Moving to another server. To have this package run in another server with different EnvName values follows the steps below.
    1. Generate the SQL script for database and table from the original ConfigDB and recreate it on the new server.
    2. Copy  the row from the original configuration table “SSIS Configurations” and paste it in the new table and modify the ConfiguredValue for the variable EnvName.
    3. Setup the environment variable “SSIS_CONFIG_DB” with the value set to the connection string to the new configuration database. For example,  Data Source=MyNewSQLServer;Initial Catalog=ConfigDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False; 
    4. Copy the SSIS package to the new server and run it. You should see the value from the new server displayed.

 

 

I’ve used this method to move my packages from development to QA to production with minimum to no changes to configuration settings. I also use this to turn on and off logging modes in the different environment to debug packages.

Determine the SQL Server version of remote servers

Technorati Tags: ,

I ran into this situation where the SQL returned expected data against SQL 2000 but not against SQL 2005. This was one of those breaking changes in the SQL 2005 database engine.  The following piece of code determines the SQL version of the remote server from within a stored procedure. As always I believe there are multiple ways of getting to the solution but here is one. Please note the remote SQL server needs to be a linked server on the SQL server executing the SQL script (Can I add any more SQL/servers in this sentence!).

 

DECLARE @RemoteSQLserver VARCHAR(20), @sql VARCHAR(8000), @SQLVersion VARCHAR(10)

SELECT @RemoteSQLserver = 'sql_1'

/* -------------------------- Determine the version of the target server ---------------------*/
-- Check and drop if temporary table exists
IF (OBJECT_ID(N'tempdb..#Results') IS NOT NULL)
BEGIN
    DROP TABLE #Results
END
-- Create the temp table to hold results
CREATE TABLE [#Results] ([SQLVersion] [varchar] (50))

-- Execute the SERVERPROPERTY on the target server
SELECT @sql = 'SELECT CAST(SQLVersion AS Varchar(20)) FROM OPENQUERY(' + @RemoteSQLserver + ', ''SELECT SERVERPROPERTY(''''ProductVersion'''') AS SQLVersion'')'
INSERT INTO #Results EXEC (@sql)
SELECT @SQLVersion = SQLVersion FROM #Results 

-- Extract the major build of the version number
SELECT @SQLVersion = SUBSTRING(@SQLVersion, 1, CHARINDEX('.', @SQLVersion) - 1)
-- Display the major build
SELECT @SQLVersion 

ASP.NET GridView with fixed column headers

Technorati Tags: ,

 

 Requirement: GridView with fixed column headers that allow for scrolling horizontally and vertically. On page refresh or postback retain focus on the selected Gridrow.

Environment: Visual Studio 2005 and C# on Windows XP Professional SP2.

Solution: Add a web form to the web project. Then drop a panel onto the design view of the form. Adjust the height and width so as to force scrolling with the data displayed. Drop a GridView control onto the panel. I selected a style from the AutoFormat option. In the Column collection, add the Select command field and keep the "Auto-generate fields" checked.

Add a SQLDataSource control to the form and configure it to point a table/query with data rows. Set the GridView’s DataSourceID to the SQLDataSource control.

If you viewed the page in the browser, you should see something like this.

Update the panel properties, and set the scrollbars property to "Auto". The browser view should then look like this. But scrolling causes the column headers to disappear.

Add the following <style> block to the .aspx page.

    <style type="text/css">
        #grdvAuthors th
        {
            text-align: center;
            position: relative;
            cursor: default;
        }
        tbody th
        {
            top: expression(document.getElementById("pnlAuthors").scrollTop-2); /* IE5+ only */
            z-index: 20;
        }
  </style>

And remove the following line from the .aspx designer source code.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

This will now cause the column headers to stay fixed with horizontal and vertical scrolling.