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?
-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.
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!
My SSIS package has 19 XML Source inputs constructed of 4 different (XSD) Schemas. I'm trying to find a convenient way to refresh the underlying metadata at design time so that I don't have to open each XML Source, change the XSD reference, click on the columns display, change the XSD back to the original but updated XSD just to get say a new default column width for string data. Does anyone have a quicker way to force an SSIS package to pick up changes in a referenced XSD?
View 1 Replies View RelatedI am running a package with a parent child relationship.
in the parent package I am executing a loop that calls the child package each time it loops.
The child package is executing a SQL command that retuns data and attempts to load the data into a flat file.
The problem is that everytime the SQL changes the Metadata needs to change also.
How can I refresh the child packages Metadata dynamically.
I KNOW THAT I CAN'T REFRESH THE METADATA WHILE THE PACKAGE IS RUNNING.
But I can refresh the Metadata before the package executes.
Does anyone know how to script this?
Thank you
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 Relatedsql2000
I have an existing data warehouse that is refreshed nightly from system1.
We will be moving to a new system2 1/1/06.
The old system1 will be phased out by the end of 2006.
Several tables on system2 will have different keys.
I have to move the data from both systems in the data warehouse.
Here's the issue.
I have to totaly refresh the data warehouse nightly.
Once system2 is phased out how do I keep the data from system1
and refresh only data from system2.
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!
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 Relatedhi,
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.
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
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?
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.
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
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
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!!!!
As title, such as the PK, data type of each column, etc.
Thanks,
Ricky.
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
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?
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
HI,
I have a problem with the data that I want to delete and insert new one on my SQL.It will work if I just delete a row. And work well if I just insert a row.But it will not work if I delete and insert at once on one procedure.
Here's the code :
Protected Sub RolesRadioButtonList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RolesRadioButtonList.SelectedIndexChanged Session("CurrentRoleId") = Me.RolesRadioButtonList.SelectedValue ' Call Sub RemoveUsersInRoles RemoveUsersInRoles() ' Call Sub AddNewUsersInRoles AddNewUsersInRoles() End Sub Sub RemoveUsersInRoles() ' Create SQL database connection Dim sqlConn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim cmd As New SqlCommand cmd.CommandType = CommandType.Text ' Delete that row with correct UserId cmd.CommandText = "DELETE aspnet_UsersInRoles WHERE (UserId = '" & Session("CurrentUserId") & "')" cmd.Connection = sqlConn sqlConn.Open() cmd.ExecuteNonQuery() ' Close SQL connecton sqlConn.Close() End Sub Sub AddNewUsersInRoles() ' Create SQL database connection Dim sqlConn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") sqlConn.Open() ' Create new row with new UserId Dim sqlString As String = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES( '" & Session("CurrentUserId") & "','" & Session("CurrentRoleId") & "')" Dim sqlComm As New SqlCommand(sqlString, sqlConn) Dim sqlExec As Integer = sqlComm.ExecuteNonQuery ' Close SQL connection sqlConn.Close() End Sub-------------------------------------------
I can delete the row if I call 'RemoveUsersInRoles'And I can insert new one if I call 'AddNewUsersInRoles'
But if 'RolesRadioButtonList_SelectedIndexChanged' is called, it will not work well.If the row not exist, it will insert new row. And that what I want.But If the row is exist, It wont delete that row and wont insert the new one. That's the problem.
Do I need some 'pause' or 'refresh connection' here ?
Thank You.
Our application is C++ and datbases are Sybase. We are 11.5.1 Sybase. Lately some of our clients have complained about data refresh problems. That if a user has made some changes within the application then these data changes are taking long time to reflect to other users in the system. We do have in our application processes to automatically refresh data for other users but for some odd reason it is not working properly. Also every day we run sp_recompile and sp_stat on the tables and stored procs. Any ideas or leads ?
Thanks
Gohar
I want refresh data de SQL Server, every certain time at night, but I want that it does only, automaticy.
In SQL sentencs or DTS
What I can do. Thanks
You know how I mentioned before that I was working with a database with a (bunch of) view(s) that are defined like this...
CREATE VIEW MyView AS
SELECT * FROM ExampleTable
Well, after altering the underlying table I noticed that the view is not picking up the new columns which leads me to believe I need to refresh the view.
Is this possible, if so, how?
SS 2K
Is there a way (command or stored procedure) to RECOMPILE or REFRESH a USER DEFINED FUNCTION? I can recompile SPs with sp_recompile and refresh views with sp_refreshView, but I could not find any way to refresh User-defined functions (some of them are like views, with parameters).
Environment: SQL 2005 SP2.
Thanks !
Please help.I have Database A (Archived data), Database B (Transactional data).Both A and B do not have any timestamps in any tables.I need to bring A (only once because it is archived data) and B withonly differentials into Database C for reporting purposes.Currently C is cleared before loading B with differentials every nightand A is not loaded since it takes 15 hours to load.Now I want to bring A once and leave it and then bring B every nightwith differentials without clearing C.Please suggest some solutions.Thank You
View 1 Replies View RelatedHello,
I have report parameters B with default from query.
This query depend on other report parameters A that updates befor report parameter B.
While user open the report and update report parameter A, the report parameter B get default value with the right default value but when user change his selection in parameter A, the default value in parameter B doesn't changed
Any idea ?
Hi all...
when i select the 1st parameter in the report(in webpage) ... then the complete page is getting auto refreshed...same is happening for all the parameter selection...is it not possible to stop it from refreshing....
simply to say i want to get data without refreshing...
few ppl suggested me to use AJAX control but as iam not much familliar with AJAY iam trying to find some other way...can u help me pls....
Roopesh Babu V
HI,
I have attached logs for my packages which runs for every one hour.
so, by end of the day the size of the logs is getting more.
I would like to refresh the log for every day or for each time that the package runs.
How to get it done.
Could you please help in this.
Thanks in advance.
Hi all,
I have spent the last couple of hours searching previous posts to see if this problem has been address before, but I was unable to find anything.
My problem is this... I use a stored procedure to populate my report. The stored procedure has recently been modified, and while I can see these updated fields when I run it through the Data tab, the list of available fields in the Dataset sidebar does not reflect the changes.
I have tried refreshing the Report Datasets multiple times. I have tried deleting the *.rdl.data file. I have tried removing the dataset in question and re-adding it. I have tried creating a new project altogether. I've tried restarting, etc.
I recognize that I can go in and add/remove fields to/from the dataset to make it match, but this feels like a temporary fix (it resets every time I modify another of the report's dataset).
It definitely seems as if this is being cached somewhere, but where? And how can I reset this? I would appreciate any help or guidance on this, as my forehead is pretty sore from banging it against this brick wall.
Cheers!
Can i referesh a open table?
The scenario is that i have two windows open... one is the table and the other is the query that modifies the same table. I was wondering if there is a way to refresh the table so that after i modify the table i don't have to close it and reopen it to verify the changes.
cheers
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
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