Bora.Choi

다음과 같이 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). 

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