Metadata: Columns in Indexes

Hi, Do you know if there are any way to know the name of wich columns integrate the indexes?
I have just found a column named "keys" in the table "sysindexes" but it is just a kind of code.

I have a problem with the primary keys that have created,
something similar to the refered: http://support.microsoft.com/kb/262541/en-us

So I am trying to identify which of my indexes would make conflict with the PKs, and
also need to standarize them. Could you help me please?

There are a few ways.

1. In 2000 or earlier, you can use: INDEX_COL, which is a bit annoying, but it works

2. In 2005, use the sys.index_columns view. It gives you a much easier view.

3. In either, for KEY constraints, use the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view.

|||

Thank Louis, Now I am using this query to get the column names of each index,
but just don't know how to compare the columns from one index with the ones that has
the other.

I need to be sure that the columns that I am using as a part of my PK's are not contained exactly as a
part of any Index because that will cause me problems with certain kind of querys used trough my
linked server (because of the bug that I have refered)

select so.name as tabla, si.name as indice, --, si.indid as tipoindice,
sc.name

from sysobjects so,
sysindexes si,
sysindexkeys sik,
syscolumns sc
where so.xtype = 'U'
and si.id = OBJECT_ID(so.name)
and so.id = sik.id
and si.indid = sik.indid
and so.id = sc.id
and sik.colid = sc.colid

and (indexproperty(si.id, si.name, 'IsAutoStatistics') = 0 /*Index was generated by the auto create statistics option of sp_dboption. 1 = True 0 = False NULL = Invalid input)*/
and indexproperty(si.id, si.name, 'IsHypothetical') = 0)
and indexproperty(si.id, si.name, 'IsStatistics') = 0
order by si.indid

Whit this query i get something like this:

Table Index Column
-
Author IX_Author_2 code
Author IX_Author_2 c_branch

Author IX_Author_1 code

Author PK_Author code
Author PK_Author c_branch

So, for example the case of IX_Author_2 and PK_Author will give me a problem and
I need to correct the Indexes but first i need a way to identify them.
I am trying with a cursor but just cannot get the right.
Could anybody give me an idea please.

Metadata/Report KB

I wanted to see if anyone has explored posting a Report KB based on metadata in the RS portal? If so, have you found a way to post the information on the RS portal?

I really am looking to add additional properties to a report. Other than author and description. I would like to add 2 to 3 more fields that would feed over to the catelog table on the report server. Then write a few reports that will allow for definitions, metadata, and links to the reports.

Does anyone have any ideas for something like this?

The code for the report server is compiled so I believe your options are limited.

If you would like to add additional properties, one way may be to add a foreign key reference to another table that contains this information, and maintain it from a separate application.

cheers,

Andrew

|||I was considering doing something just like that. I wanted to use the report catalog table and link off the report id or report name. My question is as I make future changes to the report does the report id overwrite it self or does it always stay the same?

metadata update

hi, I try to post a new question about metadata refresh...even if i see other thread that work on a similar problem.

I have a ssis package that import an xml huge file (500 mb); These are the main step:

1) generate a XSD file against xml using xsd.exe utility

2) using xml task, make a diff between the old xsd and the new

3) if there are no difference, I start the data flow task that import xml in sql server; otherwise I stop all the task, edit the data flow task, change the xsd reference in advanced editor and then make many "double clik / OK" on every single flow....

The underling idea is that xml file change because some columns are added but these columns are not interesting for my elaboration, so i can ignore this new column and work without mapping it.

What I'm looking for is a way for make, via SSIS, the "double clik / OK" steps....in other words, to update the metadata.

Could anybody suggest me a way? it's a sort of macro, or keyboard recorder...I'm trying to study xml package configuration; is this a good way ?

another way is to give to the end user the task to update metadata; for making this I need to open the package editor (visual studio..:!) in a more confortable environment....For example, is possible to edit the ssis package in ms access? probably i know the answer...

please, help me!

thank tou in advance

alessandro

Can I check my understanding?

You have an XML source file, correct? And its format (i.e. the xsd that describes it) sometimes changes, correct?

You want a way of changing the SSIS package at execution-time in order to handle this eventuality, correct?

If I've got all this correct then I'm afraid you cannot do this. The metadata of a data-flow cannot be changed after design-time.

-Jamie

|||

Thank you Jamie; you perfectly catch my question, and your answer is similat to other that I have seen on this forum. What I don't really understand is why there is this "limitation" (if i can say).

SSIS are many light year better of DTS (and not only...), but the thing I'm trying to do seem to be so easy....it's only a break in the execution and a sort of macro! At this point I don't exclude to trying a mouse recorder solution....

thank you very much for you answer

Alessandro

metadata services for analysis services

Hi,

I was wondering if anyone can tell me what options do I have to browse metadata for a cube built on Analysis services 2005. We would not like to buy a new thrid aprty tool for this. Does sql server 2005/ analysis services 2005 have a inbuilt option to view the metadata?

Thanks in advance.

Try running an AMOBrowser sample applicaiton. You should be able to see all Analysis Services metadata.

Another point here is: In version 2005 Analysis Services metadata is in XML format and it is easily obtainable. Go to the SQL Management studio, right click on any object and you can create a script containing metadata definition of the object. You can parce the XML yourself and manipulate it the way you want.

Hope that helps.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

thank you so much for the information. But i dont see any place at microsoft where I download the amoBrowser. Can you point me where I can find the amoBrowser and its installation files?

thanks again.

|||

AMOBrowser is part of the Samples you install with the product. You dont need to download it separately.

After installing the Samples you should be able to find it on your machine in C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO\AmoBrowser.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||i just saw that in one of the postings. Unfortulately i dont have those files. I installed the samples but I have the developer edition, maybe I shold have the enterprise edition for this? Do you think its right?|||

nevermind, i found it here,

http://msdn2.microsoft.com/en-us/library/ms160930.aspx

thanks again.

MetaData Repository Standards (crossposted from comp.databases.ms-access)

I put this up on the other NG, then thought better of it...
---
I'm tasked with developing a enterprise-level metadata tool - starting yesterday
and TB delivered by Jan 30th.
There actually is some small chance of my delivering same - since I've already
done something similar in the context of recording report requirements (field
sources, calculations, and so-forth).
But this time, I'd like to do the architecture the "right" way - i.e. with the
same table structure that some commercially-avaiable tools use.
Anybody know of a reference book that details this sort of architecture? It's
not quite as obvious as it may seem at first because of various recursive
relationships - like fields/calculation components and allowed values/sub
values.
--
PeteCresswellHave you had a look at SQL Server Meta Data Services' Here is what it does:
Meta Data Services is intended to store meta data, and it is designed to be
integrated with other tools and applications. It provides a solution for
storing and managing data warehousing definitions, OLAP definitions, design
data used in development tools, and any other type of meta data used in a
programming environment.
I've not personally worked with this, but maybe it can meet some of your
requirements.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"(Pete Cresswell)" <x@.y.z> wrote in message
news:aeevtvkbg1aae0enkk28pfi5jbchsnl6j2@.4ax.com...
> I put this up on the other NG, then thought better of it...
> ---
> I'm tasked with developing a enterprise-level metadata tool - starting
yesterday
> and TB delivered by Jan 30th.
> There actually is some small chance of my delivering same - since I've
already
> done something similar in the context of recording report requirements
(field
> sources, calculations, and so-forth).
> But this time, I'd like to do the architecture the "right" way - i.e. with
the
> same table structure that some commercially-avaiable tools use.
> Anybody know of a reference book that details this sort of architecture?
It's
> not quite as obvious as it may seem at first because of various recursive
> relationships - like fields/calculation components and allowed values/sub
> values.
> --
> PeteCresswell|||> Meta Data Services is intended to store meta data, and it is designed to be
We're using a product called PACE - implmented here with an Oracle
back end.
It's primary uses are
- As a staging area for data flowing into local systems from
outside vendors
- As a staging area for the reverse: data from local systems
to the outside
- As a historical archive: some systems want to know the state
of, say, a muncipal bond as of a certain day in the past.
Over the years it's grown and grown - becoming slower and more
complex.
The tactical problems at hand are three:
1) That people have used it's "UserField" definition capability
extensively and created many duplicate calulation fields which need to
be identified and coalesced.
2) The owners suspect there are a number of loops: i.e. a field comes
from and outside vendor, goes into local system XYZ, and then gets
harvested back into the staging area unchanged.
3) By now it must contain a number of "orphan" fields: fields that
are not used by any local system.
The tool I'm writing will enable various people to document each field
in PACE: all the places it comes from, all the places it goes, how
often it's refreshed, the properties of each of it's incarnations,
rollup calculations, user calculations, upload
calulations/transformations, and so-forth.
I can think of several data architectures/table designs/whatever to
accomplish this, but I'd *really* like to copy something written for
the same niche that has a proven track record - both for
bulletproofness, and because eventually they'll purchase a "real" tool
and there will be a migration...
Of course, nobody's saying what the "real" tool will be - but I'm
hoping that all the major contenders' developers will have arrived at
similar designs looking at the same requirements...

Metadata Repository Engine

How do I verify that the Metadata Repository Engine was updated to version
3.0 when I installed SQL 2000. The Help file says that if the engine is at
3.0 that I should be able to upgrade the database to 3.0. The upgrade
button is greyed out and the version is 2.0. I have another instance on the
same physical server (different virtual server) that is running 3.0.
Thanks,
JoelCheck the version for repodbc.dll. It should be version
2000.0.x.x
which corresponds to SQL 2000, repository v. 3.
I thing the v2 engine had the version of the file at
6.10.x.x or 6.0.x.x
You can find more specific info on your version by entering
the file name at the following site:
http://support.microsoft.com/default.aspx?scid=/servicedesks/fileversion/dllinfo.asp
-Sue
On Thu, 2 Oct 2003 10:36:05 -0500, "Joel Donaldson"
<jdonaldson@.centiv.com> wrote:
>How do I verify that the Metadata Repository Engine was updated to version
>3.0 when I installed SQL 2000. The Help file says that if the engine is at
>3.0 that I should be able to upgrade the database to 3.0. The upgrade
>button is greyed out and the version is 2.0. I have another instance on the
>same physical server (different virtual server) that is running 3.0.
>Thanks,
>Joel
>

metadata refresh?

What do you do to address this:

[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.

A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?

mruniqueid wrote:

A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?

I can answer this one - the versioning story for tasks allows ISVs (or Microsoft) to release completely new version of the task (new assembly version for managed, or new CLSID for native tasks). Designer doesn't automatically upgrade package to use new task version, as it will probably break package compatibility with systems that have old task version installed. Reload with Upgrade command tells the designer to try to upgrade tasks to new version.

|||

mruniqueid wrote:

What do you do to address this:

[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.

This means the metadata of your source column ("objectName1") had changed in the source table or maybe you added this column after configuring your OLE DB source component.

You should try to double-click on your OLE DB Source and the pop-up window, with a question if you want to automatically synchronize the metadata, will show up.

HTH.

|||

Thanks - works as advertised.

In the meantime I found something interesting that wasn't immediately obvious. I have a foreach container with an item enumeration. The enumeration collection is a list of views, all with the same "basic" definition (I'll explain "basic" in a minute). The container has a data flow task that opens a view (with the view name defined by a variable from the foreach container) as an OLE DB source and copies the view contents to one OLE DB destination.

I kept getting errors about metadata differences and tracked the error to the view definitions. All views have the same column data type definijtions but some of the view columns are literals with differing text lengths, such as 'person' (6 chars), 'location' (8 chars), and so on. To solve the problem the view definition had to be modified to cast these literals to the destination column length, such as

CAST('person' AS varchar(50)) AS objectName

All of the views have to have exactly the same data types and lengths in order for the package to work.

Just wanted to pass this along in case anybody else runs into the problem

|||

Correct. A data-flow is bound to the metadata at design-time. The expected metadata cannot change at runtime.

-Jamie

|||My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.

There needs to be a comprehesive, contains all clicks menu for this tool.

There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

Is there no other way to clear the external metadata error?|||

IanO wrote:

My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.

There needs to be a comprehesive, contains all clicks menu for this tool.

There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

Is there no other way to clear the external metadata error?

I understand your pain and I have similar complains....

The Data conversion transformation adds a 'Copy of..' to each column because it preserves the original column; if you decided to remove the Copy of part of the name; then SSIS is forced to use a multipart name; as the name of the column is duplicate within the dataflow; so you can see things like (OLE DB Source.MyColumn and Data Convertion.MyColumn). So, I don’t recommend you to edit the names as it gets harder when you are mapping them in the destination components.

Please, feel free to post a suggestion in Microsoft Connect site:

http://connect.microsoft.com/SQLServer/Feedback

|||Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,

I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."

If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.

Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?

IanO|||

IanO wrote:

My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

You can't. Its a new column so it has to call it something. "Copy of XXX" is as arbitrary as anything else.

IanO wrote:

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Naturally it does. So your new column will have to be changed to the data type that you require. Should the development tools simply guess what length to put on there? Of course not, so it puts an arbitrary value of 50 in there. SSIS will never make changes for you - you are always responsible for making changes yourself (this is a good thing by the way)

IanO wrote:

Editing every line is tedious. I have to remove "Copy of" and change the size.

Hopefully you understand why this is unavoidable. YOU have to tell SSIS what the data type of each column should be - it will not try and guess.

IanO wrote:

So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

Sorry, I'm a bit confused on this one. What button are you referring to on the OLE DB Connection Manager?

IanO wrote:

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

No, there is no place. Putting an average in like you suggest is a no-no because the would constitute SSIS guessing and as I explained that is something it won't do hence an arbitrary value is better. Besides, if it COULD do the average of the first 100 rows (which it cant because at design-time no actual rows exist in the pipeline - so where are these 100 rows that you are referring to?) that would create an error for any of the rows that exceeded that average - so that isn't a good idea.

What *might* be a good idea would be to have the option of selecting what the arbitrary value should be. That could be an option you set within BIDS. I do however see problems with this if different developers have this option set differently. For safety's sake I think the behaviour is just right as it is currently.

IanO wrote:

Is there no other way to clear the external metadata error?

I don't think so. The developer is responsible for setting what the external metadata should be.

Hope that helps.

-Jamie

|||

IanO wrote:

Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,

I think what Rafael means by this is that its not a good idea to change the name of the new column to be the same as the one that it is derived from as this causes confusion later in the pipeline.

IanO wrote:

I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."

I agree with the assertion that you should change at source if necassary although I'm not sure that avoidance is a good strategy. This component has its uses in certain circumstances. I don't have the book to hand so can't read the context in which Kirk stated this.

IanO wrote:

If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.
Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?
IanO

The reason changes don't get propogated will be because you have an asynchronous component such as a SORT or an AGGREGATE. I won't go into the details of asynchronous components (there's plenty of info out there though: http://search.live.com/results.aspx?q=ssis+asynchronous+synchronous&mkt=en-GB&form=QBNO) but you'll have to take my word for it that this is unavoidable because of the vary nature of the components that you are using. Any column that is input into an asynchronous component is NOT the same as any column that is output from it - even if they have the same name.

Its annoying, yes, but that's the way it is I'm afraid. If you want a fuller explanantion then let me know and I'll do my best to provide one.

-Jamie

metadata refresh?

What do you do to address this:

[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.

A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?

mruniqueid wrote:

A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?

I can answer this one - the versioning story for tasks allows ISVs (or Microsoft) to release completely new version of the task (new assembly version for managed, or new CLSID for native tasks). Designer doesn't automatically upgrade package to use new task version, as it will probably break package compatibility with systems that have old task version installed. Reload with Upgrade command tells the designer to try to upgrade tasks to new version.

|||

mruniqueid wrote:

What do you do to address this:

[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.

This means the metadata of your source column ("objectName1") had changed in the source table or maybe you added this column after configuring your OLE DB source component.

You should try to double-click on your OLE DB Source and the pop-up window, with a question if you want to automatically synchronize the metadata, will show up.

HTH.

|||

Thanks - works as advertised.

In the meantime I found something interesting that wasn't immediately obvious. I have a foreach container with an item enumeration. The enumeration collection is a list of views, all with the same "basic" definition (I'll explain "basic" in a minute). The container has a data flow task that opens a view (with the view name defined by a variable from the foreach container) as an OLE DB source and copies the view contents to one OLE DB destination.

I kept getting errors about metadata differences and tracked the error to the view definitions. All views have the same column data type definijtions but some of the view columns are literals with differing text lengths, such as 'person' (6 chars), 'location' (8 chars), and so on. To solve the problem the view definition had to be modified to cast these literals to the destination column length, such as

CAST('person' AS varchar(50)) AS objectName

All of the views have to have exactly the same data types and lengths in order for the package to work.

Just wanted to pass this along in case anybody else runs into the problem

|||

Correct. A data-flow is bound to the metadata at design-time. The expected metadata cannot change at runtime.

-Jamie

|||My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.

There needs to be a comprehesive, contains all clicks menu for this tool.

There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

Is there no other way to clear the external metadata error?
|||

IanO wrote:

My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.

There needs to be a comprehesive, contains all clicks menu for this tool.

There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

Is there no other way to clear the external metadata error?

I understand your pain and I have similar complains....

The Data conversion transformation adds a 'Copy of..' to each column because it preserves the original column; if you decided to remove the Copy of part of the name; then SSIS is forced to use a multipart name; as the name of the column is duplicate within the dataflow; so you can see things like (OLE DB Source.MyColumn and Data Convertion.MyColumn). So, I don’t recommend you to edit the names as it gets harder when you are mapping them in the destination components.

Please, feel free to post a suggestion in Microsoft Connect site:

http://connect.microsoft.com/SQLServer/Feedback

|||Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,

I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."

If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.

Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?

IanO
|||

IanO wrote:

My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

You can't. Its a new column so it has to call it something. "Copy of XXX" is as arbitrary as anything else.

IanO wrote:

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Naturally it does. So your new column will have to be changed to the data type that you require. Should the development tools simply guess what length to put on there? Of course not, so it puts an arbitrary value of 50 in there. SSIS will never make changes for you - you are always responsible for making changes yourself (this is a good thing by the way)

IanO wrote:

Editing every line is tedious. I have to remove "Copy of" and change the size.

Hopefully you understand why this is unavoidable. YOU have to tell SSIS what the data type of each column should be - it will not try and guess.

IanO wrote:

So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

Sorry, I'm a bit confused on this one. What button are you referring to on the OLE DB Connection Manager?

IanO wrote:

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

No, there is no place. Putting an average in like you suggest is a no-no because the would constitute SSIS guessing and as I explained that is something it won't do hence an arbitrary value is better. Besides, if it COULD do the average of the first 100 rows (which it cant because at design-time no actual rows exist in the pipeline - so where are these 100 rows that you are referring to?) that would create an error for any of the rows that exceeded that average - so that isn't a good idea.

What *might* be a good idea would be to have the option of selecting what the arbitrary value should be. That could be an option you set within BIDS. I do however see problems with this if different developers have this option set differently. For safety's sake I think the behaviour is just right as it is currently.

IanO wrote:

Is there no other way to clear the external metadata error?

I don't think so. The developer is responsible for setting what the external metadata should be.

Hope that helps.

-Jamie

|||

IanO wrote:

Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,

I think what Rafael means by this is that its not a good idea to change the name of the new column to be the same as the one that it is derived from as this causes confusion later in the pipeline.

IanO wrote:

I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."

I agree with the assertion that you should change at source if necassary although I'm not sure that avoidance is a good strategy. This component has its uses in certain circumstances. I don't have the book to hand so can't read the context in which Kirk stated this.

IanO wrote:

If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.
Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?
IanO

The reason changes don't get propogated will be because you have an asynchronous component such as a SORT or an AGGREGATE. I won't go into the details of asynchronous components (there's plenty of info out there though: http://search.live.com/results.aspx?q=ssis+asynchronous+synchronous&mkt=en-GB&form=QBNO) but you'll have to take my word for it that this is unavoidable because of the vary nature of the components that you are using. Any column that is input into an asynchronous component is NOT the same as any column that is output from it - even if they have the same name.

Its annoying, yes, but that's the way it is I'm afraid. If you want a fuller explanantion then let me know and I'll do my best to provide one.

-Jamie

Metadata Refresh

-We are using SSIS packages for various kind of data load from excel source.

-If there are any change in the data type or format of excel, the package cries for the Metadata mismatch.

-During design time if you accept the metadata changes, all things work fine.

But in our case we have deployed the packages on Production Server, now the excel file format/data has changed. The packages are expecting a different metadata so they are not working at all.

Do you have any suggestions for the above problem?

Thanks, Vijay.

You'll have to edit the package. Metadata cannot change once it has been designed into the package.

Metadata on data flow path not updating

I have a data flow task with a single source and destination task. I'm having the source task creating a table from a variable expression and the destination table also created from a variable expression. I'm running this under 3 scenarios in which each scenario has a different source and destination table. They are different in name but close in table structure with the exception of one column being different. The Metadata for the source flow path seems to be "sticky" in that it is not modifying the source table structure in the flow to account for this different column. I'm not sure how to adjust this. Any ideas? I've modified several properties in the task and data flow but nothing seems to make this adjustment in run-time.Metadata cannot and does not change dynamically nor at runtime.

This is by design and there's very little you can do to get around that, short of coding your own application using the SSIS API.

Metadata Model in SSIS 2005

Hi *,

Can anyone tell me what Metadata Model Microsoft supports with SSIS 2005? DTS in 2000 was supporting OIM but I was not able to get some information about this topic on SSIS 2005.

The reason I need to know is that I wanna import some metadata from Business Objects Data Integrator into SSIS 2005 and Business Objects is supporting CWM....

I would appreciate your help!

Thank you,
Steve

Can you explain what OIM & CWM stand for and what they mean?

I wasn't aware of DTS supporting any metadata models.

-Jamie

|||CWM stands for Common Warehouse Metadatamodel; OIM for Open Information Model

OIM was supported by SQL Server 2000, but this standard doesn't exist anymore. CWM is supported by a wide range of ETL-Tools (including BusinessObjects Data Integrator).

For example, it's possible to export Data Integrators Datastores and insert them into another system like ERWin. Because both support a common metadata model. I just want to know if I can share metadata with SQL Server Integration Services?

At least SSIS creates a file named CWM_1.0.dtd in my project folders which indicates some support...|||

Interesting stuff!

SSIS doesn't have a great metadata story to be honest, certainly not anything that adheres to industry standards (I hereby stand to be corrected). So you probably can't do the things that you are talking about. As far as I am aware the products you talk about there make heavy use of metadata (I'm assuming through some sort of metadata repository?) but SSIS's package architecture is different - it doesn't lend itself to metadata in quite the same way (in my opinion).

I can't find a CWM* file in any of my folders. Where exactly is yours?

How did SQL Server 2000 support OIM?

Sorry, I haven't been much help there have I?

-Jamie

|||Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...
SSIS doesn't support metadata exchange, has poor data profiling, and no possibilities to do perform impact/lineage analysis (w/out metadata samples toolkit)... does anyone have some success stories where Microsoft's Integration Services handled a really big data integration project (except for Project REAL)?|||

ssauerw wrote:

Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

No. Not for DTS.

ssauerw wrote:

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...

I'd be interested in reading that when its complete!!!

-Jamie

|||Well, you can read it but it's in German ;)

Why do you use SSIS instead of a "professional" Data Integration Tool like PowerCenter or BODI? Just the price?

Steve|||

Why don't you qualify SSIS as a professional data integration tool?

Functionally SSIS has all of the features that tools such as those have - the main difference is, I suppose,,the architecture. I would qualify tools like Informatica Powercentre (which I have used extensively) as a pureplay ETL tool - I believe SSIS is much more than that - it is a platform for data integration. The range of features that you get out-of-the-box dwarfs Informatica - it really does.

You are correct though - the main differentiator is the price. And don't forget that SSIS comes with a free relational database server in the box.

-Jamie

P.S. You may wish to read this comparison of SSIS and Informatica: http://www.microsoft.com/sql/technologies/integration/conchango.mspx

|||Hi Jamie,

interesting paper... I'll give it a closer look this afternoon.

Thanks!|||

OK great. Again though, why don't you qualify SSIS as a professional ETL tool? Is it simply because it has a Microsoft badge on it?

-Jamie

|||Hi Jamie,

interesting paper. Thanks again.

Well, I think SSIS can perform ETL Jobs pretty well. But it's not a professional data integration platform, since there is no impact analysis, data lineage, metadata exchange, no out-of-the-box support for enterprise systems, etc. I would prefer Data Integrator even though its inital costs are much higher...

Don't get me wrong, I like Microsoft software (not every piece though ;) but for an enterprise scale data integration project I would use Data Integrator...

Steve|||

Steve,

Some thoughts here for you: http://blogs.conchango.com/jamiethomson/archive/2006/08/15/4353.aspx

I'd be interested in reading your paper when it is complete. Do you think that would be possible?

-Jamie

|||Hi Jamie,

thanks for putting this page in your blog online! I'm very anxious about the comments ppl are hopefully making.

Well, as I mentioned earlier, you can read my thesis but it's written in German not English. So if that's not a problem I'll send it to you!

Steve|||

It's an interesting topic.

Strictly speaking mature metadata reporting makes enterprise data integration easier to maintain and manage over time but it is not a pre-requisite of building data warehouses and BI databases. There are a lot of DataStage sites out there that are not using MetaStage and are not aware of the numerous alternative metadata methods just like there are a lot using PowerCenter but not using SuperGlue. Several years ago we had a lot of competition between a bunch of ETL tools none of which had profiling or metadata addons.

Informatica only bought a profiling and data quality tool at the start of this year, BO only bought FirstLogic in Feb, IBM-Ascential meanwhile have spent several years trying to merge the suite onto one metadata platform, so really is Microsoft that far behind the competition? There is a risk that metadata and SOA governance becomes a big business trend in the next couple years but I don't see it captivating the market right now.

You've got to consider up front costs against ongoing costs and in some evaluations SSIS will come out ahead and in others it will be a full suite data integration vendor. A large number of sources and complex range of data problems certainly favours the full suite with profiling and metadata, but projects with a narrower scope may make SSIS more attractive.

|||

I have used Data Integrator and I prefer SSIS. My biggest complaint with DI was you cannot use a SQL Statements as a data source; you must use tables/views only (as far as I could tell). When I needed to filter a 10 million row table to only a few million records, it allowed me to set a filter on the source, but it had to read every record in first to apply the filter anyway.

Microsoft has always created a consistent look and feel to their products, making the transition from one development tool to another seamless. Within the past year, I've learned SSIS, SSAS, and SSRS just by trial-and-error, and am able to create end-to-end reporting and anlaysis solutions for our customers within a few weeks.

And I believe it will just be a matter of time before new "Data Profile" SSIS packages or custom tasks are created by the community, as was the Metadata Toolkit, and custom source adapters for enterprise systems will surface for SSIS, all probably downloadable for free.

-Kory

Metadata Model in SSIS 2005

Hi *,

Can anyone tell me what Metadata Model Microsoft supports with SSIS 2005? DTS in 2000 was supporting OIM but I was not able to get some information about this topic on SSIS 2005.

The reason I need to know is that I wanna import some metadata from Business Objects Data Integrator into SSIS 2005 and Business Objects is supporting CWM....

I would appreciate your help!

Thank you,
Steve

Can you explain what OIM & CWM stand for and what they mean?

I wasn't aware of DTS supporting any metadata models.

-Jamie

|||CWM stands for Common Warehouse Metadatamodel; OIM for Open Information Model

OIM was supported by SQL Server 2000, but this standard doesn't exist anymore. CWM is supported by a wide range of ETL-Tools (including BusinessObjects Data Integrator).

For example, it's possible to export Data Integrators Datastores and insert them into another system like ERWin. Because both support a common metadata model. I just want to know if I can share metadata with SQL Server Integration Services?

At least SSIS creates a file named CWM_1.0.dtd in my project folders which indicates some support...|||

Interesting stuff!

SSIS doesn't have a great metadata story to be honest, certainly not anything that adheres to industry standards (I hereby stand to be corrected). So you probably can't do the things that you are talking about. As far as I am aware the products you talk about there make heavy use of metadata (I'm assuming through some sort of metadata repository?) but SSIS's package architecture is different - it doesn't lend itself to metadata in quite the same way (in my opinion).

I can't find a CWM* file in any of my folders. Where exactly is yours?

How did SQL Server 2000 support OIM?

Sorry, I haven't been much help there have I?

-Jamie

|||Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...
SSIS doesn't support metadata exchange, has poor data profiling, and no possibilities to do perform impact/lineage analysis (w/out metadata samples toolkit)... does anyone have some success stories where Microsoft's Integration Services handled a really big data integration project (except for Project REAL)?|||

ssauerw wrote:

Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

No. Not for DTS.

ssauerw wrote:

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...

I'd be interested in reading that when its complete!!!

-Jamie

|||Well, you can read it but it's in German ;)

Why do you use SSIS instead of a "professional" Data Integration Tool like PowerCenter or BODI? Just the price?

Steve|||

Why don't you qualify SSIS as a professional data integration tool?

Functionally SSIS has all of the features that tools such as those have - the main difference is, I suppose,,the architecture. I would qualify tools like Informatica Powercentre (which I have used extensively) as a pureplay ETL tool - I believe SSIS is much more than that - it is a platform for data integration. The range of features that you get out-of-the-box dwarfs Informatica - it really does.

You are correct though - the main differentiator is the price. And don't forget that SSIS comes with a free relational database server in the box.

-Jamie

P.S. You may wish to read this comparison of SSIS and Informatica: http://www.microsoft.com/sql/technologies/integration/conchango.mspx

|||Hi Jamie,

interesting paper... I'll give it a closer look this afternoon.

Thanks!|||

OK great. Again though, why don't you qualify SSIS as a professional ETL tool? Is it simply because it has a Microsoft badge on it?

-Jamie

|||Hi Jamie,

interesting paper. Thanks again.

Well, I think SSIS can perform ETL Jobs pretty well. But it's not a professional data integration platform, since there is no impact analysis, data lineage, metadata exchange, no out-of-the-box support for enterprise systems, etc. I would prefer Data Integrator even though its inital costs are much higher...

Don't get me wrong, I like Microsoft software (not every piece though ;) but for an enterprise scale data integration project I would use Data Integrator...

Steve|||

Steve,

Some thoughts here for you: http://blogs.conchango.com/jamiethomson/archive/2006/08/15/4353.aspx

I'd be interested in reading your paper when it is complete. Do you think that would be possible?

-Jamie

|||Hi Jamie,

thanks for putting this page in your blog online! I'm very anxious about the comments ppl are hopefully making.

Well, as I mentioned earlier, you can read my thesis but it's written in German not English. So if that's not a problem I'll send it to you!

Steve|||

It's an interesting topic.

Strictly speaking mature metadata reporting makes enterprise data integration easier to maintain and manage over time but it is not a pre-requisite of building data warehouses and BI databases. There are a lot of DataStage sites out there that are not using MetaStage and are not aware of the numerous alternative metadata methods just like there are a lot using PowerCenter but not using SuperGlue. Several years ago we had a lot of competition between a bunch of ETL tools none of which had profiling or metadata addons.

Informatica only bought a profiling and data quality tool at the start of this year, BO only bought FirstLogic in Feb, IBM-Ascential meanwhile have spent several years trying to merge the suite onto one metadata platform, so really is Microsoft that far behind the competition? There is a risk that metadata and SOA governance becomes a big business trend in the next couple years but I don't see it captivating the market right now.

You've got to consider up front costs against ongoing costs and in some evaluations SSIS will come out ahead and in others it will be a full suite data integration vendor. A large number of sources and complex range of data problems certainly favours the full suite with profiling and metadata, but projects with a narrower scope may make SSIS more attractive.

|||

I have used Data Integrator and I prefer SSIS. My biggest complaint with DI was you cannot use a SQL Statements as a data source; you must use tables/views only (as far as I could tell). When I needed to filter a 10 million row table to only a few million records, it allowed me to set a filter on the source, but it had to read every record in first to apply the filter anyway.

Microsoft has always created a consistent look and feel to their products, making the transition from one development tool to another seamless. Within the past year, I've learned SSIS, SSAS, and SSRS just by trial-and-error, and am able to create end-to-end reporting and anlaysis solutions for our customers within a few weeks.

And I believe it will just be a matter of time before new "Data Profile" SSIS packages or custom tasks are created by the community, as was the Metadata Toolkit, and custom source adapters for enterprise systems will surface for SSIS, all probably downloadable for free.

-Kory

Metadata Model in SSIS 2005

Hi *,

Can anyone tell me what Metadata Model Microsoft supports with SSIS 2005? DTS in 2000 was supporting OIM but I was not able to get some information about this topic on SSIS 2005.

The reason I need to know is that I wanna import some metadata from Business Objects Data Integrator into SSIS 2005 and Business Objects is supporting CWM....

I would appreciate your help!

Thank you,
Steve

Can you explain what OIM & CWM stand for and what they mean?

I wasn't aware of DTS supporting any metadata models.

-Jamie

|||CWM stands for Common Warehouse Metadatamodel; OIM for Open Information Model

OIM was supported by SQL Server 2000, but this standard doesn't exist anymore. CWM is supported by a wide range of ETL-Tools (including BusinessObjects Data Integrator).

For example, it's possible to export Data Integrators Datastores and insert them into another system like ERWin. Because both support a common metadata model. I just want to know if I can share metadata with SQL Server Integration Services?

At least SSIS creates a file named CWM_1.0.dtd in my project folders which indicates some support...|||

Interesting stuff!

SSIS doesn't have a great metadata story to be honest, certainly not anything that adheres to industry standards (I hereby stand to be corrected). So you probably can't do the things that you are talking about. As far as I am aware the products you talk about there make heavy use of metadata (I'm assuming through some sort of metadata repository?) but SSIS's package architecture is different - it doesn't lend itself to metadata in quite the same way (in my opinion).

I can't find a CWM* file in any of my folders. Where exactly is yours?

How did SQL Server 2000 support OIM?

Sorry, I haven't been much help there have I?

-Jamie

|||Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...
SSIS doesn't support metadata exchange, has poor data profiling, and no possibilities to do perform impact/lineage analysis (w/out metadata samples toolkit)... does anyone have some success stories where Microsoft's Integration Services handled a really big data integration project (except for Project REAL)?|||

ssauerw wrote:

Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

No. Not for DTS.

ssauerw wrote:

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...

I'd be interested in reading that when its complete!!!

-Jamie

|||Well, you can read it but it's in German ;)

Why do you use SSIS instead of a "professional" Data Integration Tool like PowerCenter or BODI? Just the price?

Steve|||

Why don't you qualify SSIS as a professional data integration tool?

Functionally SSIS has all of the features that tools such as those have - the main difference is, I suppose,,the architecture. I would qualify tools like Informatica Powercentre (which I have used extensively) as a pureplay ETL tool - I believe SSIS is much more than that - it is a platform for data integration. The range of features that you get out-of-the-box dwarfs Informatica - it really does.

You are correct though - the main differentiator is the price. And don't forget that SSIS comes with a free relational database server in the box.

-Jamie

P.S. You may wish to read this comparison of SSIS and Informatica: http://www.microsoft.com/sql/technologies/integration/conchango.mspx

|||Hi Jamie,

interesting paper... I'll give it a closer look this afternoon.

Thanks!|||

OK great. Again though, why don't you qualify SSIS as a professional ETL tool? Is it simply because it has a Microsoft badge on it?

-Jamie

|||Hi Jamie,

interesting paper. Thanks again.

Well, I think SSIS can perform ETL Jobs pretty well. But it's not a professional data integration platform, since there is no impact analysis, data lineage, metadata exchange, no out-of-the-box support for enterprise systems, etc. I would prefer Data Integrator even though its inital costs are much higher...

Don't get me wrong, I like Microsoft software (not every piece though ;) but for an enterprise scale data integration project I would use Data Integrator...

Steve|||

Steve,

Some thoughts here for you: http://blogs.conchango.com/jamiethomson/archive/2006/08/15/4353.aspx

I'd be interested in reading your paper when it is complete. Do you think that would be possible?

-Jamie

|||Hi Jamie,

thanks for putting this page in your blog online! I'm very anxious about the comments ppl are hopefully making.

Well, as I mentioned earlier, you can read my thesis but it's written in German not English. So if that's not a problem I'll send it to you!

Steve|||

It's an interesting topic.

Strictly speaking mature metadata reporting makes enterprise data integration easier to maintain and manage over time but it is not a pre-requisite of building data warehouses and BI databases. There are a lot of DataStage sites out there that are not using MetaStage and are not aware of the numerous alternative metadata methods just like there are a lot using PowerCenter but not using SuperGlue. Several years ago we had a lot of competition between a bunch of ETL tools none of which had profiling or metadata addons.

Informatica only bought a profiling and data quality tool at the start of this year, BO only bought FirstLogic in Feb, IBM-Ascential meanwhile have spent several years trying to merge the suite onto one metadata platform, so really is Microsoft that far behind the competition? There is a risk that metadata and SOA governance becomes a big business trend in the next couple years but I don't see it captivating the market right now.

You've got to consider up front costs against ongoing costs and in some evaluations SSIS will come out ahead and in others it will be a full suite data integration vendor. A large number of sources and complex range of data problems certainly favours the full suite with profiling and metadata, but projects with a narrower scope may make SSIS more attractive.

|||

I have used Data Integrator and I prefer SSIS. My biggest complaint with DI was you cannot use a SQL Statements as a data source; you must use tables/views only (as far as I could tell). When I needed to filter a 10 million row table to only a few million records, it allowed me to set a filter on the source, but it had to read every record in first to apply the filter anyway.

Microsoft has always created a consistent look and feel to their products, making the transition from one development tool to another seamless. Within the past year, I've learned SSIS, SSAS, and SSRS just by trial-and-error, and am able to create end-to-end reporting and anlaysis solutions for our customers within a few weeks.

And I believe it will just be a matter of time before new "Data Profile" SSIS packages or custom tasks are created by the community, as was the Metadata Toolkit, and custom source adapters for enterprise systems will surface for SSIS, all probably downloadable for free.

-Kory

Metadata Issues

I am experiencing something really bazaar. The package I created usign SQL2K5 Sept. CTP Builds and functions just fine in one domain. When I deployed to another machine & new domain, we get Metadata errors within dataflow that was modified. We had encountered this earlier but by rebuilding the package it has solved the problem. This time, rebuild is not helping. How can metadata for data flow of a package get corrupted from one environment to another. Also, what's the purpose of building a package when the package is in XML (Text). I don't see any binary code for script tasks.

What exactly is the message? My favourite at present when moving between environments is the encyrption error for passwords (or any sensitive propertry), since the default protection uses the user key, so obviously changing domains changes accounts and therefore the decyption fails. Setting don't save sensitive information and use configurations to apply passwords again helps somewhat.|||Thanks for your reply Darren. I have already cross the hurdle of Package Protection level issue. Current settings are "Don't save sensitive" info. I am curious to see if they modify this default setting in RTM rel.
|||Using the RTM release now available to MSDN subscribers, the default is User Key still.|||Great!!. By the way, what does a Build functionality do for a package when script tasks within either control flow or data flow are set not to compile on save. If I don't build the package, it still works so why build it?
|||

Scripts can be compiled or not. If compiled then they run faster since there is no compilation overhead each time the package is run. I wasn't aware that this was linked to the package build, I assumed it happened within the script editor itself and VSA. Only downside of pre-compiled scripts is they are processor specific, so x86 to x64 deployments for example will fail.

|||You'd be better of ignoring me and just reading Books Online. The package Save induces the compilation.

From ScriptTask.PreCompile

Property Value

true if the VSA script is compiled when the package is saved; otherwise, false. The default value is true.

Remarks

VSA scripts must be compiled before the package runs. The Script task includes the option to pre-compile script into binary code when the package is saved. When script is precompiled, the language engine is not loaded at run time and the package runs more quickly; however, compiled binary files consume significant disk space. Script that is not precompiled is compiled at run time, which slows package execution but consumes less disk space. When storing large packages is not a problem, you should pre-compile your VSA scripts.

|||

DarrenSQLIS wrote:

Only downside of pre-compiled scripts is they are processor specific, so x86 to x64 deployments for example will fail.

Not really. The script is precompiled to MSIL (.NET intemediary language) which is platform-independent. It will be JIT-ed to process-specific code at runtime.

Metadata Inheritance

Hello,
I have written a mechanism which returns meta data back to a client for
different views. For example, one meta data item on a column is the Display
Name.
Now, so I don't have to keep attaching meta data to a particular column
every time I create a new view, I "inherit" the meta data from a parent view
or table by looking at all the same named columns in any views or tables
that the table depends (sysdepends) on.
The problem is, sometimes it make sense to change the name of a column in a
view from the underlying table (e.g. company table has a "name" column, but
in the view, you want to call it "company_name").
My question is, is there an easy way to determine where a view's column
really came from?
Thanks,
-PaulNever mind, I forgot to look at the depnumber column in sysdepends!
"Paul" <a@.b.com> wrote in message
news:eWN9knVCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have written a mechanism which returns meta data back to a client for
> different views. For example, one meta data item on a column is the
> Display Name.
> Now, so I don't have to keep attaching meta data to a particular column
> every time I create a new view, I "inherit" the meta data from a parent
> view or table by looking at all the same named columns in any views or
> tables that the table depends (sysdepends) on.
> The problem is, sometimes it make sense to change the name of a column in
> a view from the underlying table (e.g. company table has a "name" column,
> but in the view, you want to call it "company_name").
> My question is, is there an easy way to determine where a view's column
> really came from?
> Thanks,
> -Paul
>|||Okay, now the hard question.
How do I correlate a column in the sysdepends table back to a column in the
resulting view's columns?
Thanks,
-Paul
"Paul" <a@.b.com> wrote in message
news:OBXyfyVCFHA.1836@.tk2msftngp13.phx.gbl...
> Never mind, I forgot to look at the depnumber column in sysdepends!
>
> "Paul" <a@.b.com> wrote in message
> news:eWN9knVCFHA.3940@.TK2MSFTNGP09.phx.gbl...
>|||On Wed, 2 Feb 2005 15:27:44 -0500, Paul wrote:

>Okay, now the hard question.
>How do I correlate a column in the sysdepends table back to a column in the
>resulting view's columns?
Hi Paul,
You don't.
You are probably thinking right now of fairly straight-forward views,
where such a mapping would be viable. But views can be fairly complex!
CREATE VIEW test
AS SELECT Col1 - Col2 AS A,
Col1 + Col2 AS B,
CASE WHEN EXISTS (SELECT *
FROM YZ
WHERE Foo = SomeTable.Bar)
THEN 1
ELSE 2
END AS C,
'Constant value' AS D,
Col3 + 1 AS E,
Col3 - 1 AS F
FROM SomeTable
How should each of the view's columns be correlated to each of the
underlying table's columns? And remember that this is also a fairly simple
example - I could come up with much more contrived views (some of my real
views for projects I do are much more contrived!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Metadata info retrieval

I am working on a project that needs to retrieve the metadata details of a s
chema (in MS SQL Server).
Could anyone help me out..
TIA,
ArrunHi
Check out the INFORMATION_SCHEMA views. More information can be found in
books online.
John
"Arrun S" <projdqe@.yahoo.co.in> wrote in message
news:CD63C343-75B4-4B6A-AE65-D9C552B25FAB@.microsoft.com...

quote:

---

> I am working on a project that needs to retrieve the metadata details of a

---

schema (in MS SQL Server).

quote:

---

> Could anyone help me out..
> TIA,
> Arrun

---

Metadata in sql server 2005

Hi
I add extended properties to table in sql server 2005, for example i add Caption property to every column.
In case I generate a datasource from table in sql server in visual studio 2005, Is there a way that "Caption" property in datatable to be filled from that metadata, or metadata is useless for dataset.
Every time i had to fill manually the caption property of datatable generated .

hi,

you can access those extended properties calling fn_listextendedproperty function, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.Invoice ( ID INT NOT NULL PRIMARY KEY , BillNo INT NOT NULL -- , others ) GO EXEC sp_addextendedproperty '1st property' , '1St property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; EXEC sp_addextendedproperty '2nd property' , '2nd property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; GO PRINT 'All available properties'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , CONVERT(VARCHAR(20), [value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( NULL , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO PRINT 'just the required [1st property] property'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , convert(VARCHAR(20),[value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( '1st property' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO DROP TABLE dbo.Invoice; --< All available properties Property Name Property vCharValue -- -- 1st property 1St property value 2nd property 2nd property value just the required [1st property] property Property Name Property vCharValue -- -- 1st property 1St property value

so, assuming you are interested in the Invoice management, you have to query for all the properties of the dbo.Invoice table columns...

this can obviously fill a dataset or a datareader you can then consume to map as desired the returned values so you could assume the "1st property" is the caption of the corresponding UI control and you can set it accordingly..

BTW, to manage those properties outside the Microsoft tools you have to resort on http://msdn2.microsoft.com/en-us/library/aa174648(SQL.80).aspx and http://msdn2.microsoft.com/en-us/library/ms190243.aspx

regards|||Thank you for your reply.
I mean , what is the extended properties that may be used automatically by visual studio 2005.
For example , Can the "Caption " property of datatable created in vs 2005 , be set automatically using extended properties?
This property is useful, because form wizard creates label with that "caption" value(when i set it at design time), and textbox bound to column.

|||

hi,

no, you have to deal with that "by hand"...

regards

Metadata for SP parameter default values

I am looking for a way to list any default values for stored procedure
parameters.
For example with this stored procedure:
CREATE PROC dbo.pr_GetSomeData
(
@.SomeInt int,
@.AnotherInt int = 0,
@.SomeDate datetime = NULL
)
I'd like to be able to create a table that would look like this
ParameterName DefaultValue
@.SomeInt
@.AnotherInt 0
@.SomeDate NULL
I can get at the parameters themselves through information_schema.PARAMETERS
but I don't see a way to get at the default value.
Any suggestions would be appreciated.
Joel Reinford
Data Management Solutions LLCSQL Server does not store information about this in any sys table. One
possible solution could be parsing the sp code from syscomment or
information_schema.routines for the sp in question.
AMB
"Joel Reinford" wrote:

> I am looking for a way to list any default values for stored procedure
> parameters.
> For example with this stored procedure:
> CREATE PROC dbo.pr_GetSomeData
> (
> @.SomeInt int,
> @.AnotherInt int = 0,
> @.SomeDate datetime = NULL
> )
> I'd like to be able to create a table that would look like this
> ParameterName DefaultValue
> @.SomeInt
> @.AnotherInt 0
> @.SomeDate NULL
> I can get at the parameters themselves through information_schema.PARAMETE
RS
> but I don't see a way to get at the default value.
> Any suggestions would be appreciated.
>
> Joel Reinford
> Data Management Solutions LLC
>
>

metadata driven SSIS package--table variable

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

Oliver

Hmm,

I suppose you could do it that way. I am currently generating complete SSIS packages from metadata. No variables. Explicit references. You would still need to handle the objects of the package that change, like columns datatypes, etc...|||You cannot have 1 data-flow that is used for different tables (unless the structure of each table is exactly the same - which I doubt).

You will need multiple data-flows, one for each table.

Once you have your multiple data-flows, decide which one of them to execute using workflow expressions. Allan and Darren have a good example of this here: http://www.sqlis.com/default.aspx?306

-Jamie|||Patrick,

Do you think you could post a simple example of what you are doing? I think there are a lot of people in the SSIS community who are realizing they need to head toward dynamic package creation in order to achieve the maintainability that they were able to with DTS and ActiveX scripts.

Thanks,

Jason.|||Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver|||

Oliver77 wrote:

Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver

"More or less the same" isn't enough. They have to be exactly the same for them to be able to use the same data-flow.

-Jamie|||

Can we create a SSIS package using VB .NET? If we can, we can call metadata driven stored procedure to build the transformation task specifically for each table.

|||

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

|||

Oliver77 wrote:

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

One of the samples provided with the product shows how to build a package progamatically (although I've just looked at my Junne CTP installation and can't find it - perhaps its been removed?). Once you have mastered how to do that you could work on building them based on metadata. I haven't seen any examples of doing that - perhaps you'd be the first person to do it?

-Jamie|||Patrick,

DO you have sample code? Thanks!|||Patrick,

Can you give some rough ideas as what you are using in terms of programming languages, scripts or SSIS tasks to create metadata driven SSIS packages.

Many thanks!

Oliver

Metadata driven SSIS

I am looking for some pointers on Metadata driven SSIS. Specifically, how to automate creation of a large number of packages in SSIS using metadata. I am looking at this as a solution to deploy multiple packages on multiple servers. Any other ideas on this would be highly appreciated.

Thanks

Vishal Verma

This feature is not available right now. But seems we can get this feature in the next service pack (may be in end 2006)|||While I cannot confirm Suresh is correct (and in fact this is the first time I'm hearing this being associated with service pack), you can go through the object model documentation and generate the packages programmatically.

regards,
ash|||Well, I am not sure either. I read some posts by Microsoft guys (Ash why dont u chat with ur boss Kamal or Donald) and what I infered is that we might get something in near future.|||Ash is correct as usual. There are no confirmed plans for work in the SP. You may have interpreted what Kamal or Donald said as an assertion of forthcoming features. That is not correct. The planning for SPs and other releases is still under way and will not be announced for some time.
K|||By the way, I think what Suresh may be talking about here is that we're doing some work to identify and promulgate best practices around metadata for IS that will be released as either a KB article or whitepaper. That is supposed to be available around the time IS is released. But, no new metadata features, per se.