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 Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

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.

Manual validation of external metadata

Hi,

is there a way to start validation of external metadata manual?

My problem is this:

The package uses a variable as connectionstring for flatfile source, and another variable for the destination table. Running the package gives a warning about external metadata that needs to be updated. Normally I update this data by just opening the data flow, and answering the question for updating with yes. This time that deosnt work, I think because the variable is not set, so there can not be any conflicts with external metadata.

I dont want do disable validation, but just validate one time and then save the package.

Any ideas?

Regards,

Jan

Not sure I got the problem...but have you looked at the DelayValidation property of the flat file connection manager and destination component. Also make sure you give some values to those variables at design time.|||

Hi Jan,

I dont want do disable validation, but just validate one time and then save the package.

I'm not sure this is an option without setting DelayValidation to True for the package. Validation occurs at different times for different objects. For instance, when the package is opened in the development environment, some validation occurs - things like Connection Manager connections are tested, etc. When the package is executed, some other validation is performed.

There's a great description of this here.

Hope this helps,
Andy

|||

Hi,

I tried to set option delayValidation to true, but this didn't help.

My package still logs warning messages because the metadata are not updated, but I cant update them without running the package, but while running the package I don't see a way to update metadata. That's my dilemma.

Regards,

Jan

|||

Hi Jan,

Is it possible to set the variables to a Development instance of SQL Server? The values will still be updated at runtime and if they're not, the worst that will happen is they'll execute against Development.

Hope this helps,
Andy

|||

Hi Andy,

unfortunately it is not an option for me to set the variables to an development instance, because that would not help.

My variables are filled up dynamically with the right values on runtime, and because this is done in a for-each container a lot of values are set. After each iteration the data flow task is started, and this causes in warnings for the metadata. I would need to set each possible value in every variable, disable my scripttask und run the package "manually" step by step. This is my problem with SSIS: sometimes it seems that SSIS wasn't developed for dynamic use, cause everytime you use it this ways you get stopped by strange problems like these.

What I dont get is why doesnt SSIS give me an option to update metadata on runtime, instead of warning me that they needs to be updated, without giving me the possibility to do that....

Thanks for all your help and ideas, I think I will just ignore the warnings.

Regards,

Jan

|||

By any chance are you changing column names or data types in the source/destination components; if so I am afraid that would break the data flow...changing the path or file name of a flat file connection manager; or table names would be fine for SSIS though...