SSIS And XML Schemas - Substitution Groups Not Supported
Feb 14, 2008
I'm looking at building an SSIS workflow to integrate new XML product data from our supplier (they're switching to XML instead of the Current MS Access database they use). They're using a schema based on the OAGIS structure. When I try to load a Data Flow Task with the XML file, to attempt to parse it, and I specify the XSD schema they have provided, SSIS errors out by telling me:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Data Import [1]]: The component "Data Import" (1) was unable to process the XML data. Substitution Groups not supported. See element "Dimensions" with "substitutionGroup=http://www.openapplications.org/oagis/9: Dimensions".
Pipeline component has returned HRESULT error code 0xC02092B0 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Is there any way at all to get around this, or am I screwed? As I said, this data is provided to us by our supplier - we have no control over it, but I am looking to pull the data from XML and put it into a regular SQL table which I am in the process of defining - we have a large traffic e-commerce site and obviously cannot use XML as the "back-end".
I've two SQL databases, the first one having the schema named sch1 and the second one with the schema named sch2. I need to copy data of more tables belonged to the sch1 into the sch2 of the second SQL Server db and I'd like to create a SSIS 2012 pkg in order to achieve this goal.
I have the following scenario: N identical Databases (corresponding to different Fiscal Years, with names <Company Name>.<YEAR>). We want to consolidate the N DBs to a New Datawarehouse.
In SSIS we have designed a Dataflow that reads through a OLE DB Source (Connected to one of the N Databases) and maps to a OLE DB Destination (Connected to the NEW DB).
The question is, how we loop in SSIS through the N identical Connections, so to repeatedly execute the designed Dataflow, each time with a different Connection?
Using SQL Server Business Intelligence Development Studio, when I try to create a new OLEDB connection manager and I try to connect to the SQL 2005 DB, I get an error message saying that "Client is unable to establish connection Encryption not supported on the client"
This is ONLY in SQL Server Business Intelligence Development Studio!
If I connect to the same server using SQL Server Management Studio and using server type Integration Services, everything works just fine.
However this way I cannot create a working SSIS package from the client, if not working locally on the server, which is a bit nonsense.
As part of an ETL conversion I have dataflow task created that copies data from one table to another, we use it as a way to rename fields and change data types. What is strange is that the OLE DB SQL destination has not issue if I copy data from an int to smallint and in another case from an int to a numeric field and it runs fine. So I decided to test using a SQL Server Destination as I heard it may be faste that using OLE Db.
No I am getting the following error
Error: 0xC02020F5 at Insert into TRANSACTION, SQL Server Destination [32]: The column "ID" can't be inserted because the conversion between types DT_I4 and DT_NUMERIC is not supported. Error: 0xC02020F5 at Insert into TRANSACTION, SQL Server Destination [32]: The column "ID1" can't be inserted because the conversion between types DT_I4 and DT_I2 is not supported.
I do understand the error but not why it runs fine using the OLE DB but when running it through SQL Server Destination I get this error.
I want to write a script that will allow the individual running the script (in Management Studio) to be able to insert different values to the WHERE clause. Basically I want to prompt the user for his/her input. How can I accomplish this?
I am looking for "substitution variables to temporarily store value" with sqlcmd in sql server . I know the query in oracle, but looking for equivalent query in sql server?
In oracle query is like this:
select * from tablename where deptnumber = &department_number; Enter Value for department_number
I would like to run a report for each month over two years. I am currentlyusing a date range like this. Then manually substitute the error_timebounds for each month and rerun the query. How can I script this so I canprogrammatically perform the substitution in a loop. Thanx in advance.select count(*) from application_errorswhere error_message like 'Time%'and error_time >= '1Apr2004' and error_time < '1May2004'
Hi, I am trying to create a maintenance plan on an MSX server and push it out to a TSX server. When I try to run the job on the TSX server it goes into suspended status.
If I look in the SQLAGENT log I see the following error
2008-04-30 15:04:11 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system) 2008-04-30 15:08:19 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system) 2008-04-30 15:09:28 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system) 2008-04-30 15:27:36 - ! [LOG] Step 1 of job 'SystemDatabases.Back Up Database (Full) (Multi-Server)' (0x97394B08F6599040A18D93367FBDB5F7) cannot be run because the SSIS subsystem failed to load. The job has been suspended 2008-04-30 15:35:13 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system) 2008-04-30 15:50:27 - ! [LOG] Step 1 of job 'SystemDatabases.Back Up Database (Full) (Multi-Server)' (0x97394B08F6599040A18D93367FBDB5F7) cannot be run because the SSIS subsystem failed to load. The job has been suspended
my sql info is Microsoft SQL Server 2005 - 9.00.3239.00 (X64) Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I am in middle of my transformation where I have to assign records equally among 3 different groups. I can do that in SQL using NTILE() Over() function. How do I do that in SSIS package. I have applied different business rules during transformation to get unique records and now I have to assign those records to 3 group in and generate excel report.Basically, I will need to have another column which will have those group numbers.
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.
I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:
ID1 ID2 DisplayData
1 1 A
1 2 B
1 3 C
2 1 A
2 2 B
2 3 C
Parent group is on ID1, child group is on ID2, report would show:
1 1 A
2 A
3 A
2 1 A
2 A
3 A
Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
I'm trying to understand the schemas in SQL Server 2005. I have a script which was given to me by someone as follow and I'm trying to run the script but it gave me an error at the create view section:
Assume an empty database has been created.
CREATE SCHEMA [mySchema] AUTHORIZATION [dbo] GO
CREATE TABLE [tblABC] ( ... ) /*note: it didn't bind the table to the above schema*/
create view mySchema.vwMyView as SELECT * FROM mySchema.tblABC GO
However I'm confused because the above CREATE VIEW made a reference to the table with the schema. Can someone help clarify. When i tried to create the view, it gives me an error that it is unable to find the mySchema.tblABC table. To me, the error is correct as per my understanding of binding a table to a schema ---> If you do not create a table and bind it to a schema, you are not able to call it together with the schema.
What I'm trying to understand is, without explicitly stating [mySchema] during the table creation, is there a way where it is mysteriously binded thus the CREATE VIEW somehow understood that tblABC has been binded therefore it called the table in the view? or is this just a script error.
Btw, this script was generated from a working database.
Hi,Anyone know an online source for database schemas, or schemas for singletables i.e. where can I download a schema for an addresses table?--Richhttp://www.badangling.com -= Sea fishing badly explained =-
Is there any way to create a local database cache from an SQL 2005 database that uses non standard schemas (like AdventureWorks). I have tried in Visual Studio 2008 but any database that does not use .dbo will not allow any tables to be copied locally. Even setting the default schema for my login does not enable adding any tables in the Configure Data Synchronization dialog box. If it is not possible via Visual Studio is there any other way? I notice all examples with Sql Compact conveniently use Northwind. Seemingly Compact edition does not support non .dbo schemas.
I have schema called [sitex] for some tables . I have to perform insert or update operations in to these tables through views only and these views were created with [site] schema
All stored procedures are written in the point of view only ( all DML operations into the tables are through views).and these stored procedures has schema same as views.
Now come to the permissions and authorization.
We have some users like sa,112,janu
These users must have permissions only on site schema not on [sitex]. I have a role called €œtestrole€? with 112,janu users and I gave authorization on site schema on for testrole
ALTER AUTHORIZATION ON SCHEMA::[site] TO [testRole] Now I€™m getting error select permessions are denied on [sitex] schema.
But our requirement is 112 must don€™t have any type of permissions on tables
And also is there schema to schema permissions. (that mean [site] schema has permissions on [sitex] schema .
Can anyone give an introduction to the use of schemas i SQL Server 2005. If have noticed the feature, but i would like to know why and how the use of schemas is a good thing.
If anyobe has a link to a whitepaper or practical insight that would be nice.
We're using Sql Server 2000. The one database contained tables and stored procedures which were possibly updated with some script information. Is there an application(commercial or free) or script I can use to compare the base database against this updated database to confirm there schema information is the same.
I'm trying to find some documentation or procedures that will allow me to xfer existing users' roles, etc from sql2000 to sql2005. When our sql 2005 database was restored on a new server (from sql2000), all of the database users were orphaned. I need to create identical server logins and then make the logins database users. It is also important to understand the significance of the new schema concept. The Microsoft SQL2005UpgradeTechRef.doc white paper does not explain any of this. Ultimately, I would like to create a script for the above scenario once a sql2005 db is restored from a sql2000 db. Could someone please help me out on this?
Is there a way to create a table in SQL Server Management Studio under a different schema? I've tried setting my default schema and adding a table, but that didn't work. I know I could do it with a SQL statement, but wanted to know if I could do it with the designer.
I have some tables sitting in schema "A", then I created a view in schema "B" based on the tables in schema "A". Then I gave the user just access to schema "B" to only run the view but not see the tables in schema "A".
Could it be that if the user tried to ODBC to that db, they would only see the view in schema "B" and not the tables in schema "A"?
I'm looking to gather ideas on this before I start the project; so what better way to start than to ask here? ;)
The situation is this: we are in the process of replacing a large data driven application with a new one. Before turning the old'un off (that's not for a minimum of 6 months), we need to migrate data between the two entirely different schemas, and I've been tasked with writing the interfaces.
Now, here comes the kicker; they don't just want a "straight-forward data conversion", they want to run a period where they can still update both databases and any changes in the old one are to be pushed into the new db.
Because the schemas are not the same, I believe replication is out of the question so I was looking for advice as to what possibilies I have ahead of me to achieve this.
I fear I may not have expained this well enough; so as always if there are holes in the detail let me know and I'll do my best to fill them in :)
Hi eveyone, I'm reading about namespaces and schemas in MS SQL 2005, and that's exactly what I was looking for for the structure of my database.
I've a database and 4 different applications that will use it, and every application will create a new database every year, for example, i'd like to use the following schema:
MainDatabase.MyApplication.MyYear.MyTable
In MS SQL i've created my main schema using:
USE Mydatabase; CREATE SCHEMA MyApplication GO
Now I want to add sub-groups (in this example, I need to create Year2006, Year2007, and so on).
Even If i create those schemas, how can I "add" them inside the Salary main schema? I've tryed with ALTER SCHEMA MyApplication TRANSFER Year2006
but it doesn't work since Year2006 is not an object.
We have a system with multiple modules, for each module we have a separate database. Is it better to have multiple databases or have multiple schemas. Performance, advantages, disadvantages... When is too much too much, 10-25-50-100 Schemas???
I am looking for some advice on securing a sql 2005 database.
I have a database to which I want to grant 3 different users access.
The first users, who we will call 'public' should have access to a set of stored procedures, and no access to tables without using the stored procedures they have access to.
The second users have again got access to a certain set of stored procedures, along with the stored procedures for 'public'. These should be called 'moderator'.
Finally the third set of users 'admin' should have access to all stored procedures, but again no access to the database unless its through a stored procedure.
Questions :
Using SQL 2005, how do I assign access to particular stored procedures to a user?
How do I ensure that a 'user' cannot access stored procedures I don't want them to access?
Some of the stored procedure use dynamic sql within them, will this affect my setup. If I deny direct access to the tables to all three users, but allow them access the stored procedures, can the stored procedures access the tables?
If schemas are the answer, how on earth do I set them up, Ive never seen something so badly documented online in my experience! In my development environment im using sql express, so will probably working through the query analyzer.
Hi, I am storing information being sent to me weekly into a ms sqldatabase. The one twist I am running into is that later down the linethe information I recieve may require more columns, or columns mightbe renamed so having a static database call is out of the question. Iwas using mysql but for certain reasons switched to ms sql 2000.Currently before ms sql, I query the database for show fields whichreturns all the database data such as type, column name, etc... then Iuse a giant if statement structure to deal with matching andconverting data from my users display to the information into thedatabase.So my first question is how do I either query or using datatypes builtinto C# return information about a tables setup. I was hoping therewas some sort of table array object which could be returned with namesand types, if not then just an sql statement return.My second question is I have user data which needs to be displayeddifferently then the database raw data, how do I use a C# dataset, andconvert it's column data into my raw database data without having tocreate raw insert and update sql strings myself? I would really liketo use the sort of automatic update features of the dataset.Lastly am I going about this the wrong way? Are there other ways tomap & convert datatypes between a dataset column and a table column.
My problem is very simple and that is that I'm trying to copy some tables between databases, but these tables are in different schemas. let's say I have
dbo.tableA sch1.tableA sch2.tableA sch3.tableA And I just want to copy let's say sch1.tableA to a Different DB. If I use Transfer SQL Server Object task and select the table and save the package and try to open the task again, all the tables with name TableA will be selected!! it seems like although it does show the schema ( when I am selecting the table manually ) but it doe snot store the schema detail in the tablelist collection property of the task. Would please recommend any other way to achieve this. Many Thanks in advance
