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

Mappings in the packages

Hi all,

Is it possible to get information of the mappings availabe in the packages?, like the column mappings in the OLE Destination control, column mappings in the merge join control etc.,. I want the information of source and destination in the mappings(name of the DB, table). I want that to be in some file like excel file. Inform me is there a way to get it?

Thanks in advance,

Saravanan.W.S

Currently there is not way to export that kind of mapping metadata to Excel. Depending on your needs, however, you may find the SQL Server 2005 Business Intelligence Metadata Samples Toolkit useful. You can download it here ...

http://www.microsoft.com/downloads/details.aspx?FamilyID=11DAA4D1-196D-4F2A-B18F-891579C364F4&displaylang=en

Donald

Mapping two schema definitions to one column

Hi,
I have a xml data file that looks like this
<CT_DATASET>
<DATE-INTERVENTIONDATE>
<YEARS>0</YEARS>
<MONTHS>0</MONTHS>
<DAYS>-1</DAYS>
</DATE-INTERVENTIONDATE>
<DESCTEXT>Preoperative CT Diagnosis</DESCTEXT>
<NUMBER_OF_SLICES>58</NUMBER_OF_SLICES>
<DICOM_TAGS>
<TAGNR>00080014</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080016</TAGNR>
<TAGVALUE>1.2.840.10008.5.1.4.1.1.4</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080018</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102.0.2187.8.1.0.7</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080070</TAGNR>
<TAGVALUE>Toshiba </TAGVALUE>
</DICOM_TAGS>
</CT_DATASET>
And I want to map the multiple tagnr and tagvalue to one column for the
patient.
My schema file looks liek this:
<xs:element name="CT_DATASET" sql:relation="CT_DATA"
sql:overflow-field="ADDITIONAL">
<xs:complexType>
<xs:sequence>
<xs:element ref="DATE-INTERVENTIONDATE" />
<xs:element ref="DESCTEXT" />
<xs:element ref="NUMBER_OF_SLICES" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="DICOM_TAGS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NUMBER_OF_SLICES" type="xs:string" />
<xs:element name="DICOM_TAGS" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DATE-INTERVENTIONDATE" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="YEARS" />
<xs:element ref="MONTHS" />
<xs:element ref="DAYS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="YEARS" type="xs:string" sql:field="years"/>
<xs:element name="MONTHS" type="xs:string" sql:field="months" />
<xs:element name="DAYS" type="xs:string" sql:field="days"/>
<xs:element name="TAGNR" sql:field="TAG_NO:"/>
<xs:element name="TAGVALUE" sql:field="TAG VALUE"/>
</xs:schema>
I get an error which says:
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Does anybody know a way around. Any help would be appreciated. Please
do help!!!Hello Prakruthi,
Are you trying to use this as a validation schema in an XML Schema collectio
n
or a XDR for mapping queries?

> I get an error which says:
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Does anybody know a way around. Any help would be appreciated. Please
> do help!!!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Im trying to use this as a validation schema. And as you can see from
the xml data , each patient has multiple dicom tags and numbers. I want
to be able to map all these numbers into a single column. Is this
possible?|||Hi Kent,
I forgot to mention that Tag Numbers and Tag values are two separate
columns. But each column should be able to have multiple values.
Regards,
Prakruthi|||Hello Prakruthi,
Part of problem is that the schema you posted isn't a valid XSD schema:
a. The sql namespace (aliased by sql:) isn't defined by your schema.
b. The schema has some other problems, so I rewrote it -- sans the SQL parts
since I'm not sure what these are or do in your processor.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="q
ualified">
<xs:element name="CT_DATASET">
<xs:complexType>
<xs:sequence>
<xs:element name="DATE-INTERVENTIONDATE">
<xs:complexType>
<xs:sequence>
<xs:element name="YEARS" type="xs:string"/>
<xs:element name="MONTHS" type="xs:string"/>
<xs:element name="DAYS" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="NUMBER_OF_SLICES" type="xs:string"/>
<xs:element ref="DICOM_TAGS" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DICOM_TAGS">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="TAGNR" type="xs:string"/>
<xs:element name="TAGVALUE" type="xs:string"/>
</xs:schema>
I was able to catalog is into a SQL Sever 2005 XML Schema Collection succesf
ully.
Does any of that help?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Dear Kent,
Thanks for reorganising my schema in the right manner. But I dont think
you understood what I meant to say. Even when I used your modified
schema I could not map the three tagnr and tagvalues associated with
one one single patient into the tagnr and tagvalue column.I get an
error that says
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Is it not possible to map three different values to one column of the
same row? I hope you can help me out again this time.
Regards,
Prakruthi|||Hello Prakruthi,
That's why I asked if you were using this an mapping schema or as a validati
on
schema. You said "validation," so I fixed it for that. However, I don't beli
eve
you can target more than one value into a column using a single mapping.
Sadly, I don't have the time to work on this today before going on the road
for the next couple of w. However, it looks like Michael is back online,
so he might be able to help more.:)

> Thanks for reorganising my schema in the right manner. But I dont
> think
> you understood what I meant to say. Even when I used your modified
> schema I could not map the three tagnr and tagvalues associated with
> one one single patient into the tagnr and tagvalue column.I get an
> error that says
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Is it not possible to map three different values to one column of the
> same row? I hope you can help me out again this time.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi|||I have forwarded it to our mapping schema experts and I hope they will get
back to you.
Ping me if that is not the case.
Michael
"Prakruthi" <prakruthi.rao@.gmail.com> wrote in message
news:1143210046.980577.250430@.e56g2000cwe.googlegroups.com...
> Hi Kent,
> I wanted to say im not using it as a validation schema. I apologise for
> the typo error. I hope micheal would help me out with this issue.
> Regards,
> Prakruthi
>

Mapping two schema definitions to one column

Hi,
I have a xml data file that looks like this
<CT_DATASET>
<DATE-INTERVENTIONDATE>
<YEARS>0</YEARS>
<MONTHS>0</MONTHS>
<DAYS>-1</DAYS>
</DATE-INTERVENTIONDATE>
<DESCTEXT>Preoperative CT Diagnosis</DESCTEXT>
<NUMBER_OF_SLICES>58</NUMBER_OF_SLICES>
<DICOM_TAGS>
<TAGNR>00080014</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080016</TAGNR>
<TAGVALUE>1.2.840.10008.5.1.4.1.1.4</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080018</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102.0.2187.8.1.0.7< /TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080070</TAGNR>
<TAGVALUE>Toshiba </TAGVALUE>
</DICOM_TAGS>
</CT_DATASET>
And I want to map the multiple tagnr and tagvalue to one column for the
patient.
My schema file looks liek this:
<xs:element name="CT_DATASET" sql:relation="CT_DATA"
sql:overflow-field="ADDITIONAL">
<xs:complexType>
<xs:sequence>
<xs:element ref="DATE-INTERVENTIONDATE" />
<xs:element ref="DESCTEXT" />
<xs:element ref="NUMBER_OF_SLICES" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="DICOM_TAGS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NUMBER_OF_SLICES" type="xs:string" />
<xs:element name="DICOM_TAGS" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DATE-INTERVENTIONDATE" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="YEARS" />
<xs:element ref="MONTHS" />
<xs:element ref="DAYS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="YEARS" type="xs:string" sql:field="years"/>
<xs:element name="MONTHS" type="xs:string" sql:field="months" />
<xs:element name="DAYS" type="xs:string" sql:field="days"/>
<xs:element name="TAGNR" sql:field="TAG_NO:"/>
<xs:element name="TAGVALUE" sql:field="TAG VALUE"/>
</xs:schema>
I get an error which says:
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Does anybody know a way around. Any help would be appreciated. Please
do help!!!
Hello Prakruthi,
Are you trying to use this as a validation schema in an XML Schema collection
or a XDR for mapping queries?

> I get an error which says:
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Does anybody know a way around. Any help would be appreciated. Please
> do help!!!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent,
Im trying to use this as a validation schema. And as you can see from
the xml data , each patient has multiple dicom tags and numbers. I want
to be able to map all these numbers into a single column. Is this
possible?
|||Hi Kent,
I forgot to mention that Tag Numbers and Tag values are two separate
columns. But each column should be able to have multiple values.
Regards,
Prakruthi
|||Hello Prakruthi,
Part of problem is that the schema you posted isn't a valid XSD schema:
a. The sql namespace (aliased by sql isn't defined by your schema.
b. The schema has some other problems, so I rewrote it -- sans the SQL parts
since I'm not sure what these are or do in your processor.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="CT_DATASET">
<xs:complexType>
<xs:sequence>
<xs:element name="DATE-INTERVENTIONDATE">
<xs:complexType>
<xs:sequence>
<xs:element name="YEARS" type="xs:string"/>
<xs:element name="MONTHS" type="xs:string"/>
<xs:element name="DAYS" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="NUMBER_OF_SLICES" type="xs:string"/>
<xs:element ref="DICOM_TAGS" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DICOM_TAGS">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="TAGNR" type="xs:string"/>
<xs:element name="TAGVALUE" type="xs:string"/>
</xs:schema>
I was able to catalog is into a SQL Sever 2005 XML Schema Collection succesfully.
Does any of that help?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Dear Kent,
Thanks for reorganising my schema in the right manner. But I dont think
you understood what I meant to say. Even when I used your modified
schema I could not map the three tagnr and tagvalues associated with
one one single patient into the tagnr and tagvalue column.I get an
error that says
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Is it not possible to map three different values to one column of the
same row? I hope you can help me out again this time.
Regards,
Prakruthi
|||Hello Prakruthi,
That's why I asked if you were using this an mapping schema or as a validation
schema. You said "validation," so I fixed it for that. However, I don't believe
you can target more than one value into a column using a single mapping.
Sadly, I don't have the time to work on this today before going on the road
for the next couple of week. However, it looks like Michael is back online,
so he might be able to help more.

> Thanks for reorganising my schema in the right manner. But I dont
> think
> you understood what I meant to say. Even when I used your modified
> schema I could not map the three tagnr and tagvalues associated with
> one one single patient into the tagnr and tagvalue column.I get an
> error that says
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Is it not possible to map three different values to one column of the
> same row? I hope you can help me out again this time.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi
|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi
|||I have forwarded it to our mapping schema experts and I hope they will get
back to you.
Ping me if that is not the case.
Michael
"Prakruthi" <prakruthi.rao@.gmail.com> wrote in message
news:1143210046.980577.250430@.e56g2000cwe.googlegr oups.com...
> Hi Kent,
> I wanted to say im not using it as a validation schema. I apologise for
> the typo error. I hope micheal would help me out with this issue.
> Regards,
> Prakruthi
>

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 data types

I am building a .NET app that uses sqlDataReader.GetSchemaTable method. this
returns a table which includes the column "Provider Type" which is the
database data type of the column, however it is a numeric value, are the
mappings between these numeric values and the textual data type names
documented anywhere?
eg Money appears to be 9
and Date appears to be 15For SQL Server 2000, the systypes system table contains mappings between the
data type name and a number called 'xtype'. They don't seem to line up with
your observations though.
xtype Data type name
-- --
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar
"guy" wrote:

> I am building a .NET app that uses sqlDataReader.GetSchemaTable method. th
is
> returns a table which includes the column "Provider Type" which is the
> database data type of the column, however it is a numeric value, are the
> mappings between these numeric values and the textual data type names
> documented anywhere?
> eg Money appears to be 9
> and Date appears to be 15
>|||Mark,
hmmm interesting, so far i have found:-
2 bit
3 char
6 float
8 Int
9 money
15 smalldatetime
16 smallint
22 varchar
cheers
"Mark Williams" wrote:
> For SQL Server 2000, the systypes system table contains mappings between t
he
> data type name and a number called 'xtype'. They don't seem to line up wit
h
> your observations though.
> xtype Data type name
> -- --
> 34 image
> 35 text
> 36 uniqueidentifier
> 48 tinyint
> 52 smallint
> 56 int
> 58 smalldatetime
> 59 real
> 60 money
> 61 datetime
> 62 float
> 98 sql_variant
> 99 ntext
> 104 bit
> 106 decimal
> 108 numeric
> 122 smallmoney
> 127 bigint
> 165 varbinary
> 167 varchar
> 173 binary
> 175 char
> 189 timestamp
> 231 sysname
> 231 nvarchar
> 239 nchar
>
> "guy" wrote:
>

Mapping Columns Automatically?

New to SSIS...

I created a new package with a source and destination and manually created the output column with data type, etc. Works. The issue is say the table has 200 columns to export.. I dont want to create these by hand. How can I just say export them all to csv format and not have to specify and map each and every column?

Use the Export Data Wizard in SSMS.

-Jamie

|||Thats fine and dandy when starting from scratch. But if you have spent a lot of time building scripts and other actions in an existing package... it seems that it should be simple to add all columns to an existing text export. This seems like it would be such a common issue there has to be a solution.|||

You could replace your existing source adapter with a new one. The default behaviour is to select all columns which by the sound of it is what you want.

The new columns will automatically appear in the metadata of downstream components.

-Jamie

|||Thanks.. I will try that and see how it goes.

Mapping Column Headers from Source to Rows in a spreadsheet

Hello,

I am trying to do the following:

I have been given an MS Access Database that has a table with columns

I have to create a spreadsheet that will have the data stored in the column header as a row (essentially we are creating a spreadsheet that records all of the different columns in all of the different tables in the MS Access DB).

Any suggestions?

Where the problem is?

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.

Manualy Create IDENTITY Column inside ControlFlow

Dear Friends... I'm having a problem...

I want to manually create the identity column for a table...

I have some dataflws, and in each dataflow I insert values in this table...

I need to start the controlflow in a SQL task to get the last ID and save it in a global variable with name D_INST_IDENTITY.

And in each dataflow I have a script component transform, to get the ID... using a local variable COUNTER! and for each row I increment this value...

Until this step there is no problem... the problem starts here...:

I need to refresh the global variable in the final of each dataflow in order that in the next sequence dataflow I have D_INST_IDENTITY refreshed......

D_INST_IDENTITY = D_INST_IDENTITY + COUNTER

How can I do it? I have a RowCount transform next the script component, but generates errors...

What do you think I can do it?
Thanks!!

Can you not just use an Execute SQL Task to get the current max value in the target table and store is in a variable?

-Jamie

|||

I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...

Example

1. Dataflow

Return the Initial value of IDENTITY_variable from SQL Task

Refresh Identity_variable

2. Dataflow

Read Identity_variable

Refresh Identity_variable

3. Dataflow

Read Identity_variable

Refresh Identity_variable

|||

And In the SQL Task I use:

SELECT MAX(INST_ID)+1 FROM Instrumento

But if teh table is empty returns me an error... Is this statment that is usually used?

|||

PedroCGD wrote:

I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...

You don't do it IN the data-flow, you do it BEFORE the data-flow

PedroCGD wrote:

Example

1. Dataflow

Return the Initial value of IDENTITY_variable from SQL Task

Refresh Identity_variable

2. Dataflow

Read Identity_variable

Refresh Identity_variable

3. Dataflow

Read Identity_variable

Refresh Identity_variable

So you put an Execute SQL Task before each data-flow. Is there a problem with doing that?

(There are actually some cleverer ways of doing it but for now - let's keep it simple.)

-Jamie

|||

PedroCGD wrote:

And In the SQL Task I use:

SELECT MAX(INST_ID)+1 FROM Instrumento

But if teh table is empty returns me an error... Is this statment that is usually used?

It really helps if, when you get an error, you tell us the error emssage.

I'm pretty sure I can guess what it is though. Try this:

SELECT ISNULL(MAX(INST_ID), 0) +1 FROM Instrumento

-Jamie

|||

Dear Jamie,

In order I have teh best performance, I avoid to use multiple SQL tasks. Imagin that I have 20 dataflows? Why I need to execute a query in database for each dataflow, if I have a counter to automatically give me the IDENTITY column? Do you think is more consistent using SQL Task?

I have found the solution to read a global variavel, and change it inside the script component transform...

I initialize in the Script Component properties the variable INST_IDENTITY as ReadWritevariables, and in order to avoid errors inside the PreExecute method when I read the value, I changed the code as you can see:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Dim counter As Integer

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

counter = counter + 1

Row.TesteKey = counter

End Sub

Public Overrides Sub PreExecute()

Dim vars As IDTSVariables90 = Nothing

Me.VariableDispenser.LockForRead("User::INST_IDENTITY")

Me.VariableDispenser.GetVariables(vars)

counter = CType(vars(0).Value, Integer)

vars.Unlock()

MyBase.PreExecute()

End Sub

Public Overrides Sub PostExecute()

Me.ReadWriteVariables("INST_IDENTITY").Value = counter

MyBase.PostExecute()

End Sub

Protected Overrides Sub Finalize()

MyBase.Finalize()

End Sub

End Class

I was having errors if I read the value of a ReadWriteVariables inside the PreExcute Method, so, using VariableDispenser I can do it!!

Thanks!!

|||

PedroCGD wrote:

I have found the solution to read a global variavel, and change it inside the script component transform...

Cool! That is the "other way" that I alluded to earlier.

-Jamie

Manipulation in the front end

Hi

I have a scenario while creating a report where I need to maintain the same number of rows for a column, even if the data grows or shrinks for that column

example

Each Scenario is grouped by ID

Scenario 1

columnA

Row1 X X

Row2 X

Row3 X

Row4 XYZ

Scenario 2

columnA

Row1 XA

Row2 XYZ

In both scenario's I need to control from front end

the number of row should remain 4

I am grouping by row field say Type

This field Type varies for each ID group

This seems to work.

1. Use a table with a group on the ID field.

2. Use four rows in the detail section

3. On the first detail row Hidden property use this expression.

=iif(RowNumber("table1_Group1")<=4,False,True)

4. On the second detail row Hidden property use this expression.

=iif(CountRows()=1,False,True)

5. On the third detail row Hidden property use this expression.

=iif(CountRows()=1 or (CountRows()=2 and RowNumber("table1_Group1")=2) ,False,True)

6. On the third detail row Hidden property use this expression.

=iif(CountRows()=1 or (Countrows()=2 and and RowNumber("table1_Group1")=2) or (Countrows()=3 and and RowNumber("table1_Group1")=3) ,False,True)

manipulate matrix/group column heading for subtotal column

Hello all!

I'm using a matrix with a subtotal. The subtotal shows prior to the detail (first column). For the heading I have

= Fields!category.Value + " HeadCounts"

(where category is the grouping for the matrix)

Which is fine for the detail columns. But the subtotal column repeats the value for the first detail column heading and it is inappropriate. How do I identify this column and replace the heading when it is the subtotal column?

I hope I stated that clearly.

You can use the InScope() function to find out if you are in a subtotal and display different content. http://msdn2.microsoft.com/en-us/library/ms156490.aspx|||

I had two fields under the group/category. Each calculating an aggregate on different fields. I had placed the field/column headings at that level which included the category 'marker'. I found that if I put the category ('marker') at the group level on the heading and removed it at this point that it filled it appropriately. (beginner error... my apologies)

I tried using 'InScope' as you suggested and found that at that level it reported all heading at the same 'level'... I had to ask level as I couldn't manage to ask the appropriate InScope question.

I do appreciate your assitance. Thank you.

b

Manipulate Data

How can I manipulate the data in a column to get only the
numbers and leave the rest (Either on the select or remove
the text and leave the numbers in the column)?
Column with the data like:
346876 Error
432422 Warning
233556 Error
445332 Error
564445 Error
124345 Warning
995445 Info
Thanks for the help.
select * from bla where column1 like
'[0-9][0-9][0-9][0-9][0-9][0-9]%'
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.
|||If the data is always in this format, you can do:
SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM table
You might consider storing the two data elements separate since, apparently,
they are idependently relevant...
http://www.aspfaq.com/
(Reverse address to reply.)
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.
|||Thanks Chris.........but the number of digits can vary
(4,5,6,7,8,9,10)

>--Original Message--
>select * from bla where column1 like
>'[0-9][0-9][0-9][0-9][0-9][0-9]%'
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
the[vbcol=seagreen]
remove
>
>.
>
|||Thanks Aaron......That is what I wanted...

>--Original Message--
>If the data is always in this format, you can do:
>SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM
table
>You might consider storing the two data elements separate
since, apparently,
>they are idependently relevant...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
the[vbcol=seagreen]
remove
>
>.
>

Manipulate Data

How can I manipulate the data in a column to get only the
numbers and leave the rest (Either on the select or remove
the text and leave the numbers in the column)?
Column with the data like:
346876 Error
432422 Warning
233556 Error
445332 Error
564445 Error
124345 Warning
995445 Info
Thanks for the help.select * from bla where column1 like
'[0-9][0-9][0-9][0-9][0-9][0-9]%'
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.|||If the data is always in this format, you can do:
SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM table
You might consider storing the two data elements separate since, apparently,
they are idependently relevant...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.|||Thanks Chris.........but the number of digits can vary
(4,5,6,7,8,9,10)
>--Original Message--
>select * from bla where column1 like
>'[0-9][0-9][0-9][0-9][0-9][0-9]%'
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
>> How can I manipulate the data in a column to get only
the
>> numbers and leave the rest (Either on the select or
remove
>> the text and leave the numbers in the column)?
>> Column with the data like:
>> 346876 Error
>> 432422 Warning
>> 233556 Error
>> 445332 Error
>> 564445 Error
>> 124345 Warning
>> 995445 Info
>> Thanks for the help.
>
>.
>|||Thanks Aaron......That is what I wanted...
>--Original Message--
>If the data is always in this format, you can do:
>SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM
table
>You might consider storing the two data elements separate
since, apparently,
>they are idependently relevant...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
>> How can I manipulate the data in a column to get only
the
>> numbers and leave the rest (Either on the select or
remove
>> the text and leave the numbers in the column)?
>> Column with the data like:
>> 346876 Error
>> 432422 Warning
>> 233556 Error
>> 445332 Error
>> 564445 Error
>> 124345 Warning
>> 995445 Info
>> Thanks for the help.
>
>.
>

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!
Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
These are ways to add ntext, text, or image values to a row:
"Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
"Use the WRITETEXT statement. For more information, see WRITETEXT.
"ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
"OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
"ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
"DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
---
These are ways to add ntext, text, or image values to a row:
" Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
" Use the WRITETEXT statement. For more information, see WRITETEXT.
" ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
" OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
" ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
" DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
---
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format
.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
---
These are ways to add ntext, text, or image values to a row:
" Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
" Use the WRITETEXT statement. For more information, see WRITETEXT.
" ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
" OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
" ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
" DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
---
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara

Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara

Managing and Rotating keys for encryption for many SQL Servers

There is all kinds of great info out there about the mechanics behind column level encryption in SQL2005, but it all seems to assume I only have 1 or 2 database servers. If I am using an X509 certificate to encrypt my data, it looks as if I can script the administration of this fairly easily.

But what if I have 1000 SQL Servers?

Is there any guidance/best practices/tools out there that will help me manage the 1000 certificates that I would need to deploy in such a scenario. Also, what if I need to 'rotate' the certificates for some reason. Can a PKI for the domain help me to automate and manage this?

It seems as if the management of these certificates is purely 'manual' at this point.

Thanks for any help,

...Andrew

Currently there is no support for PKI, and the infrastructure for key recycling is limited to the SERVICE MASTER KEY and DB MASTER KEY. CERTIFICATE, ASYMMETRIC KEY and SYMMETRIC KEY objects need to be managed by on your application, but the available catalog views and builtins should help you in this task.

The SQL Server encryption infrastructure is better suited for encrypting data using SYMMETRIC KEYs, and protecting the SYMMETRIC KEYs with CERTIFICATEs or ASYMMETRIC KEYs.

BTW. Data encryption based on SYMMETRIC KEYs is recommended over CERTIFICATEs for performance and plaintext length limitation (only 1 block of data, typically ~117 bytes).

We really appreciate your feedback, and we would like to encourage you to share more details on how do you expect to use this feature and what type of improvements you consider are necessary.

-Raul Garcia

SDE/T

SQL Server Engine

|||

"The SQL Server encryption infrastructure is better suited for encrypting data using SYMMETRIC KEYs, and protecting the SYMMETRIC KEYs with CERTIFICATEs or ASYMMETRIC KEYs."

Right - this is what we are doing - but I didn't communicate that well in my previous post.

I have had two major clients who are implementing column level encryption due to PCI (Payment Card Industry) requirements in retail.

The PCI specification calls for a defined and documented mechanism for "rotating" keys on a regular basis if needed. These retailers are HUGE, thousands of stores - each running their own instance of SQL server holding financial transactions which inclue credit card info.

So this info qualifies as "data at rest" and must be encrypted.

I am recommending certficates to protect the symmetric keys - but my customers want to know how to manage the certificates that need to be deployed to each SQL machine. As the SQL box is just a W2K3 machine in a domain, I was thinking that could be managed via PKI or some kind of auto-enrolment?

From what you are saying, the actual "rotation" of the certificate could only be handled through some kind of script.

IMO - a great KB would cover the steps required to rotate certificates (including the subsequent decryption and re-encryption of symmetric keys) in a large, enterprise deployment.

|||

There are some useful ideas on the topic of rotating certificates in the comments for one of the posts on my blog - see the conversation with Boaz:

http://blogs.msdn.com/lcris/archive/2006/03/13/550904.aspx

Thanks

Laurentiu