Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

Expressions in For Loop Container

Hi,
I am having problem when setting for-each container's EvalExpression as a variable comparison.

I have set this in expression editor
@.[User::Syn] == 1

Editor says that it couldnt convert from System.Bool to System.String.

If I put it in quote, then for-each container says that it needs Bool, not String.

Please help me with this,
Thanks
@.[User::Syn] == "1"|||Syn is of Int32 datatype.
And now it says

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

The data types "DT_I4" and "DT_WSTR" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation "@.[User::SyncStatus] == "0"" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)|||actually, EvalExpression property is string and expression editor must end up with some string value and something == something is basically a boolean.|||So is it User::Syn or User::SyncStatus? If SyncStatus is a DT_I4, then this should work:

@.[User::SyncStatus] == 0

It works for me.|||

Fahad349 wrote:

actually, EvalExpression property is string and expression editor must end up with some string value andsomething == something is basically a boolean.

EvalExpression "must evaluate to a Boolean"|||

Fahad349 wrote:

actually, EvalExpression property is string and expression editor must end up with some string value andsomething == something is basically a boolean.

Why aren't you using the EvalExpression in the editor, not the expression property?

If you are editing the property expressions, then this is what you'd want:

"@.[User::SyncStatus] == 0"

expression syntax for variable

What would be the correct syntax if I wanted to add the following lines into a variable using an expression? The lines should be the first two rows before my XML.

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">

+@.[User::xml_output]

Thanks,

Phil

Sigh... I hope there's an easier way and I'm just missing it myself...

"<?xml version=" + "\x0x22" + "1.0" + "\x0x22" + "encoding=" + "\x0x22" + "UTF-8" + "\x0x22" + "?>
<dataroot xmlns:od=" + "\x0x22" + "urn:schemas-microsoft-com:officedata" + "\x0x22" + ">" +

@.[User::xml_output]|||The reference guide: http://msdn2.microsoft.com/en-gb/library/ms141001.aspx|||

You can just escape the quotation marks:

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<dataroot xmlns:od=\"urn:schemas-microsoft-com:officedata\">"

+@.[User::xml_output]

|||

notalreadytaken wrote:

You can just escape the quotation marks:

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<dataroot xmlns:od=\"urn:schemas-microsoft-com:officedata\">"

+@.[User::xml_output]

Agreed. I must've had another syntax problem when I tried escaping them that way. So, Phil, now you have two options! The way I did it is great for injecting special characters...

expression syntax behavior

I have a variable that I'm storing in a database. The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Thanks,

Phil

tackett wrote:

I have a variable that I'm storing in a database.

Not sure what you mean by this. What database are you storing your variable in?

tackett wrote:

The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Backslash is an escape character. It allows you to refer to values that aren't "normal" (e.g. tab, carriage return, new line etc...). In order that you can use backslashes themselves you need an escape character for that as well - which is what double backslash is. Tht's why it shows up in your Watch window as double slash,.

If I were you, i wouldn't worry about it. Using backslash for escape characters is very common in many development technologies.

-Jamie

|||

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

|||

Well if it were me, job number one would be to get the same SP on all your servers. Its extemely bad practice not to do that.

Not sure sure why its failing though I'm afraid.

-Jamie

|||

I'll see if I can convince the DBA's to do that. I'll keep you posted if that fixes this issue.

Thanks,

Phil

|||

tackett wrote:

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

The double backslash is normal and is happening to escape the backslash which would otherwise indicate a special character sequence such as \t for a tab character. If you want proof, use a script task with a messagebox instead of the local window. This way it will not be escaped.

Windows.Forms.MessageBox.Show(Dts.Variables("myVar").Value.ToString())

Your connection problem on prod is most likely permissions, name resolution, or connectivity.
|||

Does this error mean anything to you guys?

<ROOT><?MSSQLError HResult=\"0x80040e37\" Source=\"Microsoft SQL Native Client\" Description=\"Invalid object name 'exp_a_client_vw'.\"?></ROOT>\r\n

I'm able to get the task to work if it's running an SP, unlike before when I was connecting to a file that had SQL in it and then running that SQL on the variable destination (yes the file location was shared). Well now, it's getting passed the task but my "XML" is garbage. Any help would be greatly appreciated.

Thanks,

phil

|||I seem to recall this being answered in another thread - but if not - your SP or SQL is calling something that depends on "exp_a_client_vw" - but it doesn't exist in the database.

expression syntax behavior

I have a variable that I'm storing in a database. The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Thanks,

Phil

tackett wrote:

I have a variable that I'm storing in a database.

Not sure what you mean by this. What database are you storing your variable in?

tackett wrote:

The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Backslash is an escape character. It allows you to refer to values that aren't "normal" (e.g. tab, carriage return, new line etc...). In order that you can use backslashes themselves you need an escape character for that as well - which is what double backslash is. Tht's why it shows up in your Watch window as double slash,.

If I were you, i wouldn't worry about it. Using backslash for escape characters is very common in many development technologies.

-Jamie

|||

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

|||

Well if it were me, job number one would be to get the same SP on all your servers. Its extemely bad practice not to do that.

Not sure sure why its failing though I'm afraid.

-Jamie

|||

I'll see if I can convince the DBA's to do that. I'll keep you posted if that fixes this issue.

Thanks,

Phil

|||

tackett wrote:

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

The double backslash is normal and is happening to escape the backslash which would otherwise indicate a special character sequence such as \t for a tab character. If you want proof, use a script task with a messagebox instead of the local window. This way it will not be escaped.

Windows.Forms.MessageBox.Show(Dts.Variables("myVar").Value.ToString())

Your connection problem on prod is most likely permissions, name resolution, or connectivity.
|||

Does this error mean anything to you guys?

<ROOT><?MSSQLError HResult=\"0x80040e37\" Source=\"Microsoft SQL Native Client\" Description=\"Invalid object name 'exp_a_client_vw'.\"?></ROOT>\r\n

I'm able to get the task to work if it's running an SP, unlike before when I was connecting to a file that had SQL in it and then running that SQL on the variable destination (yes the file location was shared). Well now, it's getting passed the task but my "XML" is garbage. Any help would be greatly appreciated.

Thanks,

phil

|||I seem to recall this being answered in another thread - but if not - your SP or SQL is calling something that depends on "exp_a_client_vw" - but it doesn't exist in the database.

expression syntax

How would I do a select from a container using the previous container's starttime as a condition in the variable?

select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + @.[System::ContainerStartTime] + "

Thanks,

Phil

Tackett,

I dont know the requirements of your project, but try to run the SQL statment inside a OLEDB command and define @.[System::ContainerStartTime] as parameter.

For example, you can create your sql statment as stored procedure in database and inside OLEDB Command write in SQL command :

EXEC SP_NAMESTOREDPROCEDURE ?

And in the second tab link the parameter to your system variable.

If you want i can show you an example.

Regards,

Pedro

|||

Isn't there a way to refer to the containerstarttime in a execute sql task via expression syntax? That's what I'm trying to do.

select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= ' + (DT_STR,50)@.[System::ContainerStartTime] + '

|||

Sorry Tacket, I was "sleeping"...

Try this post to help your problem...

Tomorrow morning I will think better about this.

Regards,

Pedro

|||

NP. Actually I need to do a BETWEEN where time between 'previous container' and 'current container'. I'm sure that involves system variables and namespaces .

Thanks,

Phil

|||

Check this:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Ok, so I need to create a variable and then evaluate it as an expression and put the code in there, correct? NP, accept, I can't get it to evaluate in the "evaluate as expression" part. Here's what I have so far in the expression builder and it's not working..

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50)@.[System::ContainerStartTime] + "

Any help?

Thanks,

Phil

|||

One thing I noticed is that your DT_STR type conversion is missing the thrid parameter (Code Page)

(DT_STR,50) should be (DT_STR,50,1252) -- Assuming you are using the standard 1252 code page.

|||

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50,1252)@.[System::ContainerStartTime] + "" failed. The token """ at line number "5", character number "68" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

(Microsoft.DataTransformationServices.Controls)

BUTTONS:

OK

Here is my code in the variable's expression builder:

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50,1252)@.[System::ContainerStartTime] + "

|||Well I took off the + " and now it's working |||

Working syntax for those who care:

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] between '" + (DT_STR,50,1252)@.[System::ContainerStartTime] + "' and '" + (DT_STR,50,1252)GETDATE() + "'"

expression syntax

Hi,

I'm having some trouble with this syntax:

@.[User::xml_output] != "<ROOT></ROOT>"

I have a variable that I KNOW is = '<ROOT></ROOT>' and I've set up a precendence constraint to only go to the next step if the above not true, correct?

Do I need to convert the right side of the equation to a string or something first? Does anybody know that syntax off hand?

Thanks,

Phil

I don't know what the problem is but maybe try this:

@.[User::xml_output] != (DT_STR, <length>, 1252)"<ROOT></ROOT>"

I'd be surprised if this worked tho!

-jamie

|||Looks good to me. CAsE will matter and so will any spaces, of course.

Make sure you don't have another variable of the same name with a different scope.

And how do you KNOW the variable is populated with what you think it is? Have you debugged with a breakpoint set using the on PostExecute() event of the task before the "next step" and then looked at the locals window?|||

Jamie,

Is there a way to remove trailing leading spaces from a variable in expression syntax? I think that may be the issue.

Thanks,

Phil

|||

tackett wrote:

Jamie,

Is there a way to remove trailing leading spaces from a variable in expression syntax? I think that may be the issue.

Thanks,

Phil

trim(@.[User::Variable]) != "string"

This will do leading and trailing|||

Good stuff guys. That really helped. Turns out there were a few extra characters in there. Thanks Phil and Jamie.

Expression does not notice Global Variable's new value

I'm trying to do something very simple, and having a tough time with it.

I've got a Global Variable that gets a string value assigned to it in a Script Task, and then I need to access that value in Execute Process Task Expression. When running, by the time it gets to the Process Task, the global variable's value in the expression is still blank, even though a breakpoint on the task shows that it does have a value.

What am I doing wrong? This seems too simple to give me this much problem.

Make sure that you don't have another variable defined at the Script Task scope level.

Also, can you share the code you're using to set the value of the variable?|||

all variables are package scope level. I've got two variables for the script task set in the ReadWriteVariables area; pszFinCycle and ZipFile. ZipFile is the variable that has the value that will be used later in the Process Task.

Code Snippet

Dts.Variables("pszFinCycle").Value = Format(Now, "yyyyMMdd").ToString

Dts.Variables("ZipFile").Value = "EXTRACT" + Dts.Variables("pszFinCycle").Value.ToString + ".ZIP"

Dts.TaskResult = Dts.Results.Success

|||

DrinkGreen wrote:

all variables are package scope level.

Right. Make sure that when the script task is highlighted that you don't have ANOTHER variable defined at that scope. You can have multiple variables of the same name in a package that have different scopes.

The code looks good to me.|||Or that the Execute Process Task doesn't have another variable of the same name, but different scope.

Expression - Comparing a variable

Hi,

I have a variables in SSIS:

- object MyObj

How can I write an expression that checks if MyObj is NULL or NOT NULL?

Thank you.

If it's an object type, you'll have to shred it with a foreach loop container first. A script task would likely work as well, but coding is required.

Are you wanting to test if the object is EMPTY or NULL? Two different things.|||

"Are you wanting to test if the object is EMPTY or NULL? Two different things."

You′re absolutely right.

My Data Flow's Record Set Destination puts some row data in MyObj variable.

How can I How can I write an expression to see if MyObj contains some row or none at all (how can I see if it′s empty or not empty?

Thanks once again Mr.Phil Brammer!

|||

The code here might help:

Recordsets instead of raw files

(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

-Jamie

|||A row count transformation will store the count of rows that pass through it into an integer variable that you can later work with. Won't that work for you instead?|||

Yes, it worked.

Thank you once again!