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 :
Modify SSIS package’s ProtectionLevel and password using C#
Categories: Database; Tagged with: C# • DTSX • ETL • SSIS; @ December 9th, 2012 13:24Requirement:
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.
在SSIS/DTSx中使用事务管理 Using Transaction in SSIS DTSx package
Categories: Database; Tagged with: DTSX • ETL • SSIS • Transaction; @ December 9th, 2012 11:32之前有研究过一个container中某一个component失败后的事务管理:http://liguoliang.com/2012/using-transaction-in-ssis/
今天测试某一行数据出错时的事务管理.
需求: DataFlow转移数据时非0即1, 一旦遇到失败, 回滚到操作前的状态.
解决: 使用Transaction.
测试:
准备数据:
表1 Source: tbUser: userID, userName, emai, age(varchar)
表2, Destination: 结构与表1相似, 但age 类型为 int.
(用for loop insert一堆数据:
DECLARE @i int
SET @i = 0
WHILE @i < 1000000
BEGIN
SET @i = @i + 1
INSERT INTO guoliangDB.dbo.tbUser (userName, email) values (‘name’ + CAST(@i as varchar(8)), ’email@’ + CAST(@i as varchar(8)))
END
制造一条问题数据:
update tbUser set age = ‘fakeage’ where userID = 980000;
设计DTSx:
在数据跑到980,000时会因为数据转换而出现错误:
运行结束, 错误信息:
OnError,,9/12/2012 11:17:58 AM,9/12/2012 11:17:58 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Invalid character value for cast specification”.
OnError,,9/12/2012 11:17:58 AM,9/12/2012 11:17:58 AM,-1071607780,0x,There was an error with input column “age” (54) on input “OLE DB Destination Input” (38). The column status returned was: “The value could not be converted because of a potential loss of data.”.
此时Select Destination的数量为: 979998. 剩余的数据不会再被导入.
修改Dataflow transaction属性为: required
在遇到错误之后, Select destination数量, 逐渐减少, 直至rollback到0.
结论: 如需要事务支持, require transaction.
SSIS Designer(Visual Studio 2005) cannot open package: Exception from HRESULT: 0x8007007E (System.Windows.Forms)
Categories: Database; Tagged with: BIDS • DTSX • ETL • SQL Server • SSIS • Visual Studio; @ December 8th, 2012 17:31I try to apply SQL Server 2005 in my PC(Win 7 64bits), failed, After that, when I try to open SSIS package in BIDS, I got this Error every time:
TITLE: Microsoft Visual Studio
——————————
An error prevented the view from loading.
——————————
ADDITIONAL INFORMATION:
The specified module could not be found. (Exception from HRESULT: 0x8007007E) (System.Windows.Forms)
—————————— BUTTONS: OK ——————————
It’s kind of ridiculous. because I need to finish my DTSx package design, so I re-install SQL Server sp1. but seems no change.
Then I got this link:
2008 SSIS Designer stops working after installing VS11 Dev Preview
the chosen answer seems not for me, and I got the solution from one of the reply:
I solved the problem by deleting the whole folder “C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8“, and copying it from a machine that was working. Not the smoothest solution, I know, but it did work!
Gorgeous!