- #CAN I USE VISUAL STUDIO AND SQL SERVER ON SURFACE GO HOW TO#
- #CAN I USE VISUAL STUDIO AND SQL SERVER ON SURFACE GO FULL#
From the Server name drop-down list, select the name of your SQL Server instance, and then select an authentication type. When the Configur e OLE DB Connection Manager dialog box appears, click the New button to launch the Connection Manager dialog box, shown in Figure 3.
Right-click the Connection Manager s window, and then click New OLE DB Connection, as shown in Figure 2.įigure 2: Creating a new OLE DB connection manager
The next step is to create a connection manager that points to the AdventureWorks2008 database. Adding a Connection Manager to the SSIS Package
#CAN I USE VISUAL STUDIO AND SQL SERVER ON SURFACE GO FULL#
We will be using this variable to store the full result set that we retrieve in our second scenario, and SSIS requires that the variable use the Object type to accommodate the multiple rows and columns. This variable should also be at the package scope. Now create a second variable named EmployeeList. The Execute SQL task will use the variable to store the value it retrieves from the database. (In my case, I’ve stuck with the default name, which is Package.) Next, set the data type to Int32 and the value to 0, as shown in Figure 1. Name the new variable EmpNum, and ensure that the scope is set at the package level, as indicated by the package name. In the Variables window, add a new variable by clicking on the Add Variable icon. If the Variables window is not open, right-click the Control Flow workspace, and then click V ariables. The first variable we’ll create is the EmpNum variable. Our next step, then, is to add a couple variables to our package. We’ll perform both exercises in a single package. Once you’ve set up the table and stored procedures, you can create your SSIS package, if you haven’t already done so. Listing 2: Creating a stored procedure that inserts data into the SSISLog table Listing 1 shows the T-SQL necessary to create the SSISLog table. The table will store the value that’s returned by the first Execute SQL task. Setting Up Your Environmentīefore adding components to your SSIS package, you should first add a table and two stored procedures to the AdventureWorks2008 database. In articles to follow, you’ll see how you can use that variable in other SSIS components, such as the Foreach Loop container. For now, all we’ll do is use this task to save the result set to variable. This represents the third Execute SQL task we’ll be adding to our control flow. The second scenario uses a single Execute SQL task to retrieve a multi-row result set, also known as a full result set. The second Execute SQL task will pass that value into a stored procedure that inserts the row into a different table. That value is returned by the task as a single-row result set. The first task retrieves a single value from a table in the AdventureWorks2008 database. In the first scenario, we’ll use two Execute SQL tasks. This article walks you through two different scenarios for working with variables, parameters, and result sets.
#CAN I USE VISUAL STUDIO AND SQL SERVER ON SURFACE GO HOW TO#
(In the previous article in this series, “ Introducing Variables,” I explained how to work with variables, so refer back to that article if you need help.) In this article, we look at how to use those variables and parameters in conjunction with the Execute SQL task in order to transfer that data. When using the Execute SQL task to return a result set, you must also implement the necessary variables and parameters to pass data into and out of the T-SQL statement called by the task. The task is especially useful for returning result sets that can then be used by other components in your SSIS package. The Execute SQL task is one of the handier components in SQL Server Integration Services (SSIS) because it lets you run Transact-SQL statements from within your control flow. SSIS Basics: Using the Execute SQL Task to Generate Result Sets - Simple Talk Skip to content
0 kommentar(er)
