Fail package with expression

How do I use the expression to force a failure using an If statement to evalute a rowcount variable?

I'd evaluate that variable in a script task instead.|||

Do you have an example of how to do that?

If my rowcount < 1 then I want to fail the package

|||

Ken Augustine wrote:

Do you have an example of how to do that?

If my rowcount < 1 then I want to fail the package

Actually, use an expression. Add the script task and hook up a precedence constraint to it. Set it to evaluate the expression.

For the script code, it's basically the default code that is generated when you add a new script task. The only difference is that you call Dts.Results.Failure instead of success.

Code Snippet

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

Public Class ScriptMain

Public Sub Main()
Dts.TaskResult = Dts.Results.Failure
End Sub

End Class

|||

Can you help me with the if then expression to evalute the variable?

Thanks for your help.

|||

Ken Augustine wrote:

Can you help me with the if then expression to evalute the variable?

Thanks for your help.

Just use:

@.rowcount < 1

That's all you need. When you double click on the connection between the ?Data flow? and the script task, set it to use constraint and expression. Then use the above (copy and paste if your variable is named 'rowcount') in the expression box. Note that I believe the CaSE of the variable name is important.

Extracting info from an exec/sp_executesql command

Hi everyone,

Im trying to do the following code for a simple test on variable (not working but the logic show what i want to get).

Im trying to get informations from an exec/sp_executesql command using a variable declared before the command and use that extracted value in another query later. Am i far from the solution?

Code Snippet

DECLARE @.i int
DECLARE @.sql nvarchar(4000)
SELECT @.sql = 'SELECT @.i = Count(*) from tblTableTest'
exec sp_executesql @.sql
PRINT @.i

You have to declare the list of parameters.

Code Snippet

DECLARE @.i int

DECLARE @.sql nvarchar(4000)

SELECT @.sql = 'SELECT @.i = Count(*) from tblTableTest'

exec sp_executesql @.sql, N'@.i int output', @.i output

PRINT @.i

AMB

Extracting file name

I have a file name in a variable with full path
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF' .
How do I extract the file name, discarding the path. In the
above example I want to get FILENAME.MDF
TIA
Try,
declare @.f varchar(255)
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'
select right(@.f, charindex('\', reverse(@.f)) - 1)
AMB
"Data Cruncher" wrote:

> I have a file name in a variable with full path
> set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF' .
> How do I extract the file name, discarding the path. In the
> above example I want to get FILENAME.MDF
> TIA
>
|||Alejandro Mesa wrote:
> Try,
> declare @.f varchar(255)
> set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'
> select right(@.f, charindex('\', reverse(@.f)) - 1)
I don't think the above one gives me the result I want,
but anyhow you gave me the idea. This one works
set @.datafile = substring(@.filename,
(len(@.filename) - charindex('\',reverse(@.filename)) +
2),
len(@.filename))
it print FILENAME.MDF
|||> I don't think the above one gives me the result I want,
Did you test it?
use northwind
go
declare @.f varchar(255)
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'
select right(@.f, charindex('\', reverse(@.f)) - 1)
go
Result:
FILENAME.MDF
AMB
"Data Cruncher" wrote:

> Alejandro Mesa wrote:
> I don't think the above one gives me the result I want,
> but anyhow you gave me the idea. This one works
> set @.datafile = substring(@.filename,
> (len(@.filename) - charindex('\',reverse(@.filename)) +
> 2),
> len(@.filename))
> it print FILENAME.MDF
>

Extracting file name

I have a file name in a variable with full path
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FIL
ENAME.MDF'.
How do I extract the file name, discarding the path. In the
above example I want to get FILENAME.MDF
TIATry,
declare @.f varchar(255)
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FIL
ENAME.MDF'
select right(@.f, charindex('', reverse(@.f)) - 1)
AMB
"Data Cruncher" wrote:

> I have a file name in a variable with full path
> set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FIL
ENAME.MDF'.
> How do I extract the file name, discarding the path. In the
> above example I want to get FILENAME.MDF
> TIA
>|||Alejandro Mesa wrote:
> Try,
> declare @.f varchar(255)
> set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FIL
ENAME.MDF'
> select right(@.f, charindex('', reverse(@.f)) - 1)
I don't think the above one gives me the result I want,
but anyhow you gave me the idea. This one works
set @.datafile = substring(@.filename,
(len(@.filename) - charindex('',reverse(@.filename)) +
2),
len(@.filename))
it print FILENAME.MDF|||> I don't think the above one gives me the result I want,
Did you test it?
use northwind
go
declare @.f varchar(255)
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FIL
ENAME.MDF'
select right(@.f, charindex('', reverse(@.f)) - 1)
go
Result:
FILENAME.MDF
AMB
"Data Cruncher" wrote:

> Alejandro Mesa wrote:
> I don't think the above one gives me the result I want,
> but anyhow you gave me the idea. This one works
> set @.datafile = substring(@.filename,
> (len(@.filename) - charindex('',reverse(@.filename)) +
> 2),
> len(@.filename))
> it print FILENAME.MDF
>

Extracting file name

I have a file name in a variable with full path
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'.
How do I extract the file name, discarding the path. In the
above example I want to get FILENAME.MDF
TIATry,
declare @.f varchar(255)
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'
select right(@.f, charindex('\', reverse(@.f)) - 1)
AMB
"Data Cruncher" wrote:
> I have a file name in a variable with full path
> set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'.
> How do I extract the file name, discarding the path. In the
> above example I want to get FILENAME.MDF
> TIA
>|||Alejandro Mesa wrote:
> Try,
> declare @.f varchar(255)
> set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'
> select right(@.f, charindex('\', reverse(@.f)) - 1)
I don't think the above one gives me the result I want,
but anyhow you gave me the idea. This one works
set @.datafile = substring(@.filename,
(len(@.filename) - charindex('\',reverse(@.filename)) +
2),
len(@.filename))
it print FILENAME.MDF|||> I don't think the above one gives me the result I want,
Did you test it?
use northwind
go
declare @.f varchar(255)
set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'
select right(@.f, charindex('\', reverse(@.f)) - 1)
go
Result:
FILENAME.MDF
AMB
"Data Cruncher" wrote:
> Alejandro Mesa wrote:
> > Try,
> >
> > declare @.f varchar(255)
> >
> > set @.f = 'C:\DIRECTORY1\DIRECTORY2\DIRECTORY3\FILENAME.MDF'
> >
> > select right(@.f, charindex('\', reverse(@.f)) - 1)
> I don't think the above one gives me the result I want,
> but anyhow you gave me the idea. This one works
> set @.datafile = substring(@.filename,
> (len(@.filename) - charindex('\',reverse(@.filename)) +
> 2),
> len(@.filename))
> it print FILENAME.MDF
>

Extracting data from a variable

hi All,

I have a string varaible passed to the SP something like :

@.var = 'v1#1@.v2#1,2@.v3#1,3,4,5'. Now i have to extract the data from this variable in such a way that :

select * from var_data shud return like this :

ID Role

v1 1

v2 1

v2 2

v3 1

v3 3

v3 4

v3 5

Plz guide me how to achieve this result from the variable.

Thanks in advance :-)

This looks like a pretty good match for Jens Suessmeyer's split function with a CROSS APPLY join. Hang on and I'll get you an example.

Code Snippet

declare @.var varchar(40)
set @.var = 'v1#1@.v2#1,2@.v3#1,3,4,5'

select @.var

select left(a.splitValue, charindex('#', a.splitValue) - 1) as [ID],
b.splitValue as [Role]
from split(@.var, '@.') a
cross apply split(substring(splitValue, charindex('#', splitValue)+1, 99), ',') b

/*
ID Role
-- -
v1 1
v2 1
v2 2
v3 1
v3 3
v3 4
v3 5
*/

MVP Jens Suessmeyer's SPLIT function can be found at this post:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

extracting data from a varchar variable?

Guys.

I have some data like this one:

'Uploadfiles\CompanyID\ProjectCode\Files\File1.doc'

P.S: the values may vary each time.

I need to programmatically extract from that kind of string only file1.doc

Ho can I dynamically do it? any function? statement? etc? please help.

The idea is getting only the string after the last "\"

Thanks
Frdeclare @.t varchar(256)

select @.t = 'Uploadfiles\CompanyID\ProjectCode\Files\File1.doc'

select reverse(substring(reverse(@.t),1,patindex('%\%',reverse(@.t))-1))

Extracting Contents of Object Variable in script Task

Hey Guys,

Im pretty new to Scripting in SSIS, but i have worked a fair bit out already, although i am stuck with the following problem.. If anyone can help out that would be great!!

Ok, i have a package that loops through records in a table, and extracts the detailed lines in a For Each loop. and stores them in a variable called DetailRecordSet with an data type of object.

I have a built a script task so that i can send out an email confirmation for each order, and i want to list the details in a HTML Table in the body of the message ( I have read that the standard send mail task wont support this, but found an example of using .NET code to generate the email message which will support it) .

What i want to know is how do i reference the columns in the Object variable so i can extract each line and add it into my string that i am creating with HTML codes.. I

e:( str = "<TABLE> <TR>" + Cstr(Variable for 1st column in recordset) + " </TR>"

if any can help my, that would be most appriciated.. Ive tried all different things that ive found on the net, but nothing is working.

Thanks in Advance

Scotty

To know how to interact with SSIS object variable's underlying type, you need to know the type itself. That is, the type of the "thing" stored by reference in the SSIS object variable.

What the actual underlying type of the variable stored in the SSIS variable of type object?

System.Windows.Forms.MessageBox.Show(Dts.Variables("DetailRecordSet").Value.GetType().FullName)

The above MessageBox() will display the underlying type in your script task, excepting Com callable wrappers (of type System.__Object).
|||

Sorry for the late reply Jaegd. but have been away for a few days,,

This object type is System.Object. from the variable section i select the data type to be Object.

I appricate your help still, cause i am still stuck

Thanks

Scotty

|||

As per the previous reply, knowing what is in the object is crucial. From your reply it is a COM object, so I'll guess that it is probably an ADO recordset. IN which case take a look here for some links and pointers - http://blogs.conchango.com/jamiethomson/archive/2005/02/08/SSIS_3A00_-Accessing-a-recordset-in-a-script-task.aspx

If that does not help, then help me, as I don't understand how you got there. So what has populated the variable exactly, and what method was used? If it is the For Each loop, what enumerator have you used?