Database

SSIS: 在OLE DB Source SQL Command中使用Variable | Using variable in the SQL Command of OLE DB Source

Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ December 14th, 2012 19:46

需求：

SELECT
ACCOUNT,
DTCREATED
FROM USERS
WHERE DTCREATED > ?

? 来源于某Variable

解决方案：

1. 输入SQL：

注意 Data access mode不是： from variabel。

2. 点击Parameters… 在弹出对话框中选择变量即可。

SSIS: 将SQL结果存入变量 Select SQL result into variable

Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ December 14th, 2012 18:42

1. 修改SQL Task 的Result Set属性

2. 在ResultSet tab下选择要输出的Column及对应的Variable：

SSIS/DTSX: Set ‘IsSorted’ property for the source of MergeJoin

Categories: Database; Tagged with: ETL • SSIS; @ December 13th, 2012 15:31

“Merge Join” is a partially blocking component and it requires the source is sorted.

There are two ways to get the source :

1. Use “Sort” component. — Sort is a full blocking component.

2. Sort the source in SQL and mark “IsSorted”

Because Sort is a full blocking component, So I think the second one will get better performance.

I keep the two source sorted, and got one Error:
The IsSorted property must be set to True on both sources of this transformation.

I can not find this property in the properties or Editor, that’s why I hate SSIS sometimes.

At last I got it from “Advanced Editor…”:

and that’s not enough, we need to specify the SortKeyPosition:

Links:

Sort Data for the Merge and Merge Join Transformations http://msdn.microsoft.com/en-us/library/ms137653.aspx

SQL Server Integration Services SSIS Design Best Practices :

http://www.mssqltips.com/sqlservertip/1893/sql-server-integration-services-ssis-design-best-practices/

Modify SSIS package’s ProtectionLevel and password using C#

Categories: Database; Tagged with: C# • DTSX • ETL • SSIS; @ December 9th, 2012 13:24

Requirement:

Modify SSIS DTSx package production level and password.

Solution:

It’s terrible to open and edit each package, luckily, we got the interface: Microsoft.SqlServer.Dts.Runtime. (How to add the lib)

      

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;

namespace SSISHelper.com.liguoliang.ssis.util
{
    class DtsUtils
    {
        /**
         * Change DTSX package password
         */
        public static void changePassword(String pkgLocation, String oldPassword, DTSProtectionLevel dtsPrdLevel, String newPassword)
        {
            Application app = new Application();

            if (oldPassword != null && oldPassword.Trim() != "")
            {
                app.PackagePassword = oldPassword;
            }

            Package pkg = app.LoadPackage(pkgLocation, null);

            // Modify the password
            pkg.ProtectionLevel = dtsPrdLevel;
            pkg.PackagePassword = newPassword;

            // Save dts pacakge
            app.SaveToXml(pkgLocation, pkg, null);
        }
    }
}

Get source code from GitHub: https://github.com/DavidGuoliang/SSISHelper/blob/master/SSISHelper/com/liguoliang/ssis/util/DtsUtils.cs

Besides password, there’re many interfaces, like logging, Connections, variables,.

Links:

1. SaveToXml: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.savetoxml.aspx

2. Building Packages Programmatically http://msdn.microsoft.com/en-us/library/ms345167.aspx

SSIS package Transaction Error: 0x8004D01B The Transaction Manager is not available

Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ December 9th, 2012 11:43

Error: 0xC001401A at Transaction: The SSIS Runtime has failed 
to start the distributed transaction due to error 0x8004D01B 
"The Transaction Manager is not available.". The DTC transaction 
failed to start. This could occur because the MSDTC Service is not running

make sure the service called”Distributed Transaction Coordinator” is running.

Newer Posts <-> Older Posts