AdventureWorks inserting records to JobCandidates example

Hi
re: AdventureWorks example
Are there source code to how the
HumanResources.JobCandidate
table was created?
(I would like to look at the sample code for INSERTing)
thanks
joyceWhat exactly do you want to know about?
Thanks
Michael
PS: I don't think we have sample code for specifically this table...
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169588888.525240.259000@.j27g2000cwj.googlegroups.com...
> Hi
> re: AdventureWorks example
> Are there source code to how the
> HumanResources.JobCandidate
> table was created?
> (I would like to look at the sample code for INSERTing)
> thanks
> joyce
>|||Actually, what I am really about is namespace...
So I thought I should take a look at how the JobCandidates table was
created in AdventureWorks.
I understand targetnamespace, and
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
I don't really understand default namespace such as
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
"
What is the purpose of the def ns?
And also, what is the purpose of the schema collection? And how come a
schema collection can contain multiple schema.
And what is the advantage of typed vs untyped xml in sql server. Also,
does Sql server support any other types of schemas?
I'm still slowly going through the books online material, but it'd be
nice if all these questions are all answered at once.
Thank you, this is a great usenet group,
Joyce
On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> What exactly do you want to know about?
> Thanks
> Michael
> PS: I don't think we have sample code for specifically this table...
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169588888.5252
40.259000@.j27g2000cwj.googlegroups.com...
>
>
>
>
>|||See inline below.
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169736748.697916.238950@.s48g2000cws.googlegroups.com...
> Actually, what I am really about is namespace...
> So I thought I should take a look at how the JobCandidates table was
> created in AdventureWorks.
> I understand targetnamespace, and
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> I don't really understand default namespace such as
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resu
me"
> What is the purpose of the def ns?
Default namespaces are just a syntactic convenience in that you do not have
to write a prefix (this is true for XML documents and XQuery, but not XPath
1.0 where no support for default namespace exists, you have to define a
prefix for the query).
Namespaces per se are just scoping mechanisms to scope a name to a space
that can have different semantics associated than the same name in a
different namespace... e.g., river:bank vs finance:bank.

> And also, what is the purpose of the schema collection? And how come a
> schema collection can contain multiple schema.
A schema collection is a meta data object in SQL Server that allows you to
combine multiple schemas in a logical unit to constrain and type an XML data
type instance with. It also introduces a way that allows you to have more
than one version of the same schema within your database. Note that a single
schema defines structural and some semantic constraints for names within a
namespace (including the non-namespace). However not every document having
data with namespaces needs to have a schema.

> And what is the advantage of typed vs untyped xml in sql server. Also,
> does Sql server support any other types of schemas?
Untyped XML gives you the advantage of complete flexibity of your structure
and no additional validation cost.
Typed XML gives you semantical assurances over the structure of the data,
provides error detection at compile time in your queries (using static
typing), and can be used for some storage and query optimizations.
SQL Server has only limited support for DTDs and other wise does not support
other types of schemas.

> I'm still slowly going through the books online material, but it'd be
> nice if all these questions are all answered at once.
> Thank you, this is a great usenet group,
> Joyce
> On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>

AdventureWorks inserting records to JobCandidates example

Hi
re: AdventureWorks example
Are there source code to how the
HumanResources.JobCandidate
table was created?
(I would like to look at the sample code for INSERTing)
thanks
joyce
What exactly do you want to know about?
Thanks
Michael
PS: I don't think we have sample code for specifically this table...
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169588888.525240.259000@.j27g2000cwj.googlegr oups.com...
> Hi
> re: AdventureWorks example
> Are there source code to how the
> HumanResources.JobCandidate
> table was created?
> (I would like to look at the sample code for INSERTing)
> thanks
> joyce
>
|||Actually, what I am really confused about is namespace...
So I thought I should take a look at how the JobCandidates table was
created in AdventureWorks.
I understand targetnamespace, and
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
I don't really understand default namespace such as
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"
What is the purpose of the def ns?
And also, what is the purpose of the schema collection? And how come a
schema collection can contain multiple schema.
And what is the advantage of typed vs untyped xml in sql server. Also,
does Sql server support any other types of schemas?
I'm still slowly going through the books online material, but it'd be
nice if all these questions are all answered at once.
Thank you, this is a great usenet group,
Joyce
On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> What exactly do you want to know about?
> Thanks
> Michael
> PS: I don't think we have sample code for specifically this table...
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169588888.525240.259000@.j27g2000cwj.g ooglegroups.com...
>
>
|||See inline below.
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169736748.697916.238950@.s48g2000cws.googlegr oups.com...
> Actually, what I am really confused about is namespace...
> So I thought I should take a look at how the JobCandidates table was
> created in AdventureWorks.
> I understand targetnamespace, and
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> I don't really understand default namespace such as
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"
> What is the purpose of the def ns?
Default namespaces are just a syntactic convenience in that you do not have
to write a prefix (this is true for XML documents and XQuery, but not XPath
1.0 where no support for default namespace exists, you have to define a
prefix for the query).
Namespaces per se are just scoping mechanisms to scope a name to a space
that can have different semantics associated than the same name in a
different namespace... e.g., river:bank vs finance:bank.

> And also, what is the purpose of the schema collection? And how come a
> schema collection can contain multiple schema.
A schema collection is a meta data object in SQL Server that allows you to
combine multiple schemas in a logical unit to constrain and type an XML data
type instance with. It also introduces a way that allows you to have more
than one version of the same schema within your database. Note that a single
schema defines structural and some semantic constraints for names within a
namespace (including the non-namespace). However not every document having
data with namespaces needs to have a schema.

> And what is the advantage of typed vs untyped xml in sql server. Also,
> does Sql server support any other types of schemas?
Untyped XML gives you the advantage of complete flexibity of your structure
and no additional validation cost.
Typed XML gives you semantical assurances over the structure of the data,
provides error detection at compile time in your queries (using static
typing), and can be used for some storage and query optimizations.
SQL Server has only limited support for DTDs and other wise does not support
other types of schemas.

> I'm still slowly going through the books online material, but it'd be
> nice if all these questions are all answered at once.
> Thank you, this is a great usenet group,
> Joyce
> On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
sql

Advantage of using a Data Source in a project

I am new to SSIS.

I came across the following article:

http://technet.microsoft.com/en-us/library/ms139848(SQL.90).aspx

It states that:

"A data source can be defined one time and then referenced by connection managers in multiple packages. You use a data source object in a package by adding a connection manager that references the data source object to the package. There is no dependency between a data source and the connection managers that reference it."

I have created a data source (DS1) and set it to point to a database say DB1. In the connection managers area, I create a connection manager CM1 using the datasource DS1.Now I edit DS1 to point to a different database DB2. When I open CM1 however it’s still pointing to DS1.

I guess this is because it’s said that there is no dependency between the connection manager and data source. My question is what exactly is the advantage of using a data source?

Priya

AFAIK, it is used only as a template of sorts for a data source connection. Other than that, I don't really know why one would use it. I have never used them.

Maybe someone else can shed some light onto why you should use it.|||

Hi Priya,

I went through the article and what i understood is:

"A datasource is created outside the reference of a package" , means you can share it across many packages by pointing your connection manger to the datasource you just created.Consider this scenario where you have 10 packages pointing to same source then you may use a Datasource so that all connection managers point ot same datasource.Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently.

Note: Please correct me if I am wrong.

|||

When I started with SSIS , I also wanted to use Data source, since it seemd reasonable to reuse the Data source in various packages. Until you want to use package configuration files to store your connectionstrings. Then it is no longer possible to use datasources. From now on I do not use Data sources any longer. I am using package configuration files that contain the connctions strings of my connections, and in my packages I use the appropriate configuration file. I also use environment variables to proint to my package configuration files. This allows for a somewhat easier deployment of packages on developer and production servers.

|||This is the correct way of handling environment differences with SSIS.
As said, data sources are for design time.

A nice little "feature" of data sources: Delete it and it deletes all your connections which are derived from it.|||Hi all,

Thanks for ur replies.
I'v used configuration file to configure connection manager before and you can't configure data source with it.

Unni, as you'v said that:
"Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently."

Did you try out updating the datasource to point to a different source? Does it get reflected in the connection managers? As i've mentioned in the 1st post the connection manager was still pointing to the older db in my case.
Priya

|||

Let me clarify why I am using configuration files and not datas sources:

- datasources are fine if you always edit your package with visual studio as a developer, no matter where your packages are deployed.

- I have to design a package on a development server where I have full access. but my package will be deployed in a production environment where I will have very little access. The sysadmin will now how to make the enviroment variable en how to edit the configuration file with notepad, those are all the tools he will need. The ssis packages will be restored on the production server via backup restore.

So as far I am concerned this way of working is SOX compliant, and therefore I do not use datasources.

|||In general, I think the consensus among most of the regular posters on the forums is that using data sources is more trouble than it is worth. If updating a data source actually updated all the connection managers derived from it, there might be a benefit to using them. But each connection manager maintains its own copy of the connection string, so there is not much value added by using the data source. Plus, the messages to Synchronize Connection Strings are really annoying |||

Hi,

"Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently."

I have not tried this, I am using configuration files in my package.

|||Yes, if you open each and every package in the project, it will prompt you to change the connect string. It doesn't automatically propagate the change. Configurations are a much easier way of getting the same result.

Advanced SQL generations options

Advanced SQL generations options:

generate INSERT, UPDATE, and DELETE statements is all greyed out?

in my sql data source control.?

I have made a brand new instance with sql server management express...have I missed something?

Does the table have a Primary key defined?|||

Sorry about being slow to get back

NO,

|||

Thankyou very much Mr Wellens.....

I just put in a primary key into table and all working fine.

Does that mean you cant insert into any table without it having a primary key.

many many thanks

|||

>>Does that mean you cant insert into any table without it having a primary key.

Well YOU can, but ASP.Net wants a primary key.

If there was no primary key, the ASP.Net code could get very confused about what row needs to be updated or deleted.

Advanced Editor for DataReader Source / SQLServer 2005

I want to import data from a remote MySql Database

So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:

Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)

the sql string is very simple, this should not be the problem:

SELECT Objektnr FROM m02a_tblObjektstamm

Thank you very much for your support

Martin

Could you try to run this query through this provider using any other client?

Thanks.

|||I has the same problem!
if you found the solutions,please tell me.Thanks for you.|||As Bob was asking above, do you get the same error when running your query through a different client, such as a C# application or a query tool? This information would help narrow down whether this is an issue related to the query, to ADO.NET or to SSIS.|||I am having this exact problem as well. The query I've entered in the SqlCommand field in the DataReader Source Edit dialog works fine from the MySQL command-line on other platforms, other boxen. It would then have to be related to .NET Provider for ODBC or SSIS.
|||I should be more clear: I'm using MySQL ODBC 3.51.15, the latest version. It comes with a command line utility (myodbc3m) I can use to manually verify the ODBC connection/configuration that SSIS is using to connect; and it works fine. This seems to exonerate the ODBC driver, ODBC configuration, and database server itself, I would think.
|||Downgrading the MySQL ODBC driver from 3.51.15 to 3.51.12 solved this problem for me. Doesn't make any sense to me.
|||Thanks for the feedback, and for going through this prosess of elimination. I'm not sure what the difference is here either (I don't do much with MySQL) but at least you've gotten past the error.

Advanced Editor for DataReader Source / SQLServer 2005

I want to import data from a remote MySql Database

So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:

Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)

the sql string is very simple, this should not be the problem:

SELECT Objektnr FROM m02a_tblObjektstamm

Thank you very much for your support

Martin

Could you try to run this query through this provider using any other client?

Thanks.

|||I has the same problem!
if you found the solutions,please tell me.Thanks for you.|||As Bob was asking above, do you get the same error when running your query through a different client, such as a C# application or a query tool? This information would help narrow down whether this is an issue related to the query, to ADO.NET or to SSIS.|||I am having this exact problem as well. The query I've entered in the SqlCommand field in the DataReader Source Edit dialog works fine from the MySQL command-line on other platforms, other boxen. It would then have to be related to .NET Provider for ODBC or SSIS.
|||I should be more clear: I'm using MySQL ODBC 3.51.15, the latest version. It comes with a command line utility (myodbc3m) I can use to manually verify the ODBC connection/configuration that SSIS is using to connect; and it works fine. This seems to exonerate the ODBC driver, ODBC configuration, and database server itself, I would think.
|||Downgrading the MySQL ODBC driver from 3.51.15 to 3.51.12 solved this problem for me. Doesn't make any sense to me.
|||Thanks for the feedback, and for going through this prosess of elimination. I'm not sure what the difference is here either (I don't do much with MySQL) but at least you've gotten past the error.

Advance Tab not available (Greyed Out)

Hello I am working on a sql express table and while configuring the steps after I select the data source and the selectment statement window shows, I want to use the advanced tab but it is greyed out. I want to be able to add edit and delete my data. I have administrator rights for this project and the workstation so thats not the issue. What I am tryng to accomplish is extending a website to manage it's content and users. Also the table has colums and the colums has test data within them I tested a query and the connection had a successful return. Maybe it's a configuration thing I am unaware of.

DKB

Hi,

you can not edit data directly from Management Studio, refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=178581&SiteID=1&PageID=1

Hemantgiri S. Goswami

|||

This is the process of setting up a grid for a new database being implemented. I expected to be able to select, edit, update or, delete for the grid. Although this has been a couple of days ago I will try to recreate the issue and record my actions and report the issue and sbmit it to msdn and see what come up.

DKB

ADO.NET Source Custom Properties - Documentation Wrong?

Ok, so I've looked near and far and have found nothing but info that says data flow properties can not be changed at runtime....then I see in this in the SSIS documentation under ADO.NET Source Custom Properties:

SQLCommand

String

The SQL statement that the ADO.NET source uses to extract data.

The value of this property can be specified by using a property expression.

Pray tell me how this property can be specified using a property expression, or any other dts variable for that matter?
Thanks,
Adrian Crawford
Adrian,

You would never find this on your own, and I realize now that we need to document this more clearly in several places in BOL.

Property expressions for components within the Data Flow task are visible and available only on the containing Data Flow task itself at the present time. Select the Data Flow task on the Control Flow tab, or an empty spot on the Data Flow designer surface, go to the Properties window (it doesn't have a Properties dialog box), select Expressions, click the ellipsis, and in there you'll be able to drop down a list, select the SqlCommand property of your DataReader source, and set the expression.

Note that nothing will show up in the DataReader editor to indicate that you've set a value (with an expression) for the SqlCommand property...it will appear blank. I trust that the "usability" of this feature will be enhanced in the future. For now, however, it's better to have awkward access to data flow properties via property expressions, than none at all.

Best regards,

-Doug
|||Doug,
Thank you so much for your post. This is the holy grail I've been looking for, and I'll take it any way I can get it. It had boggled my mind because I had read rumors of this feature but was never able to make it work.
Thanks,
Adrian
|||Doug,

Do you know if Microsoft intends (in future revs) on allowing Web Service Task web-parameters to be updateable dynamically at runtime? Right now this one is killing me. I mean, who calls a web service with the same parameter every time?!?|||

Adrian,

Glad to hear it. I apologize for overlooking your earlier post on the same subject.

I've just finished updating and adding links to multiple BOL topics so that this not entirely intuitive how-to information will be easier to locate.

As I assume you've discovered, the Property list in the Property Expressions Editor only displays available properties for data flow objects that you've already placed on the Data Flow surface of the designer. (As a result, you can't use the Property list to view all the properties of data flow objects that support property expressions.) For example, if you've placed a DataReader Source on the designer surface, the Property list contains an entry for the [DataReader Source].[SqlCommand] property. The list also displays many properties of the Data Flow task itself.

Enjoy your expressions,

-Doug

|||I don't know the answer to that, but I understand why it would be useful. You can make your request known, as always, by using sqlwish@.microsoft.com and BetaPlace http://msdn.microsoft.com/sql/bugs/default.aspx.

Best regards,

-Doug
|||Just sent the request. I will feel so special if I, personally, effect the direction of Microsoft. :)

Just one more step in my eventual goal: Total global domination.|||A mere email to sqlwish may not achieve your entire goal, but Microsoft is more responsive to customer input than the average person gets to see. Especially the documentation team (have I mentioned those Feedback links in BOL?), where improving a BOL topic runs less risk of having unforeseen side-effects than code changes.|||Doug

I have the same problem as Adrian. I need to set the SQL Command of the Data Reader from a Variable. Trying your approach, the Property Drop Down List of the Property Expression Editor does not list an SqlCommand property.

What could be the problem?

Torsten|||Not many of the stock components implement expressions. There is a list in Books Online -

Using Property Expressions to Specify Data Flow Property Values
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/cd0e171a-08be-45d6-81dc-ed94f37698b8.htm

Seems like a reasonable request though so why not log it-
http://lab.msdn.microsoft.com/productfeedback/default.aspx|||If the ADO.Net source is in the dataflow, and you open the Expressions dialog for that dataflow, you should indeed find an entry in the properties drop down. It will likely be called "[DataReader Source].[SqlCommand]"

Thanks
Mark|||

I have a similar problem

want to use the property expression on data reader source

it loks like this

select @.var1, @.var2 from @.var3 inner join @.var4

where all the variables are from the foreachloop container > variable mapping

just tell me if this is correct

|||

You should be able to use aproperty expression (set it at the data flow level though, Expressions), but your syntax is not valid. Have a look at the BOL reference for the expression syntax. It should look more like this -

"SELECT " + @.var1 + ", " + @.var2 + " FROM " + @.var3 + " inner join " + @. var4 + " ON some columns!!!"

The evaluated expression result should give you a vali SQL statement that you could copy into any query tool and run, if not make it so that it can.

ADO.NET Source Custom Properties - Documentation Wrong?

Ok, so I've looked near and far and have found nothing but info that says data flow properties can not be changed at runtime....then I see in this in the SSIS documentation under ADO.NET Source Custom Properties:

SQLCommand

String

The SQL statement that the ADO.NET source uses to extract data.

The value of this property can be specified by using a property expression.

Pray tell me how this property can be specified using a property expression, or any other dts variable for that matter?
Thanks,
Adrian Crawford
Adrian,

You would never find this on your own, and I realize now that we need to document this more clearly in several places in BOL.

Property expressions for components within the Data Flow task are visible and available only on the containing Data Flow task itself at the present time. Select the Data Flow task on the Control Flow tab, or an empty spot on the Data Flow designer surface, go to the Properties window (it doesn't have a Properties dialog box), select Expressions, click the ellipsis, and in there you'll be able to drop down a list, select the SqlCommand property of your DataReader source, and set the expression.

Note that nothing will show up in the DataReader editor to indicate that you've set a value (with an expression) for the SqlCommand property...it will appear blank. I trust that the "usability" of this feature will be enhanced in the future. For now, however, it's better to have awkward access to data flow properties via property expressions, than none at all.

Best regards,

-Doug
|||Doug,
Thank you so much for your post. This is the holy grail I've been looking for, and I'll take it any way I can get it. It had boggled my mind because I had read rumors of this feature but was never able to make it work.
Thanks,
Adrian
|||Doug,

Do you know if Microsoft intends (in future revs) on allowing Web Service Task web-parameters to be updateable dynamically at runtime? Right now this one is killing me. I mean, who calls a web service with the same parameter every time?!?|||

Adrian,

Glad to hear it. I apologize for overlooking your earlier post on the same subject.

I've just finished updating and adding links to multiple BOL topics so that this not entirely intuitive how-to information will be easier to locate.

As I assume you've discovered, the Property list in the Property Expressions Editor only displays available properties for data flow objects that you've already placed on the Data Flow surface of the designer. (As a result, you can't use the Property list to view all the properties of data flow objects that support property expressions.) For example, if you've placed a DataReader Source on the designer surface, the Property list contains an entry for the [DataReader Source].[SqlCommand] property. The list also displays many properties of the Data Flow task itself.

Enjoy your expressions,

-Doug

|||I don't know the answer to that, but I understand why it would be useful. You can make your request known, as always, by using sqlwish@.microsoft.com and BetaPlace http://msdn.microsoft.com/sql/bugs/default.aspx.

Best regards,

-Doug
|||Just sent the request. I will feel so special if I, personally, effect the direction of Microsoft. :)

Just one more step in my eventual goal: Total global domination.|||A mere email to sqlwish may not achieve your entire goal, but Microsoft is more responsive to customer input than the average person gets to see. Especially the documentation team (have I mentioned those Feedback links in BOL?), where improving a BOL topic runs less risk of having unforeseen side-effects than code changes.|||Doug

I have the same problem as Adrian. I need to set the SQL Command of the Data Reader from a Variable. Trying your approach, the Property Drop Down List of the Property Expression Editor does not list an SqlCommand property.

What could be the problem?

Torsten|||Not many of the stock components implement expressions. There is a list in Books Online -

Using Property Expressions to Specify Data Flow Property Values
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/cd0e171a-08be-45d6-81dc-ed94f37698b8.htm

Seems like a reasonable request though so why not log it-
http://lab.msdn.microsoft.com/productfeedback/default.aspx|||If the ADO.Net source is in the dataflow, and you open the Expressions dialog for that dataflow, you should indeed find an entry in the properties drop down. It will likely be called "[DataReader Source].[SqlCommand]"

Thanks
Mark|||

I have a similar problem

want to use the property expression on data reader source

it loks like this

select @.var1, @.var2 from @.var3 inner join @.var4

where all the variables are from the foreachloop container > variable mapping

just tell me if this is correct

|||

You should be able to use aproperty expression (set it at the data flow level though, Expressions), but your syntax is not valid. Have a look at the BOL reference for the expression syntax. It should look more like this -

"SELECT " + @.var1 + ", " + @.var2 + " FROM " + @.var3 + " inner join " + @. var4 + " ON some columns!!!"

The evaluated expression result should give you a vali SQL statement that you could copy into any query tool and run, if not make it so that it can.

ADO.NET OleDb SET ROWCOUNT or DBPROP_MAXROWS

I am writing a data access tool that needs to be non provider specific. I have used System.Data.OleDb to access a variety of data source types (MSSQLServer, MSAccess, MSExcel, CSV, Oracle, XML). This works beautifully. Thankyou Microsoft for giving us ADO.NET2.0.

However, I now need to restrict the number of rows returned from a SELECT statement. Is there any way I can I achieve this in a non provider specific fashion?

So far searches have yielded DBPROP_MAXROWS however I can find no way of setting the DBProperties using ADO.NET2.0. Is this possible?

Documentation for DBPROP_MAXROWS states that it uses "SET ROWCOUNT n" as part of the command text. I tried this and it works but not for the JET4 provider (this is a problem since I use JET4 to access .mdb, .xls and .csv files)!

Any tips here would be greatly appreciated. Even if somebody were to tell me that it's just not possible, at least that would put me out of my misery :)

Thanks.

Because of the way JET accesses data, bringing all of the data to the client before processing the criteria, I don't think that there is a way to make that work.|||

Thanks for the reply Arnie,

since posting I have done some more research and discovered that using TOP works for Jet. This is good for me as it also works for SQL Server. The approach I have taken is as follows:

int maxRows = 100;

string someQry = "SELECT * FROM table1";

string topQry = "SELECT TOP " + maxRows.ToString() + " * FROM (" + someQry + ") as topSubQry";

However it doesn't work for XML files. To get data from XML files I use a connection string of "Provider=MSDAOSP.1;Data Source=MSXML2.DSOControl.2.6;" and then the full path to the XML file as the command text. I am guessing there will just be no way to limit the rows returned when using this provider.