{"id":496,"date":"2025-07-16T12:54:29","date_gmt":"2025-07-16T03:54:29","guid":{"rendered":"https:\/\/sejiwon.com\/?p=496"},"modified":"2025-07-16T12:54:29","modified_gmt":"2025-07-16T03:54:29","slug":"%ed%8c%8c%ec%9b%8c%ec%89%98%ec%9d%b4%eb%82%98-ssis%ec%99%80-%ec%97%b0%eb%8f%99%ed%95%9c-%ec%9e%90%eb%8f%99%ed%99%94","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=496","title":{"rendered":"\ud30c\uc6cc\uc258\uc774\ub098 SSIS\uc640 \uc5f0\ub3d9\ud55c \uc790\ub3d9\ud654"},"content":{"rendered":"\n<p>MSSQL\uc758 <strong>\ud30c\ud2f0\uc158\ubcc4 \uc555\ucd95\uc744 \uc790\ub3d9\ud654<\/strong>\ud558\ub824\uba74, \ub2e4\uc74c \ub450 \uac00\uc9c0 \uc811\uadfc \ubc29\uc2dd\uc774 \uc790\uc8fc \uc0ac\uc6a9\ub429\ub2c8\ub2e4:<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udee0\ufe0f 1. <strong>PowerShell\uc744 \uc774\uc6a9\ud55c \uc555\ucd95 \uc790\ub3d9\ud654<\/strong><\/h2>\n\n\n\n<p>PowerShell\uc740 SQL Server Agent \uc791\uc5c5\uacfc\ub3c4 \uc27d\uac8c \uc5f0\ub3d9\ub418\uba70, Windows \ud658\uacbd\uc5d0\uc11c \uc190\uc27d\uac8c \uc608\uc57d \uc2e4\ud589\uc774 \uac00\ub2a5\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 PowerShell \uc2a4\ud06c\ub9bd\ud2b8 \uc608\uc81c (\ub3d9\uc801 \uc555\ucd95)<\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\"><code># \ubcc0\uc218 \uc124\uc815\n$serverName = \"localhost\"\n$databaseName = \"YourDatabase\"\n$tableName = \"YourTableName\"\n\n# SQL \uc555\ucd95 \uba85\ub839 \ub3d9\uc801 \uc0dd\uc131\n$query = @\"\nDECLARE @sql NVARCHAR(MAX) = '';\nSELECT \n    @sql += '\n    ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) + '\n    REBUILD PARTITION = ' + CAST(p.partition_number AS VARCHAR) + '\n    WITH (DATA_COMPRESSION = PAGE);'\nFROM sys.partitions p\nJOIN sys.objects o ON p.object_id = o.object_id\nWHERE o.name = '$tableName' AND p.index_id IN (0,1)\nGROUP BY o.schema_id, o.name, p.partition_number;\n\nEXEC sp_executesql @sql;\n\"@\n\n# SQL \uc2e4\ud589\nInvoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $query\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 \uc0ac\uc6a9 \ubc29\ubc95:<\/h3>\n\n\n\n<ul>\n<li>\uc704 \uc2a4\ud06c\ub9bd\ud2b8\ub97c <code>.ps1<\/code> \ud30c\uc77c\ub85c \uc800\uc7a5 (\uc608: <code>CompressPartitions.ps1<\/code>)<\/li>\n\n\n\n<li><strong>Windows Task Scheduler<\/strong> \ub610\ub294 <strong>SQL Server Agent<\/strong>\uc5d0\uc11c \uc608\uc57d \uc2e4\ud589<\/li>\n\n\n\n<li>\ud544\uc694 \uc2dc PowerShell\uc5d0\uc11c \ud2b9\uc815 \ub0a0\uc9dc \uae30\ubc18 \ud30c\ud2f0\uc158\ub9cc \ud544\ud130\ub9c1\ud558\ub3c4\ub85d \ud655\uc7a5 \uac00\ub2a5<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udce6 2. <strong>SSIS (SQL Server Integration Services)<\/strong> \uc774\uc6a9\ud55c \uc790\ub3d9\ud654<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 \uad6c\uc131 \uac1c\uc694<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>SSIS \uad6c\uc131 \uc694\uc18c<\/th><th>\uc5ed\ud560<\/th><\/tr><\/thead><tbody><tr><td><strong>Execute SQL Task<\/strong><\/td><td>\ud30c\ud2f0\uc158 \ubaa9\ub85d \uc870\ud68c<\/td><\/tr><tr><td><strong>Foreach Loop Container<\/strong><\/td><td>\ud30c\ud2f0\uc158 \ubc88\ud638 \ubc18\ubcf5<\/td><\/tr><tr><td><strong>Execute SQL Task (\ub0b4\ubd80)<\/strong><\/td><td>\uac01 \ud30c\ud2f0\uc158\uc5d0 \uc555\ucd95 \uba85\ub839 \uc2e4\ud589<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 \uad6c\ud604 \uc808\ucc28 \uc694\uc57d<\/h3>\n\n\n\n<ol>\n<li><strong>Execute SQL Task<\/strong>: \ud30c\ud2f0\uc158 \ubc88\ud638 \ubaa9\ub85d \uac00\uc838\uc624\uae30 sql\ubcf5\uc0ac\ud3b8\uc9d1<code>SELECT DISTINCT partition_number FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.YourTableName') AND index_id IN (0, 1)<\/code><\/li>\n\n\n\n<li><strong>Foreach Loop Container<\/strong>: \uc704 \uacb0\uacfc\uc758 \uac01 <code>partition_number<\/code>\uc5d0 \ub300\ud574 \ubc18\ubcf5<\/li>\n\n\n\n<li>\ub0b4\ubd80\uc5d0 \ub610 \ub2e4\ub978 <strong>Execute SQL Task<\/strong>: sql\ubcf5\uc0ac\ud3b8\uc9d1<code>ALTER TABLE dbo.YourTableName REBUILD PARTITION = ? WITH (DATA_COMPRESSION = PAGE);<\/code>\n<ul>\n<li>\ud30c\ub77c\ubbf8\ud130: Foreach\uc5d0\uc11c \uc5bb\uc740 <code>partition_number<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\ud544\uc694 \uc2dc \uc131\uacf5\/\uc2e4\ud328 \ub85c\uae45, \uc774\uba54\uc77c \uc54c\ub9bc \ub4f1 \ucd94\uac00 \uac00\ub2a5<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd04 PowerShell vs SSIS \ube44\uad50<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\ud56d\ubaa9<\/th><th>PowerShell<\/th><th>SSIS<\/th><\/tr><\/thead><tbody><tr><td>\uc124\uc815 \uac04\ud3b8\uc131<\/td><td>\u2705 \uc26c\uc6c0<\/td><td>\u274c \ube44\uad50\uc801 \ubcf5\uc7a1<\/td><\/tr><tr><td>GUI \uc9c0\uc6d0<\/td><td>\u274c \uc5c6\uc74c<\/td><td>\u2705 \uc788\uc74c<\/td><\/tr><tr><td>\ud655\uc7a5\uc131<\/td><td>\u2705 \uc2a4\ud06c\ub9bd\ud2b8 \uae30\ubc18 \uc720\uc5f0\ud568<\/td><td>\u2705 \ud328\ud0a4\uc9c0 \ub2e8\uc704 \uad00\ub9ac<\/td><\/tr><tr><td>\uc6b4\uc601 \ud658\uacbd \uc81c\uc57d<\/td><td>Windows \ud544\uc694<\/td><td>SQL Server Integration Services \uc124\uce58 \ud544\uc694<\/td><\/tr><tr><td>\ub85c\uae45 \ubc0f \uc54c\ub9bc<\/td><td>\uc678\ubd80 \uc2a4\ud06c\ub9bd\ud2b8 \ud544\uc694<\/td><td>\ub0b4\uc7a5 \uac00\ub2a5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \uc790\ub3d9\ud654 \uc804\ub7b5 \uc694\uc57d<\/h2>\n\n\n\n<ul>\n<li><strong>\uac04\ub2e8\ud55c \uc790\ub3d9\ud654 &amp; \ube60\ub978 \ub3c4\uc785<\/strong> \u2192 PowerShell + Task Scheduler<\/li>\n\n\n\n<li><strong>\ubcf5\uc7a1\ud55c \uc6cc\ud06c\ud50c\ub85c\uc6b0\/\uae30\uc5c5\uc6a9 \uc790\ub3d9\ud654<\/strong> \u2192 SSIS + SQL Server Agent<\/li>\n\n\n\n<li><strong>\ubcf4\ub108\uc2a4<\/strong>: SQL Server Agent\uc5d0\uc11c <strong>PowerShell Job Step<\/strong>\ub3c4 \uc0ac\uc6a9 \uac00\ub2a5!<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>MSSQL\uc758 \ud30c\ud2f0\uc158\ubcc4 \uc555\ucd95\uc744 \uc790\ub3d9\ud654\ud558\ub824\uba74, \ub2e4\uc74c \ub450 \uac00\uc9c0 \uc811\uadfc \ubc29\uc2dd\uc774 \uc790\uc8fc \uc0ac\uc6a9\ub429\ub2c8\ub2e4: \ud83d\udee0\ufe0f 1. PowerShell\uc744 \uc774\uc6a9\ud55c \uc555\ucd95 \uc790\ub3d9\ud654 PowerShell\uc740 SQL Server Agent \uc791\uc5c5\uacfc\ub3c4 \uc27d\uac8c \uc5f0\ub3d9\ub418\uba70, Windows \ud658\uacbd\uc5d0\uc11c \uc190\uc27d\uac8c \uc608\uc57d \uc2e4\ud589\uc774 \uac00\ub2a5\ud569\ub2c8\ub2e4. \u2705 PowerShell \uc2a4\ud06c\ub9bd\ud2b8 \uc608\uc81c (\ub3d9\uc801 \uc555\ucd95) # \ubcc0\uc218 \uc124\uc815 $serverName = &#8220;localhost&#8221; $databaseName = &#8220;YourDatabase&#8221; $tableName = &#8220;YourTableName&#8221; # SQL \uc555\ucd95 \uba85\ub839 \ub3d9\uc801 \uc0dd\uc131 $query\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=496\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[11],"tags":[],"_links":{"self":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/496"}],"collection":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=496"}],"version-history":[{"count":1,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/496\/revisions"}],"predecessor-version":[{"id":498,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/496\/revisions\/498"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=496"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=496"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=496"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}