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

External columns from Sybase stored procedures

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

I since learned that the OLE DB data source also has a DelayValidation property, but I still see the same errors with this enabled.
|||Well I think I answered my own question. After switching the connection from the "Native\Sybase" type to ".Net Providers\Sybase" and changing the OLE DB connection to a DataReader I found a new option on the DataFlow property that allowed me to change the sql through an expression at runtime. I may have also needed to turn off the ValidateExternalMetadata property too, not sure yet.

External columns from a Sybase stored procedures

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

I since learned that the OLE DB data source also has a DelayValidation property, but I still see the same errors with this enabled.
|||Well I think I answered my own question. After switching the connection from the "Native\Sybase" type to ".Net Providers\Sybase" and changing the OLE DB connection to a DataReader I found a new option on the DataFlow property that allowed me to change the sql through an expression at runtime. I may have also needed to turn off the ValidateExternalMetadata property too, not sure yet.

Extended Procedure

Does anyone know any undocumented extended procedures that can red SQL
scripts from a text file and execute it. The SQL scripts contain multiple
batches (contain key word 'GO', which will not be recognized by the regular
EXECUTE statement).
Thanks,
LijunHave a look at oSql in BooksOnLine.
Andrew J. Kelly SQL MVP
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:OHF8MBgYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Does anyone know any undocumented extended procedures that can red SQL
> scripts from a text file and execute it. The SQL scripts contain multiple
> batches (contain key word 'GO', which will not be recognized by the
> regular
> EXECUTE statement).
> Thanks,
> Lijun
>

Extended Procedure

Does anyone know any undocumented extended procedures that can red SQL
scripts from a text file and execute it. The SQL scripts contain multiple
batches (contain key word 'GO', which will not be recognized by the regular
EXECUTE statement).
Thanks,
Lijun
Have a look at oSql in BooksOnLine.
Andrew J. Kelly SQL MVP
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:OHF8MBgYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Does anyone know any undocumented extended procedures that can red SQL
> scripts from a text file and execute it. The SQL scripts contain multiple
> batches (contain key word 'GO', which will not be recognized by the
> regular
> EXECUTE statement).
> Thanks,
> Lijun
>

Extended Procedure

Does anyone know any undocumented extended procedures that can red SQL
scripts from a text file and execute it. The SQL scripts contain multiple
batches (contain key word 'GO', which will not be recognized by the regular
EXECUTE statement).
Thanks,
LijunHave a look at oSql in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:OHF8MBgYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Does anyone know any undocumented extended procedures that can red SQL
> scripts from a text file and execute it. The SQL scripts contain multiple
> batches (contain key word 'GO', which will not be recognized by the
> regular
> EXECUTE statement).
> Thanks,
> Lijun
>

Expressions as a field value in a table

Is there a way to store an expression inside a table, and have the expression
being pulled from the table to execute during run time? I want to dynamically
create an expression based on the value of a column of the table. Using the
IIF statement will work but it's going to be a very long nested IIF.... and
that brings to the second question -> is there a limit for the length of an
expression?
Thanks in advance.There is no limit on the length of an expression (other than limits imposed
by the VB.NET compiler).
In your case, you may want to look at alternatives to the IIF function:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
In addition, you may want to consider writing a function in custom code or
as custom assembly that contains your logic. You can then reuse the function
from RDL expressions.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"g3kamdbs" <g3kamdbs@.discussions.microsoft.com> wrote in message
news:2A1DC075-220C-4494-AF82-157760B3EBD5@.microsoft.com...
> Is there a way to store an expression inside a table, and have the
> expression
> being pulled from the table to execute during run time? I want to
> dynamically
> create an expression based on the value of a column of the table. Using
> the
> IIF statement will work but it's going to be a very long nested IIF....
> and
> that brings to the second question -> is there a limit for the length of
> an
> expression?
> Thanks in advance.|||Or a User Defined Function that the Stored Procedure calls.
GeoSynch
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uO3ZYBwvFHA.252@.TK2MSFTNGP09.phx.gbl...
> There is no limit on the length of an expression (other than limits imposed by
> the VB.NET compiler).
> In your case, you may want to look at alternatives to the IIF function:
> * =Choose(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
> * =Switch(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
> In addition, you may want to consider writing a function in custom code or as
> custom assembly that contains your logic. You can then reuse the function from
> RDL expressions.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "g3kamdbs" <g3kamdbs@.discussions.microsoft.com> wrote in message
> news:2A1DC075-220C-4494-AF82-157760B3EBD5@.microsoft.com...
>> Is there a way to store an expression inside a table, and have the expression
>> being pulled from the table to execute during run time? I want to dynamically
>> create an expression based on the value of a column of the table. Using the
>> IIF statement will work but it's going to be a very long nested IIF.... and
>> that brings to the second question -> is there a limit for the length of an
>> expression?
>> Thanks in advance.
>

Expression Constraint and Precedence

I have a flow like this:

Container A
|
|
++
| |
| Container B
| |
| |
Container C That Both Connect To
I want to conditionally execute Container B based on a variable. I did this with a Precedence constraint on the line from A to B.

In this case however if it doesn't execute Container C doesn't execute. This is bad since Container C should always execute.

I changed the precedence constraint of B to C to be a logical Or and now Container C starts executing while Container B is executing! Not what I am looking for.

How do I do this? I thought about disabling Container B using an expression on the container but that seemed ugly.

Thanks,

ChrisHi Chris,
Wow, this is a weird one! I've had some fun trying to work this out

1) Put B into a sequence container. Lets call our new sequence container "D".
2) Put an OnSuccess precedence constraint from A to D
3) Put an OnSuccess precedence constraint from A to C
4) Place a script task into D. It doesn't need any code in it because its not going to do anything
5) Put an expression precedence constraint from the script task to B.

B will execute depending on whatever you have in the expression on the precedence constraint (which is what you want) but C will always execute regardless!

You can download a demo of this solution from here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20050727PrecedenceConstraintanomoly.zip
-Jamie

P.S. The link to your blog (www.vergentsoftware.com/blog/ckinsman) doesnt work. Well it didn't 30 seconds ago anyway.|||Put A and B into a sequence.
Put a precedence constraint between the sequence and C|||Thanks for the blog tip. I left out a letter.

Thanks also for the solution!