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

Extract color formatted text from code editor?

Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
txThis is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing th
is
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>sql

Extract color formatted text from code editor?

Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
tx
This is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx
|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing this
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>

Expressions in RS..

Hey guys,
I try to hide some sections of my report using the expression editor.
I tryed to find what language the expression editor is but I did not found
it.
Does someone knows any references about expressions in RS?
Thanks!VB.Net. I sometimes open up a VB project to test out expressions before
using them in RS. Same with code behind reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:uJ37Cv$eFHA.580@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I try to hide some sections of my report using the expression editor.
> I tryed to find what language the expression editor is but I did not found
> it.
> Does someone knows any references about expressions in RS?
> Thanks!
>

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"

Expressions

Is there a good guide out there that I can reference for expressions?
Crystal's formula editor seems very strong in comparison, and frankly
if we can't find any decent documentation with good examples, we may
just ditch reporting services for Crystal.
We have several legacy reports in Crystal that have some lengthy
formulas. As we are a medical technology firm, we have many laws we
must follow, and our formulas have to take tons of things into
account.
Can anyone point me towards some decent resources?Hi Rick:
If the formulas and expressions are long and involved, you might want
to consider writing a custom assembly ( a dll written in a .NET
language, like VB.NET or C#).
You can reuse the logic in the assembly in other reports and even
other applications. The methods in the assembly can call each other.
It can open up the entire .NET framework for you, including all the
Math functions, the data structures like lists and hashtables, the
File IO operations, and so on. In Crystal you can't even get close to
this level of abstraction and functionality.
This might be a steeper learning curve compared to the function editor
in Crystal, but it is much more powerful.
If you google for [reporting services custom assembly] you'll see a
number of resources on the internet to help out.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On 15 Oct 2004 05:21:23 -0700, rickb@.wynpartners.com (RickB) wrote:
>Is there a good guide out there that I can reference for expressions?
>Crystal's formula editor seems very strong in comparison, and frankly
>if we can't find any decent documentation with good examples, we may
>just ditch reporting services for Crystal.
>We have several legacy reports in Crystal that have some lengthy
>formulas. As we are a medical technology firm, we have many laws we
>must follow, and our formulas have to take tons of things into
>account.
>Can anyone point me towards some decent resources?

Expression editor on Custom Properties on Custom Data Flow Component

Hi,

I've created a Custom Data Flow Component and added some Custom Properties.

I want the user to set the contents using an expression. I did some research and come up with the folowing:

Code Snippet

IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";

But it doesn't work, if I enter @.[System:ackageName] in the field. It comes out "@.[System:ackageName]" instead of the actual package name.

I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.

Any help would be greatly appreciated!

Thank you

The expression for a component's property is held at the task level. If a property is marked as CPET_NOTIFY, it notifies the task (the data flow which parent's the component), which tells it to generate a new property on which can be set an expression. So to see the expression in the designer look at the properties grid for the data flow task, not the component.

|||Hello Darren,

Thank you for the quick response. But the expression editor doesn't show in the propertiesgrid either.

Could I be missing anything?|||

Are you looking at the properties grid of the DATA FLOW component or at your custom data flow component? Also, did you set the flag darren mentioned above so that the data flow component will know to include this property in it's properties expression list?

|||Ah, yes, I see it now. But it is not what I'm looking for.

Is there now way to set the expresison builder on the component itself? When the expression builder is used on the parent task, I cannot access the incomming rows.

Thank you kindly|||

Why do you think your component will have features over and above that available to Microsoft themselves?

Property expressions are ONLY available at the task level, because they are provided in the task framework.

You have created a property expression, so your mention of incoming rows does not make sense. Property expressions are just that, for the property, but not the value itself, the result will override the value. Just look how they work in the rest of SSIS.

A property that holds a text string that could be parsed as an expression is something different and maybe what you want? Perhaps the Derived Column transform may be easier?

Expression editor (sort of) bug

I've noticed in the expression editor that pasting rich text (like copied
from a web page) preserves the formatting. This makes stuff look really
screwed up in the expression editor. I noticed this copying stuff from this
newsgroup and pasting it into the expression editor.I believe the formatting goes away after you commit it. But be careful
about extra carriage returns/line feeds. They can mess up the code.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Stefan Wrobel" <StefanWrobel@.discussions.microsoft.com> wrote in message
news:8C6BE014-DC4D-400B-9CA6-CBDDFC99B16E@.microsoft.com...
> I've noticed in the expression editor that pasting rich text (like copied
> from a web page) preserves the formatting. This makes stuff look really
> screwed up in the expression editor. I noticed this copying stuff from
> this
> newsgroup and pasting it into the expression editor.

Expression Editor - avg problem

This is my data set

PersonIdType (varchar 20)

153 NewlyEnrolled

58 8

248 Enrolled

85723

I am trying to show an avg for all the numbers in the ‘Type’ field

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, CDbl(Fields!Type.value)))

I get the error “Input string was not in a correct format.”

If I use:

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, 1))

It works and it returns ‘1’

so the problem is CDbl(Fields!Type.value). i'm tring to convert it to a number so it can be used in the avg function.

any idea what I'm doing wrong?

CDbl returns a double from (typically) a floating point number.

How about trying out CInt or StrConv?

|||

CInt, Cdbl, Cdec, Csng.... all do the same thing.

StrConv is for string manipulation, cant use that to convert to a number

|||

What happens when you simply take out Cdbl?

I suppose you get an error stating that you can't average a varchar field?

Try this:

=CStr(CInt(Fields!Type.Value))

|||

What happens when you simply take out Cdbl?

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

=CStr(CInt(Fields!Type.Value))

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I also tried

=CInt(CStr(Fields!Type.Value))

Input string was not in a correct format.

|||I just made a new field that held the numbers and did an average on that. Not what I wanted but it worked.

Expression Editor - avg problem

This is my data set

PersonIdType (varchar 20)

153 NewlyEnrolled

58 8

248 Enrolled

85723

I am trying to show an avg for all the numbers in the ‘Type’ field

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, CDbl(Fields!Type.value)))

I get the error “Input string was not in a correct format.”

If I use:

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, 1))

It works and it returns ‘1’

so the problem is CDbl(Fields!Type.value). i'm tring to convert it to a number so it can be used in the avg function.

any idea what I'm doing wrong?

CDbl returns a double from (typically) a floating point number.

How about trying out CInt or StrConv?

|||

CInt, Cdbl, Cdec, Csng.... all do the same thing.

StrConv is for string manipulation, cant use that to convert to a number

|||

What happens when you simply take out Cdbl?

I suppose you get an error stating that you can't average a varchar field?

Try this:

=CStr(CInt(Fields!Type.Value))

|||

What happens when you simply take out Cdbl?

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

=CStr(CInt(Fields!Type.Value))

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I also tried

=CInt(CStr(Fields!Type.Value))

Input string was not in a correct format.

|||I just made a new field that held the numbers and did an average on that. Not what I wanted but it worked.