SQL Server

SSIS: Logging message in Script task

Categories: Database; Tagged with: Database • SQL Server • SSIS; @ October 24th, 2012 15:24

The 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

BIDS: Sorting files in SSIS/DTSX projects

Categories: Database; Tagged with: BIDS • ETL • SQL Server • SSIS; @ October 17th, 2012 16:42

It’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:

Top 5 Free SQL Server Tools

SQL Server: Grant user access permission to database

Categories: Database; Tagged with: SQL • SQL Server; @ October 17th, 2012 12:20

Grant user permission to login to the database:


USE YOUR_DB
CREATE USER ROA_NAME FOR LOGIN ROA_NAME

Add user to the group:


USER YOUR_DB
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'ROA_NAME'

“Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.”

Links:
http://stackoverflow.com/questions/6688880/how-do-i-grant-read-access-for-a-user-to-a-database-in-sql-server

SQL Server: 用户登录失败Login failed, The user is not associated with a trusted SQL Server connection(Error: 18452)

Categories: Database; Tagged with: SQL • SQL Server; @ October 11th, 2012 18:52

有若干情形可能导致使用用户名/密码登录失败，其中一类便是未开启登录模式：

更详细的参考：

http://support.microsoft.com/kb/555332#method2

Generate SSIS log using dtexec 生成SSIS 日志

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

First 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

Newer Posts <-> Older Posts