SSIS: Logging message in Script task
Categories: Database; Tagged with: Database • SQL Server • SSIS; @ October 24th, 2012 15:24The Script task can use the Log method of the Dts object to log user-defined data. If logging is enabled, and the ScriptTaskLogEntry event is selected for logging on the Details tab of the Configure SSIS Logs dialog box, a single call to the Log method stores the event information in all the log providers configured for the task.
Pre-condition:
Loging is enabled and ScriptTaskLogEntry is selected:
Loging code:
Dim emptyBytes(0) As Byte
Dts.Log("TotalRecords: " + Dts.Variables("User::NoForCheck").Value.ToString, 0, emptyBytes)
'Error:
Dts.Events.FireError(0, "", "Can not find the source file!", "", 0)
Dts.TaskResult = Dts.Results.Failure
Return
Log:
User:ScriptTaskLogEntry,,,24/10/2012 3:16:22 PM,24/10/2012 3:16:22 PM,0,0×00,TotalRecords: 27
API:
Logging in the Script Task
http://msdn.microsoft.com/en-us/library/ms136131.aspx#Y272
SSIS: Lookup Error: Row yielded no match during lookup
Categories: Database; Tagged with: Database • SQL • SSIS; @ October 24th, 2012 14:14Input: row set;
Output: validate the records using the other table’s data, and output the result.
Error Mesg: Error: 0xC020901E at Data Flow Task, Lookup 1 [2409]: Row yielded no match during lookup.
In SSIS 2005, if can not find the corresponding record, the component will be failed by default.
So the Error occurred.
What we need to do is, changing the ‘Error Output’ of the lookup component, from ‘Fail Component’ to ‘Redirect row’;
Output:
BIDS: Sorting files in SSIS/DTSX projects
Categories: Database; Tagged with: BIDS • ETL • SQL Server • SSIS; @ October 17th, 2012 16:42It’s really ridiculous that we cannot sort the files in SSIS project in BIDS.
fortunately, we got a plug-in named ‘BIDS Helper’, which has a feature called ‘Sort Project Files’:
http://bidshelper.codeplex.com/
Sort Project Files
This feature adds a “Sort by name” menu option to the “SSIS Packages” folder of an SSIS project in Visual Studio:
This feature is implemented natively in BIDS in SQL2008.
Other sorting:
1. Sorting MSDB package folders
2. Sorting SQL Project Files in SQL Server Management Studio
Links:
Generate SSIS log using dtexec 生成SSIS 日志
Categories: Database; Tagged with: ETL • SQL • SQL Server • SSIS; @ October 9th, 2012 20:11First we need to Config logging in SSIS package(GUI):
Two methods to generate the log:
1. Using SSIS ‘Connection’ to sotre the log;
Example:
dtexec /f DavidTest.dtsx /l “DTS.LogProviderTextFile;log.txt”
(log.txt is a Connection in SSIS package).
2. Redirect console message into a file:
dtexec /f DavidTest.dtsx /cons NMT >C:/ConsoleMsg.log
About the dtexec parameters:
http://msdn.microsoft.com/en-us/library/hh231187.aspx
SSIS Logging:
http://weblogs.sqlteam.com/jamesn/archive/2008/02/11/60502.aspx
How to add Simple Logging to an SSIS Package
SQL Server Singapore User Group Event: What’s New in SSIS 2012
Categories: Database; Tagged with: ETL • Singapore • Singapore SQL Server User Group • SQL Server • SSIS; @ October 4th, 2012 0:53
下班后跟同事一起去微软参加SSIS的Event.
公司使用DTS/SSIS已经很久了, 最早的一批package还是跑在SQL 2000上的, 也就是DTS. 这也是我最近工作的主要内容, 了解每一个package的细节, 然后像新版本靠近.
DTS/SSIS有个要命的地方: 如果你要了解一个package, 那就要打开这个package, 在UI上来回拖动—当mapping很多的时候, 真是要命啊! 更要命的是, 当我在research DTS时, 我没有production的访问权限! 只能蹲在DBA电脑上打开每一个DTS然后截屏….
SSIS似乎还是老样子, 如果能导出一个具有一定可读性的文档就好了.
我没有SQL的经验, 更没有DTS/SSIS的经验, 所以很早就报名参加, 可惜一直没有收到回复. 不过签到的时候看到自己名字在列表里了.
有一些marketing的东西在里面, SSIS有些细节处理的很粗糙, 2000/2005/2008 都有很粗糙甚至很粗暴的地方, 这个谁用谁知道, 但核心的功能做好了就Ok, 表面的东西其实也只是在设计的时候才会有体会.
前面有一些很细节的地方, 譬如UI上会有Getting Started啊, 撤销操作啊什么的. 有一个不错的改进:
New variable mapping — 支持复制粘贴哎! 如果你有看过DTS的mapping, 你就知道这个功能有多方便了!(其实一开始就应该有的)
catalog啊, deploy到DataBase后看起来用的很方便, logging跟report看起来也很给力!
可惜, 可惜我们还不知道几时才会迁移到新版本…
总体来讲, SSIS是个不错的工具, 很方便也很强大, 只是可能需要更细致的挖掘用户的需求, 同时还要消除一些management level的困惑. 譬如我们现在没有100%依赖SSIS的一个原因: 对于整个package, 如何部分re-run? 对于使用config file的敏感信息, 如何加密? 不过幸好SSIS在不断改进, 希望SSIS可以做的更好!
Singapore SQL Server User Group 的Facebook:
http://www.facebook.com/groups/sqlugsg/
PPT也可下载.