Metadata Update
Dec 28, 2006
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
View 2 Replies
ADVERTISEMENT
Dec 14, 2006
Take a Dataflow Task, with an OLEDB Source Component and an OLEDB Destination Component in it. The Source component's Source is stored in a SQLQuery variable, and the Destination component's Destination is stored in a TableName variable. The Dataflow Task is put into an For Loop container.
I just want to do one thing:
In the For Loop, everytime it send a new value to the SQLQuery variable and the TableName variable, then I could use it to transfer many tables in a same logic.
My question is: Really, I could send new values to the variables and make it go. But everytime it says "The external metadata column collection is out of synchronization with the data source columns." Because the external metadata was recorded and not be change automatically everytime of Loop.
How to update the source columns'metadata automatically?
View 3 Replies
View Related
Feb 21, 2007
SSIS seems to automatically set the metadata type and for "typed" sources like database and XML connections it seems to take whatever the source column datatype is. If you use a cast or convert in the your source SQL query, it will not change the datatype of the metadata. This becomes an issue when doing things like merge joins on data from different sources and the join columns are different types (e.g. a ZipCode in one system is a varchar and it is an int in another system). I've been working around the issue by editing the XML code and changing the datatype there. Is there any way to do this through the GUI?
View 2 Replies
View Related
May 6, 2015
we run 2008 std edition (I believe r2 on this server, will double check) and I need to find a needle in a haystack.
I am looking at two tables and would like to learn which, if any objects on same db updates these tables.  I'd even live with any references to these tables. I already know sql agent is out of the picture. I'm looking for a static approach, not a monitoring one because its possible updates don't occur till month end.
Is there a way in sql server to query the answer to such a question using dmvs or something like dmvs?  What if I get bold and want to search all db's on this server for objects that update my two tables even across dbs?
In the mean time, i'll look at scripting all triggers and procs , and doing a search on these table names in the scripts.Â
I don't know that I've ever seen a way to script all triggers before. Procs I can easily script from the object explorer details. But I'm not sure if in explorer details--> server objects --> triggers does what I hope it does.
View 6 Replies
View Related
Oct 4, 2013
I'd like to figure out how to use the @FieldDescription table below as an intermediate table between the @SourceData and @Stops data.
declare @Stop table (StopId int, UserField varchar(20))
declare @FieldDescription table (Label varchar(10), ColumnName varchar(10))
declare @UpdateSource table (HasPathway varchar(10))
insert into @Stop (StopId, UserField)
values (1, 'Yes')
[code]...
I want to update @Stop.UserField with thevalue from @UpdateSource where @UpdateSource.HasPathway=@Stop.UserField...but I need to use the @FieldDescription table to determine how to map the columns.
View 3 Replies
View Related
Jul 20, 2005
What is "MetaData" in "Data Transormation service" and what is"MetaData services" ?and in which field I can use them?Thanks
View 1 Replies
View Related
Aug 22, 2001
Hi All!
I recently added a column to an existing table with a getdate default. When doing a query from that server everything works fine. When a query is ran from a remote server I get an SQLOLEDB error message saying 'inconsistant metadata'. I've tried dropping the remote server and reconnecting but that didn't seem to resolve the problem. Can anyone tell me how to resolve this error. I believe the error number is 7353.
Any suggestions appreciated!
Thanks Jeff!
View 2 Replies
View Related
Jul 29, 2005
Hi,Is it possible to get metadata (i.e. descriptions of tables etc.) insql-server? In Oracle you can retrieve this information with tables likeall_objects, user_tables, user_views etc. For example, this query selectsthe owner of the table 'ret_ods_test' (in Oracle!):select ownerfrom all_objectswhere object_name = 'ret_ods_test'What's the equivalent in sql server?Thanks a lot.
View 2 Replies
View Related
Mar 18, 2007
hi,
Is there a way to find out which user defined procs/child packages etc are been called in SSIS packages using some metadata. The idea is to have a document which lists the number of packages called, whats sprocs and child packages are executed by those pkgs..
I have checked the SSIS metadata whitepaper but that is too generic.
View 5 Replies
View Related
May 15, 2006
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?
View 11 Replies
View Related
Feb 26, 2008
I'm working on capturing metadata from my SSIS packages. I have found multiple postings and a white paper that
reference a download toolkit for just this purpose. The link is dead and a search renders no results - can anyone help?
SQL Server 2005 Business Intelligence Metadata Whitepaper
View 7 Replies
View Related
Feb 26, 2007
-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.
View 1 Replies
View Related
Apr 4, 2008
i am using icolumnsrowset interface to get some metadata about the columns in a rowset, but i never got unique and primary key columns in the columnsrowset, they always return null, when using sqlcedataadapter i simply add addwithkey option to the adapter to determine it, but i dont know how to do it by using ole db interfaces, i have tried to set DBCOLUMN_KEYCOLUMN flag to true on ccommand<cynamicaccessor, crowset> but it seems it rejects it, generating an unknown error, error object says almost nothing except that 'errors occured[,,,,,]' text
can someone tell me, how can i retrieve columnsrowset with that unique and primary key sections filled?
View 5 Replies
View Related
Apr 5, 2006
Hello, i would like to know if it's possible to generate automatically a word document or an excel document that will contain all the metadata definition, for example containing the source columns names, their datatype, and the destination with their datatypes, so that it would easy to create a data dictionnary .
Thank you in advance.
View 2 Replies
View Related
Nov 9, 2007
Hello everybody,
It's now quite some time that one particular behaviour of SSIS is really frustrating me and I would like to know if I'm the only one experiencing this problem or if other people have the same problem.
The issue I'm talking about is SSIS 'dependency on what is written in the XML files describing the flows.
Particularly with the Data Types of columns.
I'm explaining myself: Imagine your are developping a flow containing several numeric(18,0) columns...
During the flow you have to perform a lookup on an Integer Field.... Of course this operation is not allowed as a numeric is not mappable with an Integer... (This is, in my opinion, a nonsense as an implicit conversion has to be possible).
as a result of this behaviour, I decide to change the datatype (numeric) from my source query to an integer and use it in the Lookup which of course succeeds but now I have a second problem: each lookup in my flow has an error handling branch which I'm joining back using a Union transform. and there we have the second irritation: the Union transform doesn't replicate the Data Type changes that occured upwards in the flow... worse: it even has no interface to let you modify the data types like the advanced editor of some transforms or data sources. (I've just lost a complete dataflow while trying to modify it manually in the xml file directly :-( for those who are considering modifying directly the XML, don't!! You are asking for trouble and a lot of frustration when you'll switch back to the designer to see the effects )
My question is now: Am I misusing SSIS?? Is there somewhere an option to activate in order to get this behaviour fixed??
Has anyone else experienced this problem?? How are you solving this??
Are there any plans in the future to loose this dependency on the datatypes or at least add some implicit conversions??
Thanks in advance for your replies, suggestions,questions and other thaughts about this subject :-)
Alain
View 14 Replies
View Related
Feb 12, 2008
Hello-
I have a question regarding my metadata information. I finally setup my fixed width file which took some time. Is there a way that I can backup my metadata so I wont have to recreate these setting again. I'm thinking the format of the file is stored in the metadata so if I have a user running the SSIS package from the Business Intell Studio they wont reset all of my columns. Is there a file I can restore or backup if this should happen
Shanon
View 3 Replies
View Related
Sep 26, 2007
Hi all of you,
I'm focused on writing a SSIS metadata application. I'm trying to get all the metadata for all the DTSX packages from a concrete server.
I'm loading successfully tasks as Execute Sql Task, ForEach task and so on but how could I do the same for
Data Flow Task and its components. ?¿¿
For a Data Flow I don't know how to begin...
If TypeOf tmpTaskHost.InnerObject Is ????????????????? Then
My central code is the following:
sServer = "TEST1"
pkgIn = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)
'''''''''''''''Para cada carpeta creada a partir de MSDB
For Each pkgCarpeta In pkgIn
If pkgIn.Item(y).PackageDataSize <= 0 Then
pkgCarpetaSSIS = app.GetPackageInfos(pkgCarpeta.Name, sServer, Nothing, Nothing)
Else
pkgCarpetaSSIS = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)
End If
If pkgCarpetaSSIS.Count >= 1 Then
While i < pkgCarpetaSSIS.Count
'cargar en memoria el SSIS en el objeto PAQUETE
pkg = app.LoadFromSqlServer(pkgCarpetaSSIS.Item(i).Folder & "" & pkgCarpetaSSIS.Item(i).Name, _
sServer, Nothing, Nothing, Nothing)
Dim task As Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask
Dim task2 As Microsoft.SqlServer.Dts.Runtime.ForEachLoop
Dim exe As Executable
Dim tmpTaskLoop As ForEachLoop = CType(exe, ForEachLoop)
Dim tmpTaskHost As TaskHost = CType(exe, TaskHost)
For Each exe In pkg.Executables
Select Case exe.ToString
Case "Microsoft.SqlServer.Dts.Runtime.ForEachLoop"
tmpTaskLoop = CType(exe, ForEachLoop)
Case "Microsoft.SqlServer.Dts.Runtime.TaskHost"
tmpTaskHost = CType(exe, TaskHost)
End Select
issue raises here.
If TypeOf tmpTaskHost.InnerObject Is ExecuteSQLTask.ExecuteSQLTask Then
n1 = tmpTaskHost.Properties.Item(33).GetValue(tmpTaskHost)
For y = 0 To 42
Console.WriteLine(tmpTaskHost.Properties.Item(y).Name)
Next
End If
Next
'contador de paquetes
i += 1
End While
y += 1
End If
Next
..
..
..
..
..
Thanks a lot for ideas and thoughts!!!!
View 4 Replies
View Related
May 11, 2007
As title, such as the PK, data type of each column, etc.
Thanks,
Ricky.
View 6 Replies
View Related
Feb 25, 2008
Hi,
I'm running the following query in SQL Server 2005:
select name from master..syslogins;
It is being executed from within a stored procedure.
For user 'sa' - I get the complete list of users.
For a user (say 'user1') with NO sysadmin privilege - I get only two names: 'sa' and 'user1'
Is there a way for me to retrieve the complete list of users even for 'user1' without making any changes to his profile (or making very MINIMAL changes to profile)?
I don't want to give sysadmin profile to this user.
I know 'GRANT VIEW ANY DEFINITION TO public' works, but don't want to do that either.
-Anshul
View 4 Replies
View Related
Jun 26, 2007
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?
View 2 Replies
View Related
Apr 20, 2000
I'm trying to create a Data Dictionary view from system table info like tablename, fieldname, datatype etc. I can find all that I need except for the "description" field which is displayed in Enterprise Manager/ Repository metadata pane.
How can I locate this field so I can reference it in a view? Isn't it stored in a system table? Master, model? Where is it?
Thank in advance,
Phil
View 1 Replies
View Related
Jun 14, 2006
Hey there,Im wondering if there is a way to determine which views in my database use the "order by" statement. The reason I need this is because we need to migrate over to MS SQL 2005 where the order by statements are ignored within the views themselves. Now(in mssql 2005) you need to explicityly state the order by now when calling a view ie. select * from [viewname] order by column x, y desc, z instead of ie. select * from [viewname] where the view already had the applicable sorting done within the view.If those order by statements are ignored, some production software which rely on the ordered data will corrupt.Please let me know if there's a way to query the actual database and determine which views have 'order by' statements in them.thx
View 7 Replies
View Related
Mar 15, 2008
Did some searching and didn't seem to find what I'm looking for. I'm pretty new to SQL Server (most of my experience is on DB2 for z/OS).I'm building some new tables, and want to find a way to add comments to the metadata for the column. In DB2 the syntax is:COMMENT ON COLUMN TB_CREATOR.TB_NAME.COLUMN_NAME IS 'comments here';ORCOMMENT ON TB_CREATOR.TB_NAME (COLUMN1 IS ' comment here',COLUMN2 IS ' comment here', );Is there anything like this in SQL Server?Thanks!
View 9 Replies
View Related
Dec 12, 2006
I'm new to dba'ing so go easy on me. I'm looking after a brand new
installation of sql server 2005. I've created a simple db with 1 table but when I go to MSAccess ( as a newly created user) and create a linked table to sql database all the metadata is showing and I can't stop it.
I've tried going through securables but its still visible no matter how much I try to deny. From what I've read this shouldn't be possible as making metadata invisible should be standard.Can anyone throw any light on this?
View 4 Replies
View Related
Jul 20, 2005
Be careful when implementing views (from SQL Server 97/2K). SQL Serverstores the metadata on the view at creation (or the last time it wassaved). This means if you have:SELECT * FROM table1it will put all the fields of table1 in the view's metadata. If youthen change table1 and add (for example) another field, this fieldwill not be visible in the view until you open it in design view andclick save (to update it).
View 1 Replies
View Related
Aug 29, 2007
I had a pervious thread on a metadata KB but didn't get a response. So I was wondering if anyone has added on a web based help file app to SSRS? Something that you could assign additional metadata to each report. Something that would be searchable.
At this point SSRS doesn't provide enough fields for metadata. I am looking to assign some thing like:
1. Owner/Requestor of report
2. Fields in report
3. Description (other than the one provided)
4. etc..
Does anyone have any ideas of a add on, utility, or software?
Thanks
fsugeiger
View 2 Replies
View Related
Mar 25, 2008
I've tried looking in sys.syscolums and sys.syscomments, but I can't seem to find where the Description information is retain for a Field in the system tables -- any hints?
Thanks, Rob.
View 2 Replies
View Related
Jul 27, 2006
Hi
When using unpivot transformation, what exactly this error denote
"Incorrect UnPivot metadata. In an UnPivot transform, all input columns with a PivotKeyValue that is set, and are pointing to the same DestinationColumn, must have metadata that exactly matches "
data on which i was trying unpivoting is -
Name
Pd1
Pd2
Pd3
Pd4
Utsav
111
211
311
411
Verma
122
222
322
422
Nucleus
133
233
333
433
Noida
144
244
344
444
View 3 Replies
View Related
Feb 27, 2007
Good afternoon,
I'm having the following error when I run my mining model.
Errors in the metadata manager. The dimension with the ID of 'TMP ~MC-ID' cannot be found in the '' database.
I think the error is in caseprocessor.cpp at the ProcessCase method. There's a code snippet below:
The intention of the following code snippet is to run over the attributes of a case and put them on a vector that holds all the cases.
for (UINT iAttribute = 0; iAttribute < _cAttribute; iAttribute++) {
if(iAttribute == 0)
continue;
if(iAttribute == 1)
continue;
if(iAttribute == 2)
continue;
if(iAttribute == 3) {
vet_termos.push_back(rgdmstatevalue[iAttribute].ch);
continue;
if(iAttribute == 4)
vet_dados.push_back(rgdmstatevalue[iAttribute].r8);
DBL dblValue = 0;
if (_vbCategorical[iAttribute]) {
dblValue = (_bMissingAtRandom)
? ::DblCategoricalMAR(rgdmstatevalue[iAttribute])
: ::DblCategorical(rgdmstatevalue[iAttribute]);
}
if (_viewtype == CASEREADER::viewtypeSparse) {
if (dblValue != dblMissing) {
CHECK_STL_MEM(_vdblValue.push_back(dblValue));
CHECK_STL_MEM(_viAttribute.push_back(iAttribute));
}
}
else {
_vdblValue[iAttribute] = dblValue;
}
The attributes of the table are like following:
Index (iAttribute) == 0 -> This is an auto-incremental ID and is the primary key. Integer.
Index (iAttribute) == 1 -> Another ID. Non-key on this table. It is Integer.
Index (iAttribute) == 2 -> Another ID. Non-key on this table. It is integer.
Index (iAttribute) == 3 -> A term. It's a word. This is non-key and the type is varchar.
Index (iAttribute) == 4 -> Weight of the word. Non-key and type double.
As you can notice I also changed the union DM_STATE_VALUE definition on dmalgo.h to accept the reading of a string (if (iAttribute == 3)) :
typedef /* [public][public][public][public][public][public][public] */ union __MIDL___MIDL_itf_dmalgo_0000_0002 {
DM_STATE_INDEX si;
DOUBLE r8;
char* ch;
} DM_STATE_VALUE;
So... Does anyone can help me with the error I discribed on the metadata manager ? I would be very pleased with any help.
Thanks a lot for your attention.
-Renan Souza
View 12 Replies
View Related
Feb 1, 2006
Hi All,
Is it possible to capture the runtime metadata relating to the number of rows processed by a graph, the number of rows inserted/updated in the target blah blah using SSIS.
I have only seen metadata extensions for starttime, machine name etc. but not for this
THanks
Manish
View 2 Replies
View Related
May 24, 2007
Does anyone know how to obtain the physical server name that a SQL failover cluster instance is running on through the system tables or other database commands? Thanks in advance.
View 3 Replies
View Related
Aug 29, 2007
Usually, the XML Source will refresh its metadata when I don't want it to, and will cause me to repair all downstream metadata when it does. This time, I actually want it to refresh, as the schema changes I've made are significant.
Yet, I don't see any "Refresh" button in the XML Source UI. Even clicking "Browse" and browsing to the schema doesn't force a refresh. I've had to change the schema name (it has to be to an existing schema!) then change it back in order to force a refresh.
Is there a "Right" way to do this?
And is there any way to "right-size" the amount of metadata disruption this causes? I was pleased to see that updating a database schema limits the SQL Server Destination to updating only the changed metadata. Yet, any change at all to the XML Schema or XML file will cause all of the XML Source metadata to be invalidated, a process that takes several minutes at 100% cpu usage!
View 5 Replies
View Related
Oct 17, 2007
I am looking for anyone who can help me find an existing tool that will allow my programming team to build reports and data extracts simply and easily from a form of query builder. Unfortunatley, my database is structured as a metadata store using name data pairs. The actual structure is something like:
DateTime; Customer (integer); fieldname(string); fieldvalue (string)
In addition, I have another table that is structured similarly to store numeric data
DateTime; Customer (integer); fieldname(string); fieldvalue (Integer)
So, for the query tool to even start, it needs to scan both tables for distinct values in the fieldname column for a specific date/time range for a specific customer. Once that is done, it can display the field names and begin to search for the data I am looking for.
In addition, If I want the names of all people in the system who have bought a pie from my customer 1, the query tool will have to search for the fieldvalue "pie", return the key cells and then search for the fieldnames "Name" where the fields match (that or do it in a compound query).
Is there any tool anywhere out there that might have such capabilities?
View 3 Replies
View Related
