在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!
Get DTSx/SSIS Connections From C#
Categories: Database • Development Notes; Tagged with: C# • DTS • DTSX • ETL • SSIS; @ December 5th, 2012 23:49Requirement: Get all connections from dtsx packages using C#.
Solution:
Using Microsoft.SqlServer.Dts.Runtime to load the package, and then get all connections.
Step1 Add reference
In case you are totally new to .Net/C#, you may need this screen capture:
In the pop-up window, click “Browse” tab, and select: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Microsoft.SQLServer.ManagedDTS.dll
About the dll path:
ref 2: http://stackoverflow.com/questions/4920591/havent-got-microsoft-sqlserver-manageddts-dll-but
Step 2 Coding
import: using Microsoft.SqlServer.Dts.Runtime
public void testDts()
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;
pkgLocation =
@"C:\Users\x\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\" +
@"Package.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
Connections conns = pkg.Connections;
foreach (ConnectionManager cm in conns)
{
Console.WriteLine("Name = " + cm.Name + ", HostType = " + cm.HostType + "; ConnectionString=" + cm.ConnectionString);
}
}
API: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.connections(v=sql.100).aspx
Output:
Name = Flat File Connection Manager, HostType = ConnectionManager; ConnectionString=C:\Users\x\Desktop\ssisTest.txt
Name = x_instan, HostType = ConnectionManager; ConnectionString=Data Source=x-PC\x_INSTAN;Integrated Security=SSPI;Connect Timeout=30;
SSIS: Append Header/Footer to flat file
Categories: Database; Tagged with: CMD • DTSX • ETL • SSIS • VB; @ November 25th, 2012 17:52Requirement:
Append additional header/footer to the destination flat file:
Solution:
Header/Footer can be added by several ways in SSIS, here is 3 ways I’m using:
Usinig Union in SQL :
-- Header
Select 'START' + convert(varchar(8),GETDATE(), 112)
-- Body
Union All
Select FULLNAME
from OVERSEA_ADDRESS
WHERE POSTAL_CODE = ''
-- Footer
Union All
Select 'END'+ Convert(varchar(8), Count(*))
from TWO_FA_OVERSEA_ADDRESS
WHERE POSTAL_CODE = ''
But sometimes the requirement is more complex, then we need to use Script task.
Script task using VB/C#
We can read/write file using VB or C#, and we can get the variables/paremeters, so basically, we can handle most of the requirement by Script task.
Reading file using VB:
Dim objReader As New System.IO.StreamReader(FILE_NAME)
' Read each line, and append the text into textLines
Dim currentLine As String
Do While objReader.Peek() <> –1
currentLine = objReader.ReadLine()
textLines.Add(currentLine)
Loop
Writing to file
' For loop the textLines
Dim index As Integer
For index = 0 To textLines.Count – 1
Dim text As String = textLines.Item(index).ToString
outStreamWriter.Write(text & vbNewLine)
Next outStreamWriter.Close()
Using copy CMD
Generate the header, body, foot file and use CMD to combine the files:
copy header.txt+body.txt+footer.txt output.txt /y
SSIS: Get Variables/Properties In Script task
Categories: Database; Tagged with: DTSX • ETL • SQL Server • SSIS • VB; @ November 25th, 2012 15:52Requirement:
Get variables/properties in Script task.
Note: Do remember to set the variables in the component editor.
Solution(in VB):
1. Get properties:
MsgBox(Dts.Connections(“fileDest”).ConnectionString)
2. Get variables:
MsgBox(Dts.Variables(“User::Counter”).Value.ToString)