SSIS script Task Processing variable

Source: Internet
Author: User
Tags ssis

Using the script task to dynamically build SQL Server Integration Services SSIS package variables
Written by: Hal Hayes

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 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 ).

Name Description Type Value
MDirectory Directory for file output. String C: \ SSIS \ Output
MFilePrefix File name (descriptive name for your organization ). String Vendors
MOutFileName Resulting output file name and path that will be used with the flat file connection. Set a default value (use the current filepath used by the flat file connection object ). String C: \ SSIS \ Output \ Vendors.txt

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.

ReadOnlyVariables MDirectory, mFilePrefix
ReadWriteVariables MOutFileName

Here is an example of our script variables setup.

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:

Public Sub Main ()
'
'Mssqltips: Modify Output File Location
'
Dim sFinalFileNameAndPath As String
Dim sDirectory As String
Dim sFilePrefix As String

SDirectory = Dts. Variables ("mDirectory"). Value. ToString

If Not (sDirectory. EndsWith ("\") Then
SDirectory = sDirectory + "\"
End If

SFilePrefix = Dts. Variables ("mFilePrefix"). Value. ToString

SFinalFileNameAndPath = sDirectory + sFilePrefix + "-" + Month (Now. Date). ToString + Year (Now. Date). ToString + ". txt"

Dts. Variables ("mOutFileName"). Value = sFinalFileNameAndPath

Dts. TaskResult = Dts. Results. Success
End Sub

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 parameter .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 settingDTS. taskresultValue to success. The setting of this value is required for proper operation of the script (the value cocould also be set to failure ).

Now you can closeVSAEnvironment.

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,200 8 the file created wocould be "C: \ SSIS \ Output \ Vendors-22008.txt ".

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.