Foreach Loop and distributed files

Hi - I'm new to SSIS and am having problems figuring out how to do the following.

I need to load data from flat files into SQLserver 2005 and have created the data flows ok, but my data files are *not* located in a single directory so I cannot use the foreach file enumerator option in the foreach loop container collection. Please correct me if I'm wrong?

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

My problem comes when I edit the properties of the connection manager to try to use that variable for the connectionstring property in the expression editor. I get an error because the datatype of the variable is not supported in an expression.

Can anyone tell me how to correct this or outline another way to solve my problem?

thanks

Brian McLean wrote:

I get an error because the datatype of the variable is not supported in an expression.

Why not? You should be posting the result of the foreach loop into a string variable.|||

But you cannot return a recordset into a string! I tried and the sql execution failed with the following error...

Error: 0xC001F009 at DAOphotLoad: The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Select Catalog files from HLA DB, Execute SQL Task: Executing the query "Select DAOcat_filename from ImgFileInfo where DAOCat_status like '%Processed%'" failed with the following error: "The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||You return a recordset into an object typed variable. Then the foreach loop works on that object variable. Using the variable mappings on the foreach loop, you can store the pieces of data in the object variable in string, int, whatver, variables.|||

Brian McLean wrote:

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

You are in the right track; but you are missing one part; you need to shred the rowset into string variables:

Jamie has a sample package here; pay special attention to Collection and Variable mapping tabs inside of the forEach loop container:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

Force Row Level locking in SQLServer 2000 ?

Hi

Is it possible to force row level locking in one or more tables in
some database. We have some problems when SQL Server decides to choose
page- or table-level locking.
We are using SQL Server 2000.

Best regards

AarnoArska (aarno.autio@.bof.fi) writes:
> Is it possible to force row level locking in one or more tables in
> some database. We have some problems when SQL Server decides to choose
> page- or table-level locking.
> We are using SQL Server 2000.

You can add a locking hint

SELECT * FROM tbl (ROWLOCK) WHERE col = 32

However, SQL Server may disregard that hint if row locks are possible
to achieve.

You may need to review you indexing strategy. For instance, in the example
above, I would not expect the hint to help if there is no index on col.
SQL Server will have to scan the entire table, so a tablock is called for.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

force a specific order of record in a table

I want the record in table is sorted, I try to use index but not succesful,
why?
I am using SQLServer 2000Use ORDER BY clause
"kei" <kei@.discussions.microsoft.com> wrote in message
news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>I want the record in table is sorted, I try to use index but not succesful,
> why?
> I am using SQLServer 2000|||YOu could create a clustered index which defines the rows / data to be
physically sorted after the clustered key. but if you want to get the
data in an ordered way, the best thing is to use the ORDER BY clause.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||No, I means the physical order, not using sql statement, anyway, I think the
answer is clustered index, thx!!
"Uri Dimant" wrote:

> Use ORDER BY clause
>
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>
>|||kei
No, don't relay on phisycal order .If you wanted to get out a sorted result
use ORDER BY clause for your safety
"kei" <kei@.discussions.microsoft.com> wrote in message
news:47E2427C-C55B-4970-808F-8AF193920F36@.microsoft.com...[vbcol=seagreen]
> No, I means the physical order, not using sql statement, anyway, I think
> the
> answer is clustered index, thx!!
> "Uri Dimant" wrote:
>|||No that wouldn't actually solve it as Uri already explained.
The why is due to the definition of a table in relational
database systems. A table has unordered rows and columns.
The physical order of the columns and rows does not, should
not matter. You manage ordering through SQL statements and
order by clauses.
-Sue
On Wed, 26 Apr 2006 02:40:01 -0700, kei
<kei@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No, I means the physical order, not using sql statement, anyway, I think th
e
>answer is clustered index, thx!!
>"Uri Dimant" wrote:
>

force a specific order of record in a table

I want the record in table is sorted, I try to use index but not succesful,
why?
I am using SQLServer 2000YOu could create a clustered index which defines the rows / data to be
physically sorted after the clustered key. but if you want to get the
data in an ordered way, the best thing is to use the ORDER BY clause.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Use ORDER BY clause
"kei" <kei@.discussions.microsoft.com> wrote in message
news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>I want the record in table is sorted, I try to use index but not succesful,
> why?
> I am using SQLServer 2000|||kei
No, don't relay on phisycal order .If you wanted to get out a sorted result
use ORDER BY clause for your safety
"kei" <kei@.discussions.microsoft.com> wrote in message
news:47E2427C-C55B-4970-808F-8AF193920F36@.microsoft.com...
> No, I means the physical order, not using sql statement, anyway, I think
> the
> answer is clustered index, thx!!
> "Uri Dimant" wrote:
>> Use ORDER BY clause
>>
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>> >I want the record in table is sorted, I try to use index but not
>> >succesful,
>> > why?
>> > I am using SQLServer 2000
>>|||No, I means the physical order, not using sql statement, anyway, I think the
answer is clustered index, thx!!
"Uri Dimant" wrote:
> Use ORDER BY clause
>
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
> >I want the record in table is sorted, I try to use index but not succesful,
> > why?
> > I am using SQLServer 2000
>
>|||No that wouldn't actually solve it as Uri already explained.
The why is due to the definition of a table in relational
database systems. A table has unordered rows and columns.
The physical order of the columns and rows does not, should
not matter. You manage ordering through SQL statements and
order by clauses.
-Sue
On Wed, 26 Apr 2006 02:40:01 -0700, kei
<kei@.discussions.microsoft.com> wrote:
>No, I means the physical order, not using sql statement, anyway, I think the
>answer is clustered index, thx!!
>"Uri Dimant" wrote:
>> Use ORDER BY clause
>>
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>> >I want the record in table is sorted, I try to use index but not succesful,
>> > why?
>> > I am using SQLServer 2000
>>

FOR XML performance question

I'm cross posting this to microsoft.public.sqlserver.xml, a more appropriate
forum for this question.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:F314E284-6E34-403C-94C1-B307682B1418@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.
----
In addition to the above I have done some further investigation. On a query
which returns the top row from a table the FOR XML method performed 83.5%
faster than the recordset version. However, when I run a where query which
I
know returns a single row the FOR XML method performance plunges and is
actually 6% slower than the recordset version.
Is SQLXML just one of those things which seems like a great idea but has no
real practical use in an enterprise environment? I find it very frustrating
that its performance is superb in some situations but is so awful in others.
Is it a work in progress?
That said, are there any resources which discuss various ways to pull data
from SQL server 2000 as(and convert to) XML format? Surely there is a bette
r
way than using a ADO recordset as described above?
Thanks.|||Hi Lee
This is hard to answer without having more specifics.
How does your FOR XML query look like? How does it compare to the previous
query, what indices do yo have on it?
Etc.
Best regards
Michael
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.
> ----
> In addition to the above I have done some further investigation. On a
> query
> which returns the top row from a table the FOR XML method performed 83.5%
> faster than the recordset version. However, when I run a where query
> which I
> know returns a single row the FOR XML method performance plunges and is
> actually 6% slower than the recordset version.
> Is SQLXML just one of those things which seems like a great idea but has
> no
> real practical use in an enterprise environment? I find it very
> frustrating
> that its performance is superb in some situations but is so awful in
> others.
> Is it a work in progress?
> That said, are there any resources which discuss various ways to pull data
> from SQL server 2000 as(and convert to) XML format? Surely there is a
> better
> way than using a ADO recordset as described above?
> Thanks.|||The query is a simple :-
SELECT stuff
FROM table
WHERE condition (optional)
FOR XML RAW
There is a single index on the primary key of the table and that was the
field I did my where clause on as described in my above posts.
"Michael Rys [MSFT]" wrote:

> Hi Lee
> This is hard to answer without having more specifics.
> How does your FOR XML query look like? How does it compare to the previous
> query, what indices do yo have on it?
> Etc.
> Best regards
> Michael
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>
>

FOR XML AUTO broken in 2005

Re: http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic32700.aspx

I'm having exactly the same problem, although I'm writing queries that need to run on both SQL 2000 and 2005. I cannot believe this isn't a bug. Although I understand the logic behind the results I cannot accept the results in 2005 are correct. If each part a union produces a parent/child structure why is it considered correct that UNIONing the two produces a flat no-child relationship? It makes no sense, I don't want to see a compatible mode in a service pack for 2005 I want to see the bug fixed!

I understand your problem is that you want to write an XML publishing query with the specific UNION that works both in SQL Server 2000 and in SQL Server 2005. Please consider using FOR XML EXPLICIT - it should solve your problem.

Also note that SQL Server 2005 Service Pack 1 should contain a fix for the compatibility issue - FOR XML AUTO query with the UNION from the link you provided will work the same way between SQL Server 2000 and SQL Server 2005 SP1 for a database with 80 compatibility level, thus not breaking your application after upgrade to SQL Server 2005 SP1. FOR XML AUTO with the UNION on a database with 90 compatibility level will work the same in SQL Server 2005 RTM and SP1.

Regards,

Eugene

Technical Lead,

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you for the reply, and yes I am using XML Explicit to work-around the problem...and it looks horrible.

I also understand that the service pack will have this special compatible mode, my point is that it shouldn't have. I don't understand the justification of letting 2005 produce results the way it does. I'm stating that this is a bug and should be fixed. I'd be very interested in learning why Microsoft feel this is a compatibility issue and not a bug. Given that if Select X...For XML AUTO produces a parent/child result surely unioning two lots of Select X would still produce a parent/child result?

|||

In addition to my explanations in http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic32700.aspx I'd say that while column naming derived from the first leg of UNION [ALL] is documented in BOL column-to-table association (which FOR XML AUTO uses) on top of UNION [ALL] was never documented; SQL Server 2000 behavior there is incorrect.

I generally discourage you from using AUTO mode of FOR XML on top of set operations (like UNION [ALL]/EXCEPT/INTERSECT) since it will prevent from using some performance optimizations we can do in AUTO mode. This is in SQL Server 2005. In SQL Server 2000 we would apply the optimizations but because of the buggy column-to-table associations we can get wrong results. I provided a repro for the wrong results below.

If you need UNION ALL and not UNION you may consider supplying two separate FOR XML AUTO and concatenating the results on the client side. This is given that you need to use a syntax that works on both SQL Server 200 and 2005. In SQL Server 2005 this can be achieved in a more explicit and cleaner way.

Here’s the SQL Server 2000 repro that produces wrong results. Notice different PK constraints on different tables and duplicate col1 values for t2 and t4.

create table t1(col1 int not null primary key, col2 varchar(256) not null)

insert t1 select 1,'t1col2row1'

insert t1 select 2,'t1col2row2'

go

create table t3(col1 int not null primary key, col2 varchar(256) not null)

insert t3 select 1,'t3col2row1'

insert t3 select 2,'t3col2row2'

go

create table t2(col1 int not null, col2 varchar(256) not null primary key)

insert t2 select 1,'t2col2row1'

insert t2 select 1,'t2col2row2'

go

create table t4(col1 int not null, col2 varchar(256) not null primary key)

insert t4 select 1,'t4col2row1'

insert t4 select 1,'t4col2row2'

go

select t1.col1,t1.col2 col12,t3.col2 from t3

inner join t1 on t3.col1 = t1.col1

union

select t2.col1,t2.col2 col12,t4.col2 from t4

inner join t2 on t4.col1 = t2.col1

for xml auto

go

Regards,

Eugene

Technical Lead,

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Again, thank you for the reply, interesting to hear that it doesn't perform very well. As I've mentioned I do understand the algorithm that is producing the results, what I'm saying is the algorithm is fundamentally flawed when used with UNIONs. To the user, SQL 2000 produces logical results whereas 2005 does not, for me that tells me that this is a bug. If you want to tell me that UNIONs and FOR XML AUTO are not longer supported but we'll provide a compat' mode, then I can swallow that. What I don't understand is the view that it's working fine in 2005 when clearly it doesn't.

As for the workarounds (and doesn't this also imply a bug) those are ok (not a great fan of using the client to do that) and I'm using the EXPLICIT XML alternative.

I don't want to appear argumentative but I would just like it to be recognised as a failing in 2005 and it should be documented as a breaking change rather than have a test fail or ,worse, have a customer report that upgrading to 2005 has broken their crucial application and have had to downgrade back to 2000! All of which could be avoided by clearly stating that there is a breaking change.