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

Mapping of an xml column to variable

I have a For Each Loop that iterates over a recordset stored in a variable. One of the columns in the recordset is type xml and I want to map it to a variable using Variable Mappings of the For Each Loop container. I am getting this error:

Error: 0xC001C012 at FELC Loop thru report defs: ForEach Variable Mapping number 4 to variable "User::Parameters_xml" cannot be applied.

I have tried changing the type of the Parameters_xml variable to Object and String, but I get the same error. Any ideas?

you can try using a script task instead.|||I tried casting the xml-typed column to varchar(8000) and then it worked.

many BULK INSERTs and error "cannot obtain a lock"...

Hi,
I still have problem with my script that perform a sequence of
many
data files with a BULK INSERT in a loop and rarely (about 10% of
times) fails with "cannot obtain a lock" (see the original post
below).
Since this happens also on a dedicate server without any other
activity
(both sql server and the O.S. are idle apart for the BULK INSERT
loop)
I start thinking the cause could be in the ODBC connection and cursor
that are
constantly re-used during the whole loop (the odbc connection is
in autocommit) and so
A friend of mine suggested me that I could try to enforce a checkpoint
could a ' cursor.execute("CHECKPOINT") ' statement issued before
any BULK INSERT
solve my issue?
TIA!
bye,
PiErre
on 2007-02-16 PiErre wrote:
> Subject: suggestion to avoid error "cannot obtain a lock"
> Hi,
> I have a script (actually the same python script
> that runs locally on several ms sql 2000sp4
> server - connecting via mxodbc)
> that every night tries to bulk insert about 20 files
> of various size (from less than 10 MB
> to more than 300 MB - about 900 MB in total )
> and sometimes (about 5% of the times)
> the importation fails (randomly on
> one or more of the servers) with the error like
> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
> The SQL Server cannot obtain a LOCK resource at this time...
>
> There is no other activity on the db server at the error time and
> so I cannot detect what is the cause of the resource lock.
> The db is normally configured with recovery-model set to simple
> and autoshrink enabled (not my decision
> and I must provide clear evidence to have
> that "standard" changed), but even after
> disable it the errors still appear.
>
> What can I do to detect the actual lock cause?
> More important: what can I do to avoid
> such errors? I heard something
> about to set a checkpoint after every bulk insert
> but I don't know how to do that...
>
> Thanks in advance for your help!
> bye,
> PiErreTry specifying TABLOCK on the BULK INSERT statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PiErre" <siggy2@.supereva.it> wrote in message
news:1178184835.364450.30740@.y80g2000hsf.googlegroups.com...
> Hi,
> I still have problem with my script that perform a sequence of
> many
> data files with a BULK INSERT in a loop and rarely (about 10% of
> times) fails with "cannot obtain a lock" (see the original post
> below).
> Since this happens also on a dedicate server without any other
> activity
> (both sql server and the O.S. are idle apart for the BULK INSERT
> loop)
> I start thinking the cause could be in the ODBC connection and cursor
> that are
> constantly re-used during the whole loop (the odbc connection is
> in autocommit) and so
> A friend of mine suggested me that I could try to enforce a checkpoint
> could a ' cursor.execute("CHECKPOINT") ' statement issued before
> any BULK INSERT
> solve my issue?
> TIA!
> bye,
> PiErre
> on 2007-02-16 PiErre wrote:
>> Subject: suggestion to avoid error "cannot obtain a lock"
>> Hi,
>> I have a script (actually the same python script
>> that runs locally on several ms sql 2000sp4
>> server - connecting via mxodbc)
>> that every night tries to bulk insert about 20 files
>> of various size (from less than 10 MB
>> to more than 300 MB - about 900 MB in total )
>> and sometimes (about 5% of the times)
>> the importation fails (randomly on
>> one or more of the servers) with the error like
>> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
>> The SQL Server cannot obtain a LOCK resource at this time...
>> There is no other activity on the db server at the error time and
>> so I cannot detect what is the cause of the resource lock.
>> The db is normally configured with recovery-model set to simple
>> and autoshrink enabled (not my decision
>> and I must provide clear evidence to have
>> that "standard" changed), but even after
>> disable it the errors still appear.
>> What can I do to detect the actual lock cause?
>> More important: what can I do to avoid
>> such errors? I heard something
>> about to set a checkpoint after every bulk insert
>> but I don't know how to do that...
>> Thanks in advance for your help!
>> bye,
>> PiErre
>|||On 3 Mag, 12:08, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> Try specifying TABLOCK on the BULK INSERT statement.
>
There are good chance that your hint was EXACTLY what I was looking
for...
Thanks a lot!
bye,
PiErre