Mappings question in OLE DB Destination

Hi,

I have a situation where I want to map a column from a flat file to TWO columns in a table.

However, in the mappings tab, you can only select the "Input Column" once. Once a column has been used, it no longer appears in the drop down list.

I am wondering if there's a way to override this behavior, and if not, what is the best way to handle this type of situation?

I have added an EXECUTE SQL task to update the second column with the inserted column values, but I would like to know if the default mapping behavior can be changed, as it seems so limited.

Thanks

Add a derived column right before the destination and select the column that you want to use more than once and drag it to the expression box. Adjust the name of the new column accordingly.

Then in the OLE DB Destination you can select the column you just added.

Feel free to suggest new features over at http://connect.microsoft.com/sqlserver/feedback|||

Great, thanks

sql

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 User Defined Data Type to Base Data Type

Hi,

I am trying to map a user-defined datatype to it's base data type in SQL Server 2000/2005. Let's say I have created a udt named ssn which is actually a char datatype with length 9. I need a query that would map the udt with the base datatype and give the typename of both. I have been using the sys.types table but I still can't see the link. Any help would be appreciated.

Thanks

Here is an Information_Schema view that I created a long time ago in my databases. Most of it I actually copied from a sql 2000 system sproc. You should be able to use it to construct what you need:

SELECT TOP 100 PERCENT
*,
ColumnName + ' ' +
UsedDataType +
CASE WHEN UserDefinedDataType IS NULL THEN
CASE WHEN collationID IS NOT NULL THEN
'('+CAST(CharacterMaxLength AS VARCHAR(10))+')'
ELSE ''
END
ELSE
''
END
AS ColumnDefinition,
ColumnName + ' ' +
UPPER(basedatatype) +
CASE WHEN collationID IS NOT NULL THEN '('+CAST(CharacterMaxLength AS VARCHAR(10))+')' ELSE '' END
AS ColumnDefinition2,
UsedDataType +
CASE WHEN UserDefinedDataType IS NULL THEN
CASE WHEN collationID IS NOT NULL THEN
'('+CAST(CharacterMaxLength AS VARCHAR(10))+')'
ELSE ''
END
ELSE
''
END
AS DefinedDataType
FROM
(
SELECT
DB_NAME() AS DatabaseName,
CASE obj.xtype WHEN 'U' THEN 'TABLE' WHEN 'V' THEN 'VIEW' WHEN 'P' THEN 'PROCEDURE' END AS ObjectType,
USER_NAME(obj.uid) AS TableSchema,
obj.name AS TableName,
col.name AS ColumnName,
col.colid AS ColumnPosition,
com.text AS DefaultValue,
CASE col.isnullable WHEN 1 THEN 'YES' ELSE 'NO' end AS IsNullable,
spt_dtp.LOCAL_TYPE_NAME AS BaseDataType,
CASE WHEN typ.xusertype > 256 THEN typ.name ELSE UPPER(typ.name) END AS UsedDataType,
CONVERT(INT, OdbcPrec(col.xtype, col.length, col.xprec) + spt_dtp.charbin) AS CharacterMaxLength,
NULLIF(col.xprec, 0) AS NumericPrecision,
col.scale AS NumericScale,
CONVERT(SYSNAME, CASE WHEN typ.xusertype > 256 THEN typ.name ELSE NULL END) AS UserDefinedDataType,
OBJECT_NAME(cdefault) AS ColumnDefaultName ,
typ.CollationID
FROM
sysobjects obj,
master.dbo.spt_datatype_info spt_dtp,
systypes typ,
syscolumns col
LEFT OUTER JOIN syscomments com on col.cdefault = com.id AND com.colid = 1,
master.dbo.syscharsets a_cha
WHERE
obj.id = col.id AND
typ.xtype = spt_dtp.ss_dtype AND
(spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2) AND
obj.xtype in ('U', 'V', 'P') AND
col.xusertype = typ.xusertype AND
(
spt_dtp.AUTO_INCREMENT IS NULL OR spt_dtp.AUTO_INCREMENT = 0) AND
a_cha.id = ISNULL(CONVERT(TINYINT, CollationPropertyFromID(col.collationid, 'sqlcharset')),
CONVERT(TINYINT, ServerProperty('sqlcharset'))
) and obj.type = 'u'

) a
ORDER BY
TableName, ColumnPosition ASC

Mapping Source and Destination columns

can somebody show an example of how to map source and destination columns when uploading a file to sql server?

Also, please send me the mapping when i want to map source to different destination columns.

I think you have to to be a bit more specific to get answers that will help you.

Mapping one table to another in diffirent databases

I want to map a table1 in one database to a table2 in another database. That way I can populate the table2 with the information that table1 has. How would you go about doing this. Very new at this and need help! Thanks alot .Do you mean that if an INSERT, UPDATE or DELETE Occurs you wan the action reflected in that table?|||Originally posted by Brett Kaiser
Do you mean that if an INSERT, UPDATE or DELETE Occurs you wan the action reflected in that table?

yeah, whenever something getts updated in one table in the database, the results will also reflect on the other table that lies it the other database. I am under the impression that yu have to map the databases together for that to happen. Is this something that is done with DTS? hopefully that makes a little more sence.|||Sounds like a job for triggers.|||Transactional replication can also be an answer.

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.

Mapping from flatfile to SQL server database...

Hi!
Im trying to find existing software to map from flatfiles (mostly .txt) to a
database running on MS SQL Server. The files are recieved by mail, and made
available to my program, and are supposed to be read and processed in a way
that extracts the data from the files and inserts it into the database.
If I had the time and skill, I would love to do this myself, but under the
current circumstances i need to find some sort of existing SDK to do this (o
r
an easy way to program it).
Any suggestions?Christian Johansen wrote:

> Hi!
> Im trying to find existing software to map from flatfiles (mostly .txt) to
a
> database running on MS SQL Server. The files are recieved by mail, and mad
e
> available to my program, and are supposed to be read and processed in a wa
y
> that extracts the data from the files and inserts it into the database.
> If I had the time and skill, I would love to do this myself, but under the
> current circumstances i need to find some sort of existing SDK to do this
(or
> an easy way to program it).
> Any suggestions?
Have you considered using DTS or Integration Services?
http://www.sqldts.com/
www.microsoft.com/sql/technologies/...on/default.mspx
Alternatively, there are many integration tool vendors whose products
will do the job. Here is a selection:
www.abinitio.com
ibm.ascential.com
www.datamirror.com
www.datawatch.com
www.embarcadero.com
www.informatica.com
www.pervasive.com
David Portas
SQL Server MVP
--|||
"David Portas" wrote:

> Christian Johansen wrote:
>
> Have you considered using DTS or Integration Services?
> http://www.sqldts.com/
> www.microsoft.com/sql/technologies/...on/default.mspx
> Alternatively, there are many integration tool vendors whose products
> will do the job. Here is a selection:
> www.abinitio.com
> ibm.ascential.com
> www.datamirror.com
> www.datawatch.com
> www.embarcadero.com
> www.informatica.com
> www.pervasive.com
> --
> David Portas
> SQL Server MVP
> --
>
Ahh, just what I was looking for!
Thank you very much. Merry Christmas :)

Mapping Elements to Database Fields in XSD for Bulkload

Hi Everyone,
Was wondering whether it is possible to map an element in a xml
document to a field in db table which has a different name.
A fragment of my Bulkload.xsd is below: Say I Changed my XML document
so that Event_ID was called EE_ID, could I still get EE_ID to map to
the Event_ID field in the database?
<xsd:element name="XML_WORKBOOKS" msch:relation="XML_WORKBOOKS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Event_ID" type="xsd:int" />
<xsd:element name="LastAuthor" type="xsd:string" />
<xsd:element name="Version" type="xsd:string" />
<xsd:element name="SaveComment" type="xsd:string" />
<xsd:element name="TM_ID" type="xsd:int" />
<xsd:element name="File_ID" type="xsd:int" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Thanks for your help.
Yes, just use the field annotation.
e.g.
<xsd:element name="EE_ID" type="xsd:int" msch:field="Event_ID"/>
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"jnc" <j_culverwell@.yahoo.co.uk> wrote in message
news:44a3f856.0405091143.297ea942@.posting.google.c om...
> Hi Everyone,
> Was wondering whether it is possible to map an element in a xml
> document to a field in db table which has a different name.
> A fragment of my Bulkload.xsd is below: Say I Changed my XML document
> so that Event_ID was called EE_ID, could I still get EE_ID to map to
> the Event_ID field in the database?
>
> <xsd:element name="XML_WORKBOOKS" msch:relation="XML_WORKBOOKS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Event_ID" type="xsd:int" />
> <xsd:element name="LastAuthor" type="xsd:string" />
> <xsd:element name="Version" type="xsd:string" />
> <xsd:element name="SaveComment" type="xsd:string" />
> <xsd:element name="TM_ID" type="xsd:int" />
> <xsd:element name="File_ID" type="xsd:int" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> Thanks for your help.
|||Thanks, exactly what I was after.
Cheers....Jim
sql

Mapping database in XSD

I have an xsd document that I want to map to a database.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/
XMLSchema">
<xs:element name="ComputerScan">
<xs:complexType>
<xs:sequence>
<xs:element name="scanheader">
<xs:complexType>
<xs:sequence>
<xs:element name="ScanDate" type="xs:string" />
<xs:element name="UniqueID" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="computer">
<xs:complexType>
<xs:sequence>
<xs:element name="ComputerName" type="xs:string" />
<xs:element name="ClientID" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="scans">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="scan">
<xs:complexType>
<xs:sequence>
<xs:element name="scanattributes">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded"
name="scanattribute">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="GUID"
type="xs:string" use="required" />
<xs:attribute name="GUParentID"
type="xs:string" use="required" />
<xs:attribute name="Name"
type="xs:string" use="required" />
<xs:attribute name="LastModified"
type="xs:dateTime" use="required" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ID" type="xs:decimal"
use="required" />
<xs:attribute name="Section" type="xs:string"
use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Here is a snippet of the xml this applies to(not whole thing because
it is very large)
<?xml version="1.0" encoding="utf-8"?>
<ComputerScan>
<scanheader>
<ScanDate>6/13/2007 8:00:10 AM</ScanDate>
<UniqueID>20:41:53:59:4E:FF</UniqueID>
</scanheader>
<computer>
<ComputerName>DSSESCDVLPR02</ComputerName>
<ClientID>New Client</ClientID>
</computer>
<scans>
<scan ID="1.0" Section="Drives">
<scanattributes>
<scanattribute GUID="baacad7f-dd5b-4883-a24e-1d4d66a32345"
GUParentID="" Name="Letter"
LastModified="2007-06-13T08:00:54.937888-04:00">C:</scanattribute>
<scanattribute GUID="9b393147-9085-4068-9ce4-fd65209b5d02"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Drive Type"
LastModified="2007-06-13T08:00:54.9679312-04:00">Local Disk</
scanattribute>
<scanattribute GUID="2ab90c79-9baa-49a7-8065-dae46b3b1727"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Percent Used"
LastModified="2007-06-13T08:00:54.9679312-04:00">97%</scanattribute>
<scanattribute GUID="5d327b08-6bde-4102-9aaa-b86024ab61e8"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Used Space"
LastModified="2007-06-13T08:00:54.9679312-04:00">22.89 GB</
scanattribute>
<scanattribute GUID="7f685ca6-fec2-476e-bad9-7348eea24ba6"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Free Space"
LastModified="2007-06-13T08:00:54.9679312-04:00">0.54 GB</
scanattribute>
<scanattribute GUID="24dee5e9-4b94-44ff-825e-a257c8f92786"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Total Space"
LastModified="2007-06-13T08:00:54.9679312-04:00">23.44 GB</
scanattribute>
<scanattribute GUID="b1963c6a-f77e-43f6-a86c-ca6453ab6d8f"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Drive Name"
LastModified="2007-06-13T08:00:54.9679312-04:00">C:</scanattribute>
<scanattribute GUID="e4a816a7-c62f-45a4-bb0c-ffe5e1cb67a9"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="File System
Type" LastModified="2007-06-13T08:00:54.9679312-04:00">NTFS</
scanattribute>
<scanattribute GUID="0b9d5e25-dd91-4e96-bf18-59f0b42f71eb"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Drive Serial
Number" LastModified="2007-06-13T08:00:54.9679312-04:00">7468D306</
scanattribute>
<scanattribute GUID="dfed9095-1e25-4789-bdae-4b7b51502170"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Sectors Per
Cluster" LastModified="2007-06-13T08:00:54.9679312-04:00">8</
scanattribute>
<scanattribute GUID="59c3b840-c2f6-4ced-a1ac-604e117f5ef4"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Bytes Per
Cluster" LastModified="2007-06-13T08:00:54.9679312-04:00">512</
scanattribute>
<scanattribute GUID="f2165a88-35ca-4507-a3eb-4bb36c386d68"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Free Clusters"
LastModified="2007-06-13T08:00:54.9679312-04:00">141935</
scanattribute>
<scanattribute GUID="cfaf034a-517c-46c1-b327-e9f41293d417"
GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Total
Clusters" LastModified="2007-06-13T08:00:54.9679312-04:00">6144854</
scanattribute>
<scanattribute GUID="bab83616-3e69-4a28-9bb3-c59508fcdc72"
GUParentID="" Name="Letter"
LastModified="2007-06-13T08:00:54.9679312-04:00">D:</scanattribute>
<scanattribute GUID="078da3b2-1895-4e5c-9dbf-971d95267099"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Drive Type"
LastModified="2007-06-13T08:00:54.9679312-04:00">Local Disk</
scanattribute>
<scanattribute GUID="1dbd0b90-006c-40a1-a828-182a32579e35"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Percent Used"
LastModified="2007-06-13T08:00:54.9679312-04:00">82%</scanattribute>
<scanattribute GUID="e94dbfb9-b15c-4382-a98b-2cfc8b6a5a68"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Used Space"
LastModified="2007-06-13T08:00:54.9679312-04:00">11.46 GB</
scanattribute>
<scanattribute GUID="5cab4178-9f86-4ca8-8c22-23a4b1b99ffa"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Free Space"
LastModified="2007-06-13T08:00:54.9679312-04:00">2.35 GB</
scanattribute>
<scanattribute GUID="ad5621c1-72f8-4d23-9be1-d7d90bc5fa07"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Total Space"
LastModified="2007-06-13T08:00:54.9679312-04:00">13.81 GB</
scanattribute>
<scanattribute GUID="43e63657-7436-4b65-b178-2ce839c2d204"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Drive Name"
LastModified="2007-06-13T08:00:54.9679312-04:00">D:</scanattribute>
<scanattribute GUID="4cb8a3e9-6a03-42d2-b2e8-90e59ccbced1"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="File System
Type" LastModified="2007-06-13T08:00:54.9679312-04:00">NTFS</
scanattribute>
<scanattribute GUID="31c7f4e0-2730-4e46-b5d1-92f9c60a5472"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Drive Serial
Number" LastModified="2007-06-13T08:00:54.9679312-04:00">B4289EC3</
scanattribute>
<scanattribute GUID="4c52feba-a62b-42f5-9241-3bacc9ccb773"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Sectors Per
Cluster" LastModified="2007-06-13T08:00:54.9679312-04:00">8</
scanattribute>
<scanattribute GUID="a53d3df3-f935-4300-ba1b-59d5dcc406f1"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Bytes Per
Cluster" LastModified="2007-06-13T08:00:54.9679312-04:00">512</
scanattribute>
<scanattribute GUID="299b062c-6246-401d-8ac4-9d73c6556825"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Free Clusters"
LastModified="2007-06-13T08:00:54.9679312-04:00">141935</
scanattribute>
<scanattribute GUID="8665555b-e8be-44e5-814d-1ed781cb2fd7"
GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Total
Clusters" LastModified="2007-06-13T08:00:54.9679312-04:00">6144854</
scanattribute>
<scanattribute GUID="5827ab65-10ba-4630-a3d5-917e3b160c06"
GUParentID="" Name="Letter"
LastModified="2007-06-13T08:00:54.9679312-04:00">E:</scanattribute>
<scanattribute GUID="30e4cb79-d178-4126-980a-7d954bcf535b"
GUParentID="5827ab65-10ba-4630-a3d5-917e3b160c06" Name="Drive Type"
LastModified="2007-06-13T08:00:54.9679312-04:00">Compact Disc</
scanattribute>
</scanattributes>
</scan>
<scan ID="2.0" Section="Error Logs">
<scanattributes>
.....................
I have three tables I need information from my xml mapped to.
tblAsset has columns for ComputerName, ScanDate, and UniqueID
tblScan has columns for the exact same data.
tblScanDetails has columns for UniqueID, GUID, GUParentID, Name,
LastModified, and the text for all the scanattributes.
How would I change my xsd to map these tables and columns'On Jul 11, 11:31 am, rhaazy <rha...@.gmail.com> wrote:
> I have an xsd document that I want to map to a database.
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema attributeFormDefault="unqualified"
> elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/
> XMLSchema">
> <xs:element name="ComputerScan">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="scanheader">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ScanDate" type="xs:string" />
> <xs:element name="UniqueID" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="computer">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ComputerName" type="xs:string" />
> <xs:element name="ClientID" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="scans">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="scan">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="scanattributes">
> <xs:complexType>
> <xs:sequence minOccurs="0">
> <xs:element maxOccurs="unbounded"
> name="scanattribute">
> <xs:complexType>
> <xs:simpleContent>
> <xs:extension base="xs:string">
> <xs:attribute name="GUID"
> type="xs:string" use="required" />
> <xs:attribute name="GUParentID"
> type="xs:string" use="required" />
> <xs:attribute name="Name"
> type="xs:string" use="required" />
> <xs:attribute name="LastModified"
> type="xs:dateTime" use="required" />
> </xs:extension>
> </xs:simpleContent>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> <xs:attribute name="ID" type="xs:decimal"
> use="required" />
> <xs:attribute name="Section" type="xs:string"
> use="required" />
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
> Here is a snippet of the xml this applies to(not whole thing because
> it is very large)
> <?xml version="1.0" encoding="utf-8"?>
> <ComputerScan>
> <scanheader>
> <ScanDate>6/13/2007 8:00:10 AM</ScanDate>
> <UniqueID>20:41:53:59:4E:FF</UniqueID>
> </scanheader>
> <computer>
> <ComputerName>DSSESCDVLPR02</ComputerName>
> <ClientID>New Client</ClientID>
> </computer>
> <scans>
> <scan ID="1.0" Section="Drives">
> <scanattributes>
> <scanattribute GUID="baacad7f-dd5b-4883-a24e-1d4d66a32345"
> GUParentID="" Name="Letter"
> LastModified="2007-06-13T08:00:54.937888-04:00">C:</scanattribute>
> <scanattribute GUID="9b393147-9085-4068-9ce4-fd65209b5d02"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Drive Type"
> LastModified="2007-06-13T08:00:54.9679312-04:00">Local Disk</
> scanattribute>
> <scanattribute GUID="2ab90c79-9baa-49a7-8065-dae46b3b1727"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Percent Used"
> LastModified="2007-06-13T08:00:54.9679312-04:00">97%</scanattribute>
> <scanattribute GUID="5d327b08-6bde-4102-9aaa-b86024ab61e8"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Used Space"
> LastModified="2007-06-13T08:00:54.9679312-04:00">22.89 GB</
> scanattribute>
> <scanattribute GUID="7f685ca6-fec2-476e-bad9-7348eea24ba6"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Free Space"
> LastModified="2007-06-13T08:00:54.9679312-04:00">0.54 GB</
> scanattribute>
> <scanattribute GUID="24dee5e9-4b94-44ff-825e-a257c8f92786"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Total Space"
> LastModified="2007-06-13T08:00:54.9679312-04:00">23.44 GB</
> scanattribute>
> <scanattribute GUID="b1963c6a-f77e-43f6-a86c-ca6453ab6d8f"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Drive Name"
> LastModified="2007-06-13T08:00:54.9679312-04:00">C:</scanattribute>
> <scanattribute GUID="e4a816a7-c62f-45a4-bb0c-ffe5e1cb67a9"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="File System
> Type" LastModified="2007-06-13T08:00:54.9679312-04:00">NTFS</
> scanattribute>
> <scanattribute GUID="0b9d5e25-dd91-4e96-bf18-59f0b42f71eb"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Drive Serial
> Number" LastModified="2007-06-13T08:00:54.9679312-04:00">7468D306</
> scanattribute>
> <scanattribute GUID="dfed9095-1e25-4789-bdae-4b7b51502170"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Sectors Per
> Cluster" LastModified="2007-06-13T08:00:54.9679312-04:00">8</
> scanattribute>
> <scanattribute GUID="59c3b840-c2f6-4ced-a1ac-604e117f5ef4"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Bytes Per
> Cluster" LastModified="2007-06-13T08:00:54.9679312-04:00">512</
> scanattribute>
> <scanattribute GUID="f2165a88-35ca-4507-a3eb-4bb36c386d68"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Free Clusters"
> LastModified="2007-06-13T08:00:54.9679312-04:00">141935</
> scanattribute>
> <scanattribute GUID="cfaf034a-517c-46c1-b327-e9f41293d417"
> GUParentID="baacad7f-dd5b-4883-a24e-1d4d66a32345" Name="Total
> Clusters" LastModified="2007-06-13T08:00:54.9679312-04:00">6144854</
> scanattribute>
> <scanattribute GUID="bab83616-3e69-4a28-9bb3-c59508fcdc72"
> GUParentID="" Name="Letter"
> LastModified="2007-06-13T08:00:54.9679312-04:00">D:</scanattribute>
> <scanattribute GUID="078da3b2-1895-4e5c-9dbf-971d95267099"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Drive Type"
> LastModified="2007-06-13T08:00:54.9679312-04:00">Local Disk</
> scanattribute>
> <scanattribute GUID="1dbd0b90-006c-40a1-a828-182a32579e35"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Percent Used"
> LastModified="2007-06-13T08:00:54.9679312-04:00">82%</scanattribute>
> <scanattribute GUID="e94dbfb9-b15c-4382-a98b-2cfc8b6a5a68"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Used Space"
> LastModified="2007-06-13T08:00:54.9679312-04:00">11.46 GB</
> scanattribute>
> <scanattribute GUID="5cab4178-9f86-4ca8-8c22-23a4b1b99ffa"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Free Space"
> LastModified="2007-06-13T08:00:54.9679312-04:00">2.35 GB</
> scanattribute>
> <scanattribute GUID="ad5621c1-72f8-4d23-9be1-d7d90bc5fa07"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Total Space"
> LastModified="2007-06-13T08:00:54.9679312-04:00">13.81 GB</
> scanattribute>
> <scanattribute GUID="43e63657-7436-4b65-b178-2ce839c2d204"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Drive Name"
> LastModified="2007-06-13T08:00:54.9679312-04:00">D:</scanattribute>
> <scanattribute GUID="4cb8a3e9-6a03-42d2-b2e8-90e59ccbced1"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="File System
> Type" LastModified="2007-06-13T08:00:54.9679312-04:00">NTFS</
> scanattribute>
> <scanattribute GUID="31c7f4e0-2730-4e46-b5d1-92f9c60a5472"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Drive Serial
> Number" LastModified="2007-06-13T08:00:54.9679312-04:00">B4289EC3</
> scanattribute>
> <scanattribute GUID="4c52feba-a62b-42f5-9241-3bacc9ccb773"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Sectors Per
> Cluster" LastModified="2007-06-13T08:00:54.9679312-04:00">8</
> scanattribute>
> <scanattribute GUID="a53d3df3-f935-4300-ba1b-59d5dcc406f1"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Bytes Per
> Cluster" LastModified="2007-06-13T08:00:54.9679312-04:00">512</
> scanattribute>
> <scanattribute GUID="299b062c-6246-401d-8ac4-9d73c6556825"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Free Clusters"
> LastModified="2007-06-13T08:00:54.9679312-04:00">141935</
> scanattribute>
> <scanattribute GUID="8665555b-e8be-44e5-814d-1ed781cb2fd7"
> GUParentID="bab83616-3e69-4a28-9bb3-c59508fcdc72" Name="Total
> Clusters" LastModified="2007-06-13T08:00:54.9679312-04:00">6144854</
> scanattribute>
> <scanattribute GUID="5827ab65-10ba-4630-a3d5-917e3b160c06"
> GUParentID="" Name="Letter"
> LastModified="2007-06-13T08:00:54.9679312-04:00">E:</scanattribute>
> <scanattribute GUID="30e4cb79-d178-4126-980a-7d954bcf535b"
> GUParentID="5827ab65-10ba-4630-a3d5-917e3b160c06" Name="Drive Type"
> LastModified="2007-06-13T08:00:54.9679312-04:00">Compact Disc</
> scanattribute>
> </scanattributes>
> </scan>
> <scan ID="2.0" Section="Error Logs">
> <scanattributes>
> .....................
> I have three tables I need information from my xml mapped to.
> tblAsset has columns for ComputerName, ScanDate, and UniqueID
> tblScan has columns for the exact same data.
> tblScanDetails has columns for UniqueID, GUID, GUParentID, Name,
> LastModified, and the text for all the scanattributes.
> How would I change my xsd to map these tables and columns'
please disregard this topic, i was .

Mapping columns of complexType to different table

In my xml file I have an element called pointInTime which has 4 elements
associated with it.
Among these 4 fields, I need to map 1 field to one SQL table and remaining
fields to another table.
How do I write XSD schema for this.
<xs:element name="pointInTime" sql:relation="SpanFileDetailsTemp">
<xs:complexType>
<xs:sequence>
<xs:element ref="date" sql:field="FileDate"/>
<xs:element ref="isSetl"/>
<xs:element ref="setlQualifier"/>
<xs:element ref="clearingOrg"/>
</xs:sequence>
</xs:complexType>
</xs:element>
I tried to map 1 field to the table.
Now should I repeat this for other table?
But If I repeat this, the lement name PointInTime would be duplicated,
right?
So, should I use <xs:element ref="PointInTime" sql:relation="xxxx"> like
this ?
What is the difference between <xs:> and <xsd:>
I found both these while surfing about XSD's.
Regards
Meena
Meenakshi wrote:

> What is the difference between <xs:> and <xsd:>
> I found both these while surfing about XSD's.
Whether the prefix is xs or xsd or some other prefix (e.g. pf) does not
matter in terms of XML, what matters is the namespace URI bound to the
prefix. So you can use
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"></xs:schema>
or
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"></xsd:schema>
or
<pf:schema xmlns:pf="http://www.w3.org/2001/XMLSchema"></pf:schema>
or any other allowed prefix you like as long as the prefix is bound to
the namespace URI http://www.w3.org/2001/XMLSchema.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Mapping columns of complexType to different table

In my xml file I have an element called pointInTime which has 4 elements
associated with it.
Among these 4 fields, I need to map 1 field to one SQL table and remaining
fields to another table.
How do I write XSD schema for this.
<xs:element name="pointInTime" sql:relation="SpanFileDetailsTemp">
<xs:complexType>
<xs:sequence>
<xs:element ref="date" sql:field="FileDate"/>
<xs:element ref="isSetl"/>
<xs:element ref="setlQualifier"/>
<xs:element ref="clearingOrg"/>
</xs:sequence>
</xs:complexType>
</xs:element>
I tried to map 1 field to the table.
Now should I repeat this for other table?
But If I repeat this, the lement name PointInTime would be duplicated,
right?
So, should I use <xs:element ref="PointInTime" sql:relation="xxxx"> like
this ?
What is the difference between <xs:> and <xsd:>
I found both these while surfing about XSD's.
Regards
MeenaMeenakshi wrote:

> What is the difference between <xs:> and <xsd:>
> I found both these while surfing about XSD's.
Whether the prefix is xs or xsd or some other prefix (e.g. pf) does not
matter in terms of XML, what matters is the namespace URI bound to the
prefix. So you can use
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"></xs:schema>
or
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"></xsd:schema>
or
<pf:schema xmlns:pf="http://www.w3.org/2001/XMLSchema"></pf:schema>
or any other allowed prefix you like as long as the prefix is bound to
the namespace URI http://www.w3.org/2001/XMLSchema.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/sql

Mapping columns in custom Destination component

I am working on a destination component where the columns in the destination are already set. I want to give the user the option to map columns, similar to the way the Excel destination component does it. So, the Available Input Columns could be mapped to Available Destination Columns. The Available Input Columns would come from a connection from some other component (either a source or a transformation component), and the Available Destination Columns would be generated from the Data Source.

Is there a way to do this without creating a custom ui?

Yes, you can get this for free in the advanced UI, if you store your Available Destination Columns to the ExternalMetadataColumnCollection on the destination input and set IsUsed flag on this collection to true.

HTH.

|||Man, I suspected it had to do with the external metadata columns, but I couldn't get them to show in the UI. Setting the IsUsed property to "true" was the answer. Thanks a lot Bob.

mapping

hi! i have two different databases (SQL 2005 and Oracle) and i need to map their tables with one another. how will i do this?

thanks

Hi,

you can create a Linked server with Oracle. and query the oracle table as local tables. or you can use OpenRowset to query oracle database

|||

ahm, I'm just new at using oracle and i'm a little bit confused.. could you please explain a little more?

thanks!

|||If you create a linked server for you Oracle Server in SQL Server (See the Books online for SQL Server for detailed information) you can access the tables of the Oracle instance using the four part notation of SQL Server:

SELECT * FROM OracleLinkedServerName..Schema.ObjectName

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

However, I found that in order to get it to work, I needed to use brackets around each element such as the following: SELECT * FROM [LINKEDSERVERNAME]..[DATABASENAME].[TABLENAME]. I wouldn't have found it out had I not used the "Script table as..." command by right-clicking the table in the Object Explorer! This wasn't mentioned in BOL.

Brian J. Matuschak

|||

Actually there is no need to use the brackets unless you have special characters in the names.

Map One generic Input column to multiple Destination column

I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".

I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.

But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.

So how do I go about doing this the smart way?

thanks in advance!

Hi,

You can use "Copy Column" transformation component to copy one input column to multiple output columns. If you have to perform some computing between original and new columns, you can use "Derive Column" transformation.

Jean-Pierre Riehl

http://blog.djeepy1.net

http://www.bewise.fr

|||Sorry not very elegant, this is more work than creating all the output columns one by one. I want to create one DataType_NULL Column which I want to reuse to map to the destination columns.

|||For what you are describing I would probably just call the stored procedure from inside the script component. As you have seen, the OLE Command doesn't really support this, so script, Copy Column, or Derived Column are the only way to do this that I am aware of.

map drive

If my computer has a map drive, i want to delete it, how to do it? Is that right if i right click the map drive and click the disconnect?Yes, that should allow you to disconnect the mapped drive.

-PatP|||From a command prompt you can also run

net use z: /del

(replace z: with the mapped drive letter)|||From a command prompt you can also run

net use z: /del

(replace z: with the mapped drive letter)

well if we are in the sql server forum, how about

exec xp_cmdshell 'net use x: /delete'

Map based reporting

Im going to throw this out and maybe someone has some resources to
point me at.
Map based reporting within the US based on Zip supported by census data
? Has anyone done this ? Are there any Zip Maps out there to use as
templates ?
Say I have a database of customers and addresses and I want to show
state and then zip based drill down of approximate percentages of
cutomer saturation / return for a given area.
Any ideas anyone ?
ChrisWertmanTheMad wrote:
> Any ideas anyone ?
We developed an ASP.Net-Solution with mappoint
(http://www.microsoft.com/mappoint/default.mspx)
By clicking on a zip-based-area in the map the suggested report opened.
regards
Frank|||We used Spatially Aware's Map Suite Product, you can build up web pages with
interactive maps or you can retrieve custom maps via an exteral URL in SQL
Reporting Services.
www.spatiallyaware.com
"WertmanTheMad" wrote:
> Im going to throw this out and maybe someone has some resources to
> point me at.
> Map based reporting within the US based on Zip supported by census data
> ? Has anyone done this ? Are there any Zip Maps out there to use as
> templates ?
> Say I have a database of customers and addresses and I want to show
> state and then zip based drill down of approximate percentages of
> cutomer saturation / return for a given area.
> Any ideas anyone ?
> Chris
>sql