mapping XML data to variable

I can’t figure out how to map xml data stored in a table to a variable in integration service.

For example:
I would like to use a “for each loop container” to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like “variable mapping number X to variable XXX can’t apply”.

Any help?

This is a supported scenario. Ensure that:

The column is actually being loaded into the record set: Check the column mappings in the recordset dest The value being mapped to the variable is less than 4000 characters long: select max(datalength(xmlCol)) from XmlTable

Managing record groupings

Hi group,
I cannot figure out the best way to manage some data I have to put into a
database without doing something il-advised. Imagine this scenario:
Table: Animals
AnimalID AnimalType
1 Cat
2 Dog
3 Ferret
4 Iguana
5 Orangutan
6 Nurse Shark
7 Binturong
8 King Snake
9 Moth
10 Crawfish
11 Pelican
12 Man
13 Porpoise
14 Ermine
15 Seahorse
Fine, so there's a long list of animals, say a few hundred, and what an end
user needs to be able to do is select any number of animals and assign them
to carriers for transportation. The end user may select that one animal
type is alone, or one animal will travel with anywhere from 1 to
count(animalid)-1 animals. So, I can't do anything like:
AnimalID AnimalType TravelsWith01 TravelsWith02 TravelsWith03
That would be silly. But I can't figure out how to make a table that can
manage an unlimited number of AnimalIDs that would indicate that they are
related in some way (will travel together). There won't necessarily be a
fixed number of traveling containers either. Like, I can't do:
TravelContainerID Animal1, Animal2, Animal3
That would also leave me with a bunch of Animal columns that wouldn't make
sense to have exist. The way I'm thinking about it now is:
MatchID AnimalID
1 1
1 6
1 9
2 3
2 11
3 12
4 7
4 15
4 10
4 5
4 13
And so on. So, that would mean that animals 1, 6, and 9 travel together,
and so on. But something about that doesn't seem right either. Can anyone
offer advice for my design please?
Thank you,
Ray at workRay,
I think your end solution is fine. You have basically
created a join table that contains yours "Transit ID"
associated with the animals in that "Transit ID". You
could now add a shipping ID to track how the animals were
sent and still be able to tell what group they were in.
Hope that helps.
Derek
>--Original Message--
>Hi group,
>I cannot figure out the best way to manage some data I
have to put into a
>database without doing something il-advised. Imagine
this scenario:
>
>Table: Animals
>AnimalID AnimalType
>1 Cat
>2 Dog
>3 Ferret
>4 Iguana
>5 Orangutan
>6 Nurse Shark
>7 Binturong
>8 King Snake
>9 Moth
>10 Crawfish
>11 Pelican
>12 Man
>13 Porpoise
>14 Ermine
>15 Seahorse
>Fine, so there's a long list of animals, say a few
hundred, and what an end
>user needs to be able to do is select any number of
animals and assign them
>to carriers for transportation. The end user may select
that one animal
>type is alone, or one animal will travel with anywhere
from 1 to
>count(animalid)-1 animals. So, I can't do anything like:
>AnimalID AnimalType TravelsWith01 TravelsWith02
TravelsWith03
>That would be silly. But I can't figure out how to make
a table that can
>manage an unlimited number of AnimalIDs that would
indicate that they are
>related in some way (will travel together). There won't
necessarily be a
>fixed number of traveling containers either. Like, I
can't do:
>TravelContainerID Animal1, Animal2, Animal3
>That would also leave me with a bunch of Animal columns
that wouldn't make
>sense to have exist. The way I'm thinking about it now
is:
>
>MatchID AnimalID
>1 1
>1 6
>1 9
>2 3
>2 11
>3 12
>4 7
>4 15
>4 10
>4 5
>4 13
>And so on. So, that would mean that animals 1, 6, and 9
travel together,
>and so on. But something about that doesn't seem right
either. Can anyone
>offer advice for my design please?
>Thank you,
>Ray at work
>
>.
>|||Thank you Derek.
Ray at work
"Derek Wilson" <derek_wilson@.rmic.com> wrote in message
news:016f01c34006$3d6dbfd0$a501280a@.phx.gbl...
> Ray,
> I think your end solution is fine. You have basically
> created a join table that contains yours "Transit ID"
> associated with the animals in that "Transit ID". You
> could now add a shipping ID to track how the animals were
> sent and still be able to tell what group they were in.
> Hope that helps.
> Derek
> >--Original Message--
> >Hi group,
> >
> >AnimalID AnimalType TravelsWith01 TravelsWith02
> TravelsWith03
> >
> >That would be silly. But I can't figure out how to make
> a table that can
> >manage an unlimited number of AnimalIDs that would
> indicate that they are
> >related in some way (will travel together). There won't
> necessarily be a
> >fixed number of traveling containers either. Like, I
> can't do:
> >
> >TravelContainerID Animal1, Animal2, Animal3
> >
> >That would also leave me with a bunch of Animal columns
> that wouldn't make
> >sense to have exist. The way I'm thinking about it now
> is:
> >
> >
> >MatchID AnimalID
> >1 1
> >1 6
> >1 9
> >2 3
> >2 11
> >3 12
> >4 7
> >4 15
> >4 10
> >4 5
> >4 13
> >
> >And so on. So, that would mean that animals 1, 6, and 9
> travel together,
> >and so on. But something about that doesn't seem right
> either. Can anyone
> >offer advice for my design please?
> >
> >Thank you,
> >
> >Ray at work
> >
> >
> >.
> >

managing log file

I'm unfortunately not a DBA who is forced into being a DBA. I'm trying to
figure out why the log file for my database is almost 300MB when the data
file is only 31MB. Shrinking it does no good. I back the database up, but
that doesn't allow for making the log file any smaller either. Can someone
give me a brief explanation as to how to effectively manage my log file and
make it smaller? If it is historically keeping every single modification
ever made, I don't need it to do that past the point where I'm backing it
up.
Thanks,
Jamesyou'll need a log-backup (not a full) to allow the log to remove completed
transactions..
jobi
"James" <capricorn@.nospam.com> wrote in message
news:#BAaYK#hDHA.884@.TK2MSFTNGP10.phx.gbl...
> I'm unfortunately not a DBA who is forced into being a DBA. I'm trying to
> figure out why the log file for my database is almost 300MB when the data
> file is only 31MB. Shrinking it does no good. I back the database up,
but
> that doesn't allow for making the log file any smaller either. Can
someone
> give me a brief explanation as to how to effectively manage my log file
and
> make it smaller? If it is historically keeping every single modification
> ever made, I don't need it to do that past the point where I'm backing it
> up.
> Thanks,
> James
>|||The log file is not emptied on database backup. Make sure that you do regular log backups or
possibly put the db in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"James" <capricorn@.nospam.com> wrote in message news:%23BAaYK%23hDHA.884@.TK2MSFTNGP10.phx.gbl...
> I'm unfortunately not a DBA who is forced into being a DBA. I'm trying to
> figure out why the log file for my database is almost 300MB when the data
> file is only 31MB. Shrinking it does no good. I back the database up, but
> that doesn't allow for making the log file any smaller either. Can someone
> give me a brief explanation as to how to effectively manage my log file and
> make it smaller? If it is historically keeping every single modification
> ever made, I don't need it to do that past the point where I'm backing it
> up.
> Thanks,
> James
>|||In addition to other post refer to below mentioned urls.
http://www.support.microsoft.com/?id=256650 INF: How to
Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File
Grows too big
http://www.support.microsoft.com/?id=110139 Log file
filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink
File
http://www.support.microsoft.com/?id=315512
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File
Grows too big
http://www.support.microsoft.com/?id=110139 Log file
filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink
File
http://www.support.microsoft.com/?id=315512
Considerations for Autogrow and AutoShrink
- Vishal|||Ok, I went in EM and right clicked the database, selected All Tasks, Backup
Database, and selected Transaction Log and executed the backup. Then I went
into All Tasks, Shrink Database. I clicked the Files button and selected
the Log File. It tells me that Space used is 30MB and its current size is
230MB. I used the "Compress pages and then truncate free space from the
file" and this had very little impact. The file is still 230MB reporting
only 30MB used. I then did the general "Shrink Database" selecting "Move
pages to the beginning of the file before shrinking", but this again yields
Space Allocated: 252MB, space free: 190MB (75%). How can I get the
Transaction Log to shrink to a size closer to the size of its contents?
Thanks,
James
"jobi" <jobi@.reply2.group> wrote in message
news:bldttb$n4h$1@.reader08.wxs.nl...
> you'll need a log-backup (not a full) to allow the log to remove completed
> transactions..
> jobi
> "James" <capricorn@.nospam.com> wrote in message
> news:#BAaYK#hDHA.884@.TK2MSFTNGP10.phx.gbl...
> > I'm unfortunately not a DBA who is forced into being a DBA. I'm trying
to
> > figure out why the log file for my database is almost 300MB when the
data
> > file is only 31MB. Shrinking it does no good. I back the database up,
> but
> > that doesn't allow for making the log file any smaller either. Can
> someone
> > give me a brief explanation as to how to effectively manage my log file
> and
> > make it smaller? If it is historically keeping every single
modification
> > ever made, I don't need it to do that past the point where I'm backing
it
> > up.
> >
> > Thanks,
> >
> > James
> >
> >
>|||there's a kb that clears out the mistics of transactionlogshrinking.
hope this link stil works :
http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
jobi
"James" <capricorn@.nospam.com> wrote in message
news:#tdzkHDiDHA.1300@.TK2MSFTNGP10.phx.gbl...
> Ok, I went in EM and right clicked the database, selected All Tasks,
Backup
> Database, and selected Transaction Log and executed the backup. Then I
went
> into All Tasks, Shrink Database. I clicked the Files button and selected
> the Log File. It tells me that Space used is 30MB and its current size is
> 230MB. I used the "Compress pages and then truncate free space from the
> file" and this had very little impact. The file is still 230MB reporting
> only 30MB used. I then did the general "Shrink Database" selecting "Move
> pages to the beginning of the file before shrinking", but this again
yields
> Space Allocated: 252MB, space free: 190MB (75%). How can I get the
> Transaction Log to shrink to a size closer to the size of its contents?
> Thanks,
> James
>
> "jobi" <jobi@.reply2.group> wrote in message
> news:bldttb$n4h$1@.reader08.wxs.nl...
> > you'll need a log-backup (not a full) to allow the log to remove
completed
> > transactions..
> >
> > jobi
> > "James" <capricorn@.nospam.com> wrote in message
> > news:#BAaYK#hDHA.884@.TK2MSFTNGP10.phx.gbl...
> > > I'm unfortunately not a DBA who is forced into being a DBA. I'm
trying
> to
> > > figure out why the log file for my database is almost 300MB when the
> data
> > > file is only 31MB. Shrinking it does no good. I back the database
up,
> > but
> > > that doesn't allow for making the log file any smaller either. Can
> > someone
> > > give me a brief explanation as to how to effectively manage my log
file
> > and
> > > make it smaller? If it is historically keeping every single
> modification
> > > ever made, I don't need it to do that past the point where I'm backing
> it
> > > up.
> > >
> > > Thanks,
> > >
> > > James
> > >
> > >
> >
> >
>|||Hi James,
What is the version of SQL Server?
In SQL Server 7.0, there are some common reasons why a transaction log
might not shrink when you use the DBCC SHRINKFILE or DBCC SHRINKDATABASE
command. The SQL Server Books Online topics "DBCC SHRINKFILE" and "DBCC
SHRINKDATABASE" provide detailed information, but a brief summary follows.
For additional information regarding shrinking the SQL Server 7.0
transaction log, please refer to the following article below:
256650 INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?id=256650
In SQL Server 2000, shrinking the log in is no longer a deferred operation.
A shrink operation attempts to shrink the file immediately. However, in
some circumstances it may be necessary to perform additional actions before
the log file is shrunk to the desired size.
For additional information regarding shrinking the Transaction Log in SQL
Server 2000, please refer to the following article below:
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Please let me know if this solves your problem or if you would like further
assistance.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.