[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

SSIS script task

Im new to the script task in SSIS so if you give me code which I would need I also need you to tell me exactly where to put it please... I have a OLE DB Source Editor that runs the code below and puts the result into a table in SQL(another OLD DB Source).. The only thing I have inbetween the two OLE DB Source editors now is a data conversion transformation editor..... I need to add something else in between that checks the sum of all [current_onhand] and if 0 then fail the package.. If sum [current_onhand] <1 then..I want to stick this in between the 2 OLE DB Sources.. So basically check my condition and if the sum of the column is <1 dail the package
0
cheryl9063
Asked:
cheryl9063
  • 14
  • 8
1 Solution
 
cheryl9063Author Commented:
fail the package sorry..
0
 
cheryl9063Author Commented:
Help?
0
 
8080_DiverCommented:
uh . . . What "code below"?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
cheryl9063Author Commented:
Sorry..code attached.. This is on an execute SQL task and is pulling from an Oracle database.. I need to put the result in a variable and then pass to a script task I think.. In the script task I need it to evaluate my variable.. If my variable(lets calls it COH) If COH is <1 then fail if COH>0 go to next step..
SELECT     SUM(CURRENT_ONHAND) AS COH
FROM         SFA.Z_ITEM_SUPPLY

Open in new window

0
 
8080_DiverCommented:
If I were you, I would put the SQL in a stored proc that accepts some parameters (if needed) and returns at least one parameter (the COH).  Then you can more easily call the SP and get back your answers.

In any case, once you get the value into the variable, you can designate the variable (Current_OnHand?) as a Read Only variable when setting up the Script Task.  Within the Script Task, you would need to code (using VB.Net for SSIS 2005 or C# for SSIS 2008) an if statement with the then and else clauses setting the Dts.TaskResult to Dts.Results.Success or Dts.Results.Failure.

You may want to view the following:

SSIS Variables and Expressions

 Using Script Task and Components in SQL Server Integration Services
0
 
cheryl9063Author Commented:
Can I create an SP in a SQL task in SSIS? This pulls from an Oracle connection and I'm not a PS/SQL developer just T-SQL
0
 
8080_DiverCommented:
Ah, so!  In that case, I would set up the SSIS package with a Data Flow Task that has the source pulling from the Oracle databse and the destination inserting into a staging table in your SQL Server database.  Once the data is in the staging table, you can operate on it in the SQL Server world much easier and you can create a stored procedure (not in the SSIS package but in the database ;-) that performs the task I suggested earlier.

The PL/SQL to SELECT the data you want is going to look almost (if not exactly) like the T-SQL you would use.
0
 
cheryl9063Author Commented:
Sorry just need to clarify something.. Wouldn't it be better to just populate a variable in SSIS with the results of my query and then evaluate the variable to see if its >0?
0
 
8080_DiverCommented:
Sorry I was rather expecting that you would be needing more inforamtion from the data.  If you only need the one value (and you don't need to know anything else about any individual entries in the source table), then yes, executing an Execute SQL Task with the SELECT statement like the following:
SELECT @COH = SUM(CURRENT_ONHAND)
FROM SFA.Z_ITEM_SUPPLY

Open in new window


You would need to set the parameter mapping so that you map your Current_OnHand SSIS variable to the @COH [parameter in the query.  Once that is done, then you would still need the Script Task.
0
 
cheryl9063Author Commented:
Yeah, so the script task is the BIG problem.. I wish there was a class I could take or a book I can read to learn that.. I know a little VB and would prefer that route.. Can you write me a simple VB script task script that checks the @coh variable for <1 and fails the package if that is true?
0
 
8080_DiverCommented:
Piece of cake; however, the @COH parameter will need to have its value mapped to a User::CurrentOnHand SSIS Variable.  Try the following:

if Dts.Variables("User::CurrentOnHand").Value.Equals(0) then
     Dts.TaskResult = Dts.Results.Failure
else
    Dts.TaskResult = Dts.Results.Success
end

Open in new window

0
 
cheryl9063Author Commented:
I'm getting all kinds of errors when I simply paste your code into the VBScript task editor(see my screen print)
example.bmp
0
 
cheryl9063Author Commented:
Also, how do I check to make sure my variable is being populated?
0
 
8080_DiverCommented:
Is this your first SSIS Package or just your first Script Task?

Did you look at the links I sent you?



0
 
cheryl9063Author Commented:
I'm looking now.. I have done a few simple ssis packages that pull data from one database and populate another or create spreadsheets.. But no vbscript task
0
 
cheryl9063Author Commented:
Watched videos thanks! It did not address problem below though:


I put this in a VBSCRIPT editor and get the error below
Public Sub Main()
        If Dts.Variables("User::COH").Value.Equals(0) Then
            Dts.TaskResult = Dts.Results.Failure
        Else
            Dts.TaskResult = Dts.Results.Success
        End If
        Dts.TaskResult = ScriptResults.Success
    End Sub


Error      1      'Results' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.      C:\Documents and Settings\csmith\Local Settings\Temp\aa\SSIS\85256562f4cd4259a2bd29a1253c01af\ScriptMain.vb      39      30      st_9c94ac60e2914f0b9043db4d66c290de
0
 
8080_DiverCommented:
If you are using SSIS 2008,you need to use C# and the code is differnt.  You asked for VB.Net scripting and that is what I provided; however, that is only applicable for SSIS 2005.  

I think the equivalent in C# would be:
if (Dts.Variables["User::COH"].Value.Equals0) == true)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
else
{
Dts.TaskResult = (int)ScriptResults.Success;
}

Open in new window

0
 
cheryl9063Author Commented:
I dont want C.. I need VB.net for Visual Studio 2008
0
 
cheryl9063Author Commented:
vbscript sorry
0
 
8080_DiverCommented:
Are you putting this in an SSIS Script Task?  I was under the distinct impression that BIDS 2008 defaulted to C# for Script Tasks. :-/
0
 
cheryl9063Author Commented:
NO.. See my code attached
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

	Enum ScriptResults
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
	End Enum
	

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts property. Connections, variables, events,
	' and logging features are available as members of the Dts property as shown in the following examples.
	'
	' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
	' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
	' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
	'
	' To use the connections collection use something like the following:
	' ConnectionManager cm = Dts.Connections.Add("OLEDB")
	' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
	'
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Help, press F1.

	Public Sub Main()
        If Dts.Variables("User::COH").Value.Equals(0) Then
            Dts.TaskResult = Dts.Results.Failure
        Else
            Dts.TaskResult = Dts.Results.Success
        End If
        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

Open in new window

0
 
cheryl9063Author Commented:
Results in error

Error      1      'Results' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.      C:\Documents and Settings\csmith\Local Settings\Temp\aa\SSIS\b36b4beb85664b4bb5d83e9963f10ab1\ScriptMain.vb      39      30      st_9c94ac60e2914f0b9043db4d66c290de
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 14
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now