Bora.Choi

1.  --=============================================================
2. -- 장비 이동하거나 DB 복원시 파일을 다른 디렉토리로 보관해야할때
3. -- 파일들이 많은 DB의 경우 일일이 변경하기 어려움이 있어서 원본 기준으로 복원스크립트 생성하고
4. -- 디렉토리 경로와 백업 파일 경로만 넣어주면 되게끔 생성 한다.
5. --==============================================================

6.  

7. SET NOCOUNT ON

8. DECLARE @sql            nvarchar(max)

9. DECLARE @sql_move       nvarchar(3000)

10. DECLARE @move           nvarchar(200)

11. DECLARE @backup_type    char(1)

12. DECLARE @name           sysname

13. SET @backup_type = 'L' --N

14. SET @sql_move = ''

15. DECLARE cur_restore CURSOR FOR

16.     SELECT name FROM sys.databases WHERE database_id > 4 and NAME != 'LiteSpeedLocal' and state = 0

17.     ORDER BY name

18.  

19. OPEN cur_restore

20. FETCH NEXT FROM cur_restore

21. INTO @name

22.  

23. WHILE @@FETCH_STATUS = 0

24. BEGIN

25.     SET @sql = NULL
        SET @sql_move = ''

26.     IF @backup_type = 'L'

27.     BEGIN

28.         SET @sql = 'exec master.dbo.xp_restore_database' + char(13)

29.                    + '@database = ''' + @name + '''' + char(13)

30.                    + ',@filename = ''''' + char(13)

31.                    + ',@filenumber = 1' + char(13)

32.                    + ',@with = ''REPLACE''' + char(13)

33.                    + ',@with = ''NORECOVERY''' + char(13)

34.  

35.  

36.         DECLARE cur_move CURSOR FOR

37.                 SELECT ',@with = ''MOVE ''''' +

38.                             name + ''''' TO N''''' +

39.                             filename + ''''''' '

40.                 FROM sys.sysaltfiles  WHERE dbid = db_id(@name)

41.                 ORDER BY fileid

42.         OPEN cur_move

43.         FETCH NEXT FROM cur_move

44.         INTO @move

45.  

46.         WHILE @@FETCH_STATUS = 0

47.         BEGIN

48.             SET @sql_move = @sql_move + @move + char(13)

49.            

50.             FETCH NEXT FROM cur_move

51.             INTO @move

52.         END

53.         CLOSE cur_move

54.         DEALLOCATE cur_move

55.  

56.   

57.     END

58.     ELSE IF @backup_type = 'N'

59.     BEGIN

60.         SET @sql = 'RESTORE DATABASE ' + @name + char(13)

61.                  + 'FROM DISK =''''' + char(13)

62.                  + 'WITH NORECOVERY' + char(13)

63.  

64.         DECLARE cur_move CURSOR FOR

65.                 SELECT ',MOVE ''' + name + ''' TO  ''' + filename + ''''

66.                 FROM sys.sysaltfiles  WHERE dbid = db_id(@name)

67.                 ORDER BY fileid

68.         OPEN cur_move

69.         FETCH NEXT FROM cur_move

70.         INTO @move

71.  

72.         WHILE @@FETCH_STATUS = 0

73.         BEGIN

74.             SET @sql_move = @sql_move + @move + char(13)

75.            

76.             FETCH NEXT FROM cur_move

77.             INTO @move

78.         END

79.         CLOSE cur_move

80.         DEALLOCATE cur_move

81.     END

82.  

83.     SET @sql = @sql + @sql_move + char(13)

84.  

85.     print @sql

86.  

87.     FETCH NEXT FROM cur_restore

88.     INTO @name

89. END

90.  

91. CLOSE cur_restore

92. DEALLOCATE cur_restore

93.  

이 글은 스프링노트에서 작성되었습니다.

 Convert Hex value to Signed 32-bit int

1. /* ======================================================================== *
    *   Function Name:    fnHexToInt                                           *
    *                                                                          *
    * -- Description --------------------------------------------------------- *
    * Convert, up to 8 nibbles, hex string ('FFFFFFFF'...'00000000')           *
    *      to                                                                  *
    *  Signed 32-bit int (-2147483648..2147483647).                            *
    * -- History ------------------------------------------------------------- *
    * 1.0.0 28.Oct.2001, Ofer Bester                                           *
    * ======================================================================== */
   PRINT '* CREATE FUNCTION: fnHexToInt, Ver. 1.0.0 (28.Oct.2001).'
   SETUSER 'dbo'
   go
2. IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToInt' AND type IN ('FN', 'IF', 'TF'))
   BEGIN
       PRINT '  + DROP FUNCTION dbo.fnHexToInt.'
       DROP FUNCTION dbo.fnHexToInt
   END
   PRINT '  + CREATE FUNCTION dbo.fnHexToInt.'
   go
3. CREATE FUNCTION dbo.fnHexToInt (@Hex varchar(8))
   RETURNS int
   -- Ver. 1.0.0 28.Oct.2001, Ofer Bester
   AS BEGIN
       DECLARE @i      tinyint,
               @Nibble tinyint,
               @ch     char(1),
               @Result int
4.     SET @i      = 1                                                     -- Init nibble counter
       SET @Result = 0                                                     -- Init output parameter
5.     SET @Hex     = UPPER( LTRIM( RTRIM( @Hex ) ) )                      -- Convert to uppercase
6.     WHILE (@i <= LEN(@Hex))
       BEGIN
           SET @ch = SUBSTRING(@Hex, @i, 1)
7.         IF      (@ch >= '0' AND @ch <= '9') SET @Nibble = ASCII(@ch) - ASCII('0')
           ELSE IF (@ch >= 'A' AND @ch <= 'F') SET @Nibble = ASCII(@ch) - ASCII('A') +10
           ELSE RETURN NULL
8.         IF( @Result > 0x7FFFFFF)                                        -- 134217727 = 0x7FFFFFF
           BEGIN
               SET @Result = @Result & 0x7FFFFFF                           -- Set MSB, of 7 nibbles, OFF
               SET @Result = @Result * 16 + @Nibble +0x80000000            -- Shift left 4Bits, Add last nibble and convert to negetive number.
           END
           ELSE BEGIN
              SET @Result = @Result *16 +@Nibble                           -- Shift left 4Bits, Add nibble.
           END
9.         SET @i = @i +1                                                  -- Next nibble.
       END -- While
10.     RETURN ( @Result )
    END -- Function
    go
11. -- Check create result -------------------------------------------------------
    IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToInt' AND type IN ('FN', 'IF', 'TF'))
    BEGIN
        PRINT 'DONE (CREATE FUNCTION: fnHexToInt).'
    END
    ELSE BEGIN
        PRINT '****** FAILES (CREATE FUNCTION: fnHexToInt) ******'
    END
    PRINT ''
    go
12. SETUSER
    go

이 글은 스프링노트에서 작성되었습니다.

CONSTRAINT 조사

1. select * from sys.default_constraints 
2. select * from sys.key_constraints 
3. select * from sys.check_constraints 
4. 
   
5. --=================
6. -- 2005용
7. --=================
8. -- FK
9. SELECT  object_name(fk.constraint_object_id) AS fk_name, 
10.     fk.constraint_column_id AS fk_clolum,
11.     object_name(fk.parent_object_id) AS parent_name,
12.     (select name from sys.columns where object_id =  fk.parent_object_id and column_id = fk.parent_column_id) as parent_column,
13.     object_name(fk.referenced_object_id) AS referenced_name,
14.     (select name from sys.columns where object_id =  fk.referenced_object_id and column_id = fk.referenced_column_id) as referenced_column
15. FROM sys.foreign_key_columns  as fk
16. WHERE fk.parent_object_id  = object_id('<@table_name, @sysname,@table_name>') 
17.         or  fk.referenced_object_id = object_id('<@table_name, @sysname,@table_name>')
18. 
    
19. -- DEFAULT
20. SELECT object_name(parent_object_id) as table_name,
21.        name,
22.        (select name from sys.columns where object_id =  df.parent_object_id and column_id = df.parent_column_id) as column_name, 
23.        definition
24. FROM sys.default_constraints   as df
25. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
26. 
    
27. -- DEFAULT
28. SELECT
29.     object_name(parent_object_id) as table_name,
30.        name,
31.        (select name from sys.columns where object_id =  ck.parent_object_id and column_id = ck.parent_column_id) as column_name, 
32.        definition
33. FROM sys.check_constraints  as ck
34. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
35. 
    
36. 
    
37. 
    
38. --=================
39. -- 2000용
40. --=================
41. --FK
42. select object_name(fkeyid) , object_name(constid),object_name(rkeyid)  
43. from sys.sysforeignkeys where fkeyid = object_id ('<@table_name, @sysname,@table_name>')
44. 
    
45. 
    
46. select object_name(id), object_name(constid) ,status from sys.sysconstraints  
47. where status&5 = 1 --FK 
48. where constid  = object_id('<@table_name, @sysname,@table_name>')
49. order by object_name(constid)
50. 
    
51. 
    
52. 
    
53. --=========================== 
54. -- DROP
55. --===========================
56. -- FK 2005용
57. SELECT 'ALTER TABLE ' + bject_name(fk.parent_object_id) + ' DROP CONSTRAINT ' +  object_name(fk.constraint_object_id)
58. FROM sys.foreign_key_columns
59. WHERE fk.parent_object_id  = object_id('<@table_name, @sysname,@table_name>') 
60. 
    
61. -- DEFULT 2005용
62. SELECT 'ALTER TBLE ' + object_name(parent_object_id)  + ' DROP CONSTRAINT ' + name
63. FROM sys.default_constraints 
64. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
65. 
    
66. -- CK 2005용
67. -- DEFULT 2005용
68. SELECT 'ALTER TBLE ' + object_name(parent_object_id)  + ' DROP CONSTRAINT ' + name
69. FROM sys.default_constraints  
70. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
71. 
    
72. 
    
73. -- FK 2000용
74. SELECT 'ALTER TABLE ' + object_name(fkeyid) + ' DROP CONSTRAINT ' +  object_name(constid) 
75. FROM sys.sysforeignkeys
76. WHERE fkeyid = object_id ('<@table_name, @sysname,@table_name>')
77. 
    
78. -- INDEX 2000용
79. select  'ALTER TABLE ' + object_name(id) + ' DROP INDEX '  + name 
80. from sys.sysindexes where  object_name(id) in ('GOODSDAQ_BANNER_POSITION')
81. order by id, name
82. 
    

이 글은 스프링노트에서 작성되었습니다.

1. --===================================

1. -- SP_WHO2와명령어동시확인하기

1. --===================================

1. SELECT

1.        ISNULL(D.text, '') AS SQLStatement,

1.        object_name(D.objectid) AS 'sp_ame',

1.        A.Session_ID SPID,

1.        ISNULL(B.status,A.status) AS [Status],

1.        A.login_name AS [Login],

1.        ISNULL(A.host_name, '  .') AS HostName,

1.        ISNULL(CAST(C.BlkBy AS varchar(10)), '  .') AS BlkBy,

1.        DB_NAME(B.Database_ID) AS DBName,

1.        B.command,

1.        ISNULL(B.cpu_time, A.cpu_time) AS CPUTime,

1.        ISNULL((B.reads + B.writes),(A.reads + A.writes)) AS DiskIO,

1.        A.last_request_start_time AS LastBatch,

1.        ISNULL(A.program_name, '') AS ProgramName,

1.        ISNULL(A.client_interface_name, '') AS ClientInterfaceName

1. FROM sys.dm_exec_sessions A

1.        LEFT OUTER JOIN sys.dm_exec_requests B ON A.session_id = B.session_id

1.        LEFT OUTER JOIN (

1.              SELECT A.request_session_id SPID, B.blocking_session_id BlkBy

1.              FROM sys.dm_tran_locks A

1.                     INNER JOIN sys.dm_os_waiting_tasks B ON A.lock_owner_address = B.resource_address

1.        ) C ON A.Session_ID = C.SPID

1.        OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) D

1. go

이 글은 스프링노트에서 작성되었습니다.

백업 DB에서 최근  백업정보

• DBA는 백업은 중요한 일중에 하나이다. 모든 장비를 확인해 가면서 백업 성공/실패를 확인할 수 없으므로 마지막으로 백업된 날짜를 확인하여 메일로 공지되는것이 좋은 방법이다. 
• 아래 쿼리는 그 내용이며 위 쿼리와 조합을 해서 백업보고를 받게 할 것이다.  
• 해당 보고는 DTS 패키지나 SSIS를 사용해서 처리하거나 JOB으로 장비별로 OSQL을 접속해서 처리하도록 해도 된다. (링크드 서버를 사용하지 않기 위햬) 

1. select max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) as 'Number of Days since last backup',
   b.type as  'Backup type (D-database,L-log)', b.backup_size, d.name as database_name
   from  master..sysdatabases d with (nolock)
    left join msdb..backupset b  with (nolock) on d.name = b.database_name
    and b.backup_start_date = (select max(backup_start_date)
                                      from msdb..backupset b2
                                      where b.database_name = b2.database_name
                                     and b2.type = 'D')
   where d.name != 'tempdb'
   group by d.name, b.type, b.backup_size
   union all
   select max(isnull(datediff(dd,b.backup_start_date,getdate()),0))
   as 'Number of Days since last backup',
   b.type as  'Backup type (D-database,L-log)', b.backup_size, d.name
   as database_name
   from  master..sysdatabases d with (nolock)
   join msdb..backupset b  with (nolock)
   on d.name = b.database_name
   and b.backup_start_date = (select max(backup_start_date)
                                      from msdb..backupset b2
                                      where b.database_name = b2.database_name
                                      and b2.type = 'L')
   group by d.name, b.type, b.backup_size
   order by d.name,b.type   -- optional 
2. ================================================
   다른 방법 
3. ================================================
   SELECT
    cast(database_name AS varchar(30)) AS [DBName],
    cast(user_name AS varchar(30)) AS [UserName],
    str(cast(backup_size AS decimal(20,2)) / 1048576 ,10,2) + ' MB' AS [BackupSize],
    cast(datediff(n,backup_start_date,backup_finish_date) AS varchar(5)) + ' min.' AS [Duration],
    cast(datediff(dd,backup_finish_date,Getdate()) AS varchar(10))  AS [BackupAge],
    convert(varchar(20),backup_finish_date) AS [FinishDate],
    physical_device_name AS [Location],
    mf.device_type
   FROM
    master..sysdatabases DB
    JOIN
    msdb..BACKUPSET BS
    ON DB.name = BS.database_name
    JOIN msdb..backupmediaset MS
    ON
    BS.media_set_id = MS.media_set_id
    JOIN msdb..backupmediafamily MF
    ON
    BS.media_set_id = MF.media_set_id
    JOIN
    (
     SELECT
      max(backup_set_id) AS backup_set_id
     FROM
      msdb..BACKUPSET BS
      JOIN msdb..backupmediaset MS
       ON
       BS.media_set_id = MS.media_set_id
       JOIN msdb..backupmediafamily MF
       ON
       BS.media_set_id = MF.media_set_id
     WHERE
      type = 'D'
      AND mf.device_type <> 7
     GROUP BY database_name
    ) MaxBackup
    ON
    BS.backup_set_id = MaxBackup.backup_set_id
   WHERE
    type = 'D' 
    

이 글은 스프링노트에서 작성되었습니다.

백업

전체 DB를 백업하는 간단한 스크립트.

저장 프로시저로 만들어서 Job 으로 이용해도 됨.

Simple script to backup all databases

Problem
Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup all databases on your server.   This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server.  You could use Enterprise Manager to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Solution
With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this.

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBnameYYYDDMM.BAK".

1.  DECLARE @name VARCHAR(50) -- database name 
   DECLARE @path VARCHAR(256) -- path for backup files 
   DECLARE @fileName VARCHAR(256) -- filename for backup 
   DECLARE @fileDate VARCHAR(20) -- used for file name
   
   SET @path = 'C:\Backup\' 
   
   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
   
   DECLARE db_cursor CURSOR FOR 
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ('master','model','msdb','tempdb') 
   
   OPEN db_cursor  
   FETCH NEXT FROM db_cursor INTO @name  
   
   WHILE @@FETCH_STATUS = 0  
   BEGIN  
          SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
          BACKUP DATABASE @name TO DISK = @fileName 
   
          FETCH NEXT FROM db_cursor INTO @name  
   END  
   
   CLOSE db_cursor  
   DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Automating Transaction Log Backups for All Databases

Problem
Maintenance plans are a great thing, but sometimes the end results are not what you expect.  The whole idea behind maintenance plans is to simplify repetitive maintenance tasks without you having to write any additional code.  For the most part maintenance plans work without a problem, but every once in awhile things do not go as planned. Two of the biggest uses of maintenance plans are issuing full backups and transaction log backups.  What other approaches are there to issue transaction log backups for all databases without using a maintenance plan?

Solution
With the use of T-SQL you can generate your transaction log backups and with the use of cursors you can cursor through all of your databases to back them up one by one. With the use of the DATABASEPROPERTYEX function we can also just address databases that are either in the FULL or BULK_LOGGED recovery model since you can not issue transaction log backups against databases in the SIMPLE recovery mode.

Here is the script that will allow you to backup the transaction log for each database within your instance of SQL Server that is either in the FULL or BULK_LOGGED recovery model.

You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBname_YYYDDMM_HHMMSS.TRN".

1. DECLARE @name VARCHAR(50) -- database name  
   DECLARE @path VARCHAR(256) -- path for backup files  
   DECLARE @fileName VARCHAR(256) -- filename for backup  
   DECLARE @fileDate VARCHAR(20) -- used for file name 
   
   SET @path = 'C:\Backup\'  
   
   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
      + '_' 
      + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
   
   DECLARE db_cursor CURSOR FOR  
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ('master','model','msdb','tempdb') 
      AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
   
   OPEN db_cursor   
   FETCH NEXT FROM db_cursor INTO @name   
   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   
          SET @fileName = @path + @name + '_' + @fileDate + '.TRN'  
          BACKUP LOG @name TO DISK = @fileName  
   
          FETCH NEXT FROM db_cursor INTO @name   
   END   
   
   CLOSE db_cursor   
   DEALLOCATE db_cursor  

In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases. 

이 글은 스프링노트에서 작성되었습니다.

다음과 같이 For루프 컨테이너를 추가합니다.

루프 편집기에서 다른 조건은 설정할 필요 없이 EvalExpression 속성 값에 true를 입력합니다.

이렇게 설정하는 것은 쿼리에서

WHILE (1=1)

BEGIN

 …

END

와 비슷한 형태입니다.

이제, For 루프 컨테이너 안에 필요한 작업들을 설정해서 넣고선, 5초 동안 대기하는 것을 구현해야 합니다.

이전에 올린 글에 FOR 루프 컨테이너를 이용해서 일정 시간 동안 대기하는 것을 구현한 것이 있습니다. 하지만 이 방법은 쓰지 마시기 바랍니다. (CPU가 과도하게 높이 올라갑니다.)

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=523

대신, SQL 명령을 이용하는 것이 가장 바람직할 것 같습니다.

작업들을 구성한 후, 가장 마지막 부분에 SQL 실행 작업을 추가한 후,

DB 연결을 설정하고, 다음과 같은 간단한 명령을 사용합니다.

예) 5초 동안 대기를 해야 할 경우

                  WAITFOR DELAY ’00:00:05’

매 번 루핑 때 마다 마지막 단계에서 5초간 대기하는 무한 루프가 만들어졌습니다.

이 글은 스프링노트에서 작성되었습니다.

Using the script task to dynamically build SSIS package variables

Written By: Hal Hayes -- 2/27/2008

 

Problem
One of the advantages of using SSIS is the ability to dynamically create tasks that can take different elements and manipulate them in code instead of having to hard code the package to do only one task. In a previous tip we looked at how to use expressions to dynamically build an output file: "Dynamic Flat File Connections in SQL Server Integration Services".  In this tip, we will show you another way to do this using the script task.

Solution
Basically what we will be doing is generating an output file name using a package variable, representing the location and name of an output file and using the Script Task to dynamically build the output filename.

Using this method, modifying variable information is not just restricted to output file information. You can use this method to generate input file locations, complex queries that can be used in your Execute SQL tasks, data for inserts, etc.  The Script Task is a powerful component that can offer developers capabilities not covered in other Control Flow items.

In this solution we will address the simplest case of using a Script Task to dynamically modify a variable that is assigned to an output flat file connection so that the file is saved with a name and location of our choosing.

Create Package

To start, create a new Integration Services project using the SQL Server Business Intelligence Design Studio (BIDS).

• Add a Data Flow Task to your Control Flow surface
• In the Connection managers, create an OLE DB connection to the AdventureWorks database
• Double click the Data Flow task and add an OLE DB Source, set it to any table or view (in this case I used the Purchasing.vVendor view)
• Add a Flat File Destination and connect the two objects with a Data Source Path (green arrow) as shown below
• Open the Flat File Destination Editor and create a new connection, choosing the output file type (delimited) with a name of "CSV File Output".  Also, choose a filename which can be anything at this point, this will be dynamically generated again below, but for now you need some filename to complete this step.

Add Package Variables

Move back to the Control Tab and right click on any area, but not on an object and select Variables. (We do this to make sure that our variables, which we will design in the next step, are scoped to the entire package.)  Another way to do this is from the menus by selecting View | Other Windows | Variables.

Now add the following variables (within scope of the entire package).

Note: Our package is called "WithScripting", so here is an example of our variables after they have been setup.

Adding the Script Task

Now add a Script Task to the Control Flow above the Data Flow Task. Connect the two objects (from Script Task to Data Flow Task) with a precedence constraint (green arrow) as shown below.

Open the Script Task Editor. We will use the defaults for this item, but we need to add the following to the Script tab.By setting our variables in this property window, we make them available to the Script environment. If this step is not done the package variables can not be used with the script task.  Note: the variable names have to match the exact same names that were used, the variables are case sensitive.

Next select the “Design Script...” button. This opens the Visual Studio for Applications (VSA) design environment. By default, you will have a ScriptMain class with a single method, Main.

Replace the Main method with the following text:

Here is a snippet of what our script looks like after pasting the above code.

Discussion of VB.Net coding is beyond the scope of this article, but a brief explanation follows. The above code reads the directory information from the SSIS variable into a string variable, and ensures that it ends with a “\” character for the formatting of our full file name and path. The file prefix is also captured in a string variable. We craft our output path and file name in a string variable, dynamically adding the month number and year to the file name. The file extension of “.txt” is added to the end of the file name.

Finally the SSIS variable, mOutFileName, is set to the full file path. Remember that we set this variable as read-write in the Script task. At the end, the script closes by setting the DTS.TaskResult value to success. The setting of this value is required for proper operation of the script (the value could also be set to failure).

Now you can close the VSA environment.

Modify the Flat File Connection

• Select the flat file connection object "CSV File Output"
• Right click and select Properties
• Select the Expressions property and click on the ellipse "..."
• In the Property Expression Editor, select the ConnectionString property as shown below.

• Next select the “…” button to bring up the Expression Builder interface shown below
• Set the expression to @[User::mOutFileName] by dragging the value down to the Expression window from the Variables window as shown below and select OK.

That is it. Now you have dynamically modified your filename by adding the month and year to the end of it. And you are saving it in the location of your choosing as defined by the mDirectory variable. 

So for our example if this was run on February 27, 2008 the file created would be "C:\SSIS\Output\Vendors-22008.txt".

Next Steps

• Now you have another way of dynamically generating file names.  You can use this method or use the method found in this tip Dynamic Flat File Connections in SQL Server Integration Services
• Take this a step further and format the date so that it always includes a two month year.
• Also, use this technique to build other dynamic variables that can be used in other parts of your SSIS packages
• Download the SSIS package

이 글은 스프링노트에서 작성되었습니다.

 

• Debugging and Logging in SSIS
• Use breakpoints and checkpoints to more efficiently debug packages -->-->
• By:  Erin Welker
• Features
• InstantDoc #98167
• From the April 2008 edition of SQL Server Magazine

It’s been more than two years since SQL Server 2005 shipped with SQL Server Integration Services (SSIS). However, many companies still haven’t converted their DTS packages to SSIS, possibly because the migration process can be painful or they don’t have the time to learn about a new product.

Those of you who have made the conversion know that SSIS definitely isn’t the “next version of DTS.” Debugging and logging are just two of many areas that have undergone a complete overhaul in SSIS. Let’s take a high-level look at SSIS’s debugging and logging capabilities. If you’re among those who are still using DTS, prepare to be impressed.

Debugging Packages During Development

SSIS is far ahead of DTS in the area of debugging, which is the process of identifying errors that prevent a package from being executed or producing the desired results. In DTS, debugging typically involves using MsgBox statements or the VBScript Stop command to simulate a breakpoint in scripts. Such statements had to be removed from the package before it was put into production. In contrast, debugging is built into SSIS, and nothing has to be removed when the package is moved to a production environment.

The problem with SSIS’s debugging tools is that they aren’t consistent across all types of tasks, so knowing when to use what tools can be a big help. Let’s look at SSIS’s debugging capabilities at the package level, followed by debugging within Control Flow tasks and Data Flow tasks.

Package-Level Debugging

During the development process, SSIS provides red or yellow icons within the SSIS designer that tell you when something is inherently wrong with the package. To view the message associated with an icon in a collection of packages, hover your mouse over the icon, as

shows. These messages are typically related to data-source connections or data-type problems. Because it can take time to sift through the messages in the Output pane (which is located below the Error List pane in the SSIS designer) during debugging to see what caused a package to fail, SSIS lets you click the Progress tab from the designer to view an outline structure of the package, as Figure 2 shows, and see where the failure took place.

When you debug a package in a Business Intelligence Development Studio environment, you can see which task is running and how far it’s progressed by the background color of the task: Yellow indicates that the task is running, green indicates that the task completed successfully, and red indicates that the task completed with errors. Next to Data Flow tasks, you also get a count of the rows that have been processed by the task. You might notice that SSIS often runs multiple tasks simultaneously, whereas DTS runs only one task at a time. The pipeline nature of SSIS is one of the core architectural differences between it and DTS.

Debugging Control Flow Tasks

Control Flow tasks control the flow of the package. SSIS’s debugging tools for Control Flow tasks closely resemble those available in any respectable development environment. Breakpoints and the Debug windows can be especially helpful in debugging Control Flow tasks.

Breakpoints tell SSIS to pause execution at the indicated point in the package. When processing encounters a breakpoint in Debug mode, processing pauses, and the Debug windows give you access to additional information such as variable values, call stacks, and status messages. You can set breakpoints at the package, container, task, or Script task level. You can even set breakpoints to pause after a given number of encounters with an event, such as on the tenth iteration of a task in a For Loop construct. Figure 3 shows how you can interrogate an object to reveal its property values during a breakpoint session.

I find the Locals window, which is accessible from the Debug/Windows/Locals menu item, to be the most useful of SSIS’s Debug windows. It displays values for all the variables in the package and even lets you modify variable values, which can be useful when skipping ahead to a later point in the package or simulating certain conditions during package development.

Debugging Data Flow Tasks

Data Flow tasks control how and when data is manipulated in the package. The primary debugging tool available in Data Flow tasks is the data viewer. I use the data viewer during the development of Data Flow tasks to see what the data looks like in the pipeline as it flows from one task to another—usually just before the task that actually loads the data into its destination.

You can add a data viewer between two Data Flow tasks by right-clicking the connector between the tasks, selecting Data Viewers, and then selecting the data viewer you want to use. You can view the data in a grid, chart, scatter plot, or histogram. I usually view data in a grid, as shown in Web Figure 1, but I recommend playing with all four data viewers to get a feel for when you should use each one. Although I typically remove my data viewers before deploying a package in production, you don’t have to do so.

You can modify columns displayed by a data viewer either as you set up the data viewer or after setup. To modify a data viewer after you’ve created it, right-click the connector, select Data Viewers, highlight the data viewer, then click Configure.

Another Data Flow task debugging technique that I use frequently during package development and debugging is the RowCount task. The RowCount task relates only to Data Flow tasks. The RowCount task isn’t usually billed as a debugging tool but can be quite useful as one. The RowCount task simply counts the rows passed through the pipeline and puts the final row count into a variable. I almost always use the RowCount task as my initial data destination because it serves as a way of examining the data, via a data viewer, without actually loading the data anywhere. Because the Row- Count task carries no measurable overhead, it can also be used for baselining or to diagnose performance problems. For more information about using the RowCount task, see the Microsoft white paper

Integration Services: Performance Tuning Techniques” (www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx). 

이 글은 스프링노트에서 작성되었습니다.

 Inserting, Updating or Deleting Data in SQL Server from an Excel Spreadsheet

Problem
I have seen your previous tips (Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server) related to working with Excel and SQL Server data.  The main command used in one of the tips is OPENROWSET.  This has been beneficial for us because in our environment because our business users provide data to us in the form of Excel spreadsheets.  Many of the situations end up translating to INSERT, UPDATE or DELETE code in one or more of our SQL Server databases.  We always upload the data to a table and then begin the process.  Although this process works are you familiar with any other options to directly perform the INSERT, UPDATE or DELETE operations?  Are their any benefits to transitioning our code to another approach with the OPENROWSET command?

Solution
Yes - You are correct the OPENROWSET command can directly support INSERT, UPDATE or DELETE operations as shown in these tips: Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server.  In addition, the OPENROWSET command can also support SELECT statements where a table is joined to the Excel spreadsheet.  Let's work through some examples with the SQL Server 2005 AdventureWorks sample database for each operation with a sample Excel spreadsheet.

Prerequisites

For all of these examples, please do the following:

• Download this Excel (Office 2003) spreadsheet to perform the operations.
• In order to follow the examples, be sure to save the Excel spreadsheet into a directory called C:\MSSQLTips\.
• Review each of the worksheets in the Excel file to see the different data.
• Be sure to close the Excel spreadsheet before running any of the code.
• Review each of the commands below before executing them in your environment.

OPENROWSET Examples

Below are four examples to show some of the flexibility with the OPENROWSET command:

General Analysis

In terms of transitioning your code to the new approach, I have yet to experience any performance issues with 1000's of records, but that seems to be the first concern.  If you test the approach and the overall performance is not an issue, then consider the approach.  It also may be faster to perform a single UPDATE as is the case with the example above versus uploading (INSERT...SELECT) the data and then performing an UPDATE based on the new table.  In addition, by using the commands listed above versus a two step process the overall code may be a little bit cleaner.  A second consideration with SQL Server 2005 is that the Surface Area Configuration setting must be enabled to use this code in either case.  Finally, with either approach be sure to clean up any Excel spreadsheets or temporary tables once you are finished with them.

이 글은 스프링노트에서 작성되었습니다.