I'm using the XML Source to process a document with 17 elements in it. That leaves me with 17 outputs from the source.
Any time I make even the slightest change to the schema, it causes the metadata collection of the source to be updated. This causes everything in the data flow to have inconsistent metadata. This can take a very long time to fix, even though all I have to do is open the top transform and allow name mapping to work.
Is there any better way to make changes to an XML schema being used this way? Any tricks?
I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?I was hoping someone had experimented and found which works best.
I have a serialized XML that I got from a dataset. In my 'Data Flow Task', I bind the 'XML Source' source to this XML file. Since the XML file is having the schema along with it, I check the 'Use Inline schema' option. However, when I put a dataviewer to see the rows getting sent to the destination, I see that no rows are getting transfered. As you will see from the XML file I am trying to use, I do have one row to transfer.
I tried kepping the schema file and the content file separate and that worked. I am not sure if there are any inherent issues I need to take care of, when using inline schemas to transfer data. I have the SP2 for SQL 2005 installed.
I have a MsAccess db containing a table called Employees which i am transforming to Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.
However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :
Employees empId int, empName varchar(60), empAge int
Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or are not null. If they are of type smallint,i have no problem.
However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it.
THe schema of Sqlserver table is same as of that of Employees in msaccess.
I want to be able to programmatically set the schema location for an XML source. I first thought it would be a simple task using expressions and variables but it doesn't appear to allow anything in the way of setting it at runtime. Is this possible?
I have a MsAccess db containing a table called Employees which i am transforming to a staging table in Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :
Code Snippet Employees empId int, empName varchar(60), empAge int
Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or string etc. If they are of type smallint,i have no problem.
However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it. THe schema of Sqlserver table is same as of that of Employees in msaccess.
I want to compare the schema of the incoming access tbl fields with my desired schema and all mdb's having data types that are higher or incompatible with the desired schema should be moved to the error.
Is there a way to configure the XML source component to use a schema file whose filename is in a variable? Now I know it doesn't make any sense to have an XML source whose schema can vary, however, I have a parent package with a ton of child packages, all of which process the same XML input using the same schema. What I would like to do is for the parent to load the filename of the XML input file into a variable, and the filename of the XML schema file into another. Each child would have its XML source configured to pickup these variables from the parent, so that if the location or name of the schema changes, I don't have to edit each package one-by-one. However, it doesn't seem possible to specify anything but a filename for the schema. Any suggestions?
I'm having a problem with the XML Source data flow component not transferring the length attributes from an XML Schema to the column attributes of the output table.
An example schema that I have is:
<?xml version='1.0' encoding='UTF-8'?><data xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'><xsd:schema> <xsd:simpleType name='NameType'> <xsd:restriction base='xsd:string'> <xsd:minLength value='0'/> <xsd:maxLength value='50'/> </xsd:restriction> </xsd:simpleType> <xsd:element name='Name' type='NameType' nillable='true'/> <xsd:simpleType name='FamilyType'> <xsd:restriction base='xsd:string'> <xsd:minLength value='0'/> <xsd:maxLength value='50'/> </xsd:restriction> </xsd:simpleType> <xsd:element name='Family' type='FamilyType' nillable='true'/> <xsd:element name='row'> <xsd:complexType> <xsd:sequence> <xsd:element ref='Name'/> <xsd:element ref='Family'/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name='data'> <xsd:complexType> <xsd:sequence> <xsd:element ref='row' maxOccurs='unbounded'/> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema> <!-- data follows --> <row><Name>Fred</Name><Family>Jones</Family></row></data> When I reference file in the XML Source data control, it correctly infers that there are two columns, but the length of the strings in the columns are set as 255.
This behaviour appears to be at odds with the SSIS documentation (SQL Server Integration Services/Integration Services Object and Concepts/Data Flow Elements/Integration Services Sources/XML Source), which states (highlighting mine):
When the data is extracted from the XML data file, it is converted to an Integration Services data type. The XSD or inline schema may specify the data type for elements, but if it does not, the XML Source Editor dialog box assigns the Unicode string data type (DT_WSTR) to the column in the output that contains the element, and sets the column length to 255 characters. If the schema specifies the maximum length of an element, the length of output column is set to this value. If the maximum length is greater than the length supported by the Integration Services data type to which the element is converted, then the data is truncated to the maximum length of the data type. For example, if a string has a length of 5000, it is truncated to 4000 characters because the maximum length of the DT_WSTR data type is 4000 characters; likewise, byte data is truncated to 8000 characters, the maximum length of the DT_BYTES data type. If the schema specifies no maximum length, the default length of columns with either data type is set to 255. Data truncation in the XML source is handled the same way as truncation in other data flow components. For more information, see Handling Errors in Data.
Has anyone had any luck in getting string lengths automatically extracted from an XML document? If so, where I am going wrong?
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, Bryan Ax
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
I use SQL Server 2005I have tables tblUserData, tblUsersAndGuestbook, tblGuestbooktblUserdata contains:UserCode intUsername nvarchar(50)tblUsersAndGuestbook contains:Usercode int (FK to tblUserData)GBEntryCode inttblGuestbookGBEntryCode int (FK to tblUsersAndGuestbook)GBText textNow...if I delete a user in tblUserData I want to also delete the entries in tblUsersAndGuestbook AND in tblGuestbook.I've heard something about cascading delete, but how can i configure that in my database?Or do I manually need to delete all entries from code?
Hi I have three params p1 , p2 and p3. All 3 are non queried with values Yes and NO . if p1 is yes only i have to enable the remaining twp params otherwise disable them. Can some one suggest as to how this can be achieved. Thanks
Hi, I read all the existing material in SWYNK but still am not clear on the following question. What is the best way to perform Cascading actions (Delete & Update) with foreign Key Constraints declared? We are using SQL Server 7.0 thanks Rozina
It deletes all rows in the table specified that conform to the criteria selected, while also deleting any child/grandchild records and so on. This is designed to do the same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys table to find any child tables, then deletes the soon-to-be orphan records from them using recursive calls to this procedure. Once all child records are gone, the rows are deleted from the selected table. It is designed at this time to be run at the command line. It could also be used in code, but the printed output will not be available. */ ( @cTableName varchar(50), /* name of the table where rows are to be deleted */ @cCriteria nvarchar(1000), /* criteria used to delete the rows required */ @iRowsAffected int OUTPUT /* number of records affected by the delete */ ) As set nocount on declare @cTab varchar(255), /* name of the child table */ @cCol varchar(255), /* name of the linking field on the child table */ @cRefTab varchar(255), /* name of the parent table */ @cRefCol varchar(255), /* name of the linking field in the parent table */ @cFKName varchar(255), /* name of the foreign key */ @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */ @cChildCriteria nvarchar(1000), /* criteria to be used to delete records from the child table */ @iChildRows int /* number of rows deleted from the child table */
/* declare the cursor containing the foreign key constraint information */ DECLARE cFKey CURSOR LOCAL FOR SELECT SO1.name AS Tab, SC1.name AS Col, SO2.name AS RefTab, SC2.name AS RefCol, FO.name AS FKName FROM dbo.sysforeignkeys FK INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id AND FK.fkey = SC1.colid INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id AND FK.rkey = SC2.colid INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id WHERE SO2.Name = @cTableName
OPEN cFKey FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName WHILE @@FETCH_STATUS = 0 BEGIN /* build the criteria to delete rows from the child table. As it uses the criteria passed to this procedure, it gets progressively larger with recursive calls */ SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + @cRefTab +'] WHERE ' + @cCriteria + ')' print 'Deleting records from table ' + @cTab /* call this procedure to delete the child rows */ EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName END Close cFKey DeAllocate cFKey /* finally delete the rows from this table and display the rows affected */ SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria print @cSQL EXEC sp_ExecuteSQL @cSQL print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName -------- The above code is good .. but has limitation...throws an error: Server: Msg 217, Level 16, State 1, Procedure spDeleteRows, Line 58 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can anyone out there suggest a better way of implementing on a database without a limitation of levels.. we are talking about a HUGE DB with lots of table and FK referentials..
This question is for Bill directly. Hi Bill, I have lots of experience with RS, implementing the beta in a production environment, I was so impressed. Currently my company uses Cognos for financial reporting, and I am hoping to replace this with RS and MSAS. I have little knowledge of MDX, so I have been using your various series of articles to get up to speed. However, I don't get the article Mastering OLAP Reporting: Cascading Prompts. I fully understand how to build the prompts, but there seems to be a step missing in the article. You never replace the hardcoded where clause in the base dataset, and I can't seem to work out how to pass the parameter from the cascading prompts to the report. Maybe I'm being stupid, but the article doesn't seem to tell us how to do that.
I am using RS 2000. I used 2 input parameters for one of the report. The 2nd parameter is based on the 1st parameter. i.e. First Parameter is Country and 2nd Parameter is State. Based on the Country selection the list of states for the selected country will get displayed. This is working fine in Reporting services as well as report server. When deploying into the application it doesn't work.
If you have solution for this I would like to share.
I need some suggestions concerning the issue cascading deletes in a self-referencing table, i.e. a table with a foreign key pointing at the primary key in itself. Although SQL Server still does not support cascading deletes the declarative way there are some other ways to handle this situation. One common way is to use trigger coding with the simple structure CREATE TRIGGER DelCascadeTrig ON self_ref_tab FOR DELETE AS DECLARE @C_FK xxxxxx SELECT @C_FK = C_FK FROM DELETED BEGIN DELETE self_ref_tab FROM self_ref_tab, deleted WHERE self-_ref_tabC_FK = deleted.C_PK END
where C_PK is the primary key column and C_FK the foreign key column. The problem is that this simple pattern does not work with self-referencing tables, because the removal of dependent rows deeper layer (n-2, n-3 etc. if the originating delete request is level n and the first level of dependant deletes handled by the trigger code is n-1) would require the delete trigger to fire more than once for the same delete operation. As far as I know, in ver. 6.x triggers are executed only once per SQL statement and in this case the n-2, n-3 etc. level rows would have been left as "orphans".
In ver. 7.0 I suppose this should work fine because of the new recursive trigger execution possibility (trigger will fire up to 32 recursive times per SQL statement), but in the meanwhile (i.e. my case util we have upgraded all our servers) the delete logic for a cascading, self-referencing relationship must be handled completely within one execution of the trigger.
My question is now: does anyone know anything about any common algorithm or trigger code example solving this problem.
tblSteps: StepID int IDENTITY ParentStepID int ALLOWSNULLS OtherID int Amount money
tblOther: OtherID int IDENTITY Amount money
Now, I have a trigger defined on tblSteps:
Code:
CREATE TRIGGER tgrUpdateAmount ON dbo.tblSteps AFTER INSERT, UPDATE, DELETE AS BEGIN DECLARE @Amount money SET @Amount = ISNULL((SELECT SUM([Amount]) FROM inserted), 0) - ISNULL((SELECT SUM([Amount]) FROM deleted), 0)
IF (SELECT [ParentStepID] FROM inserted) IS NULL BEGIN UPDATE tblOther SET [Amount] = [Amount] + @Amount WHERE [OtherID] = (SELECT [OtherID] FROM inserted) END ELSE BEGIN UPDATE tblSteps SET [Amount] = [Amount] + @Amount WHERE [StepID] = (SELECT [ParentStepID] FROM inserted) END
END
What this code basically does is this: if you update the amount of a Step that has a ParentStepID, it will take what was addded (or deleted etc...) and update it's parent with the added amount. If the Step does not have a ParentStepID, it will take the amount add add it to the Other row it corresponds to.
Here's an example of some test data: tblOther: OtherID: 1 Amount: 0
If I update the Amount to 100 for StepID=2, it also updates the amount for StepID=1. If I update the amount for StepID=1, it also updates the amount for OtherID=1. However, when I update the amount for StepID=2, it does not cascade up to the tblOther level (e.g. Updating StepID=2 should update StepID=1 which should update OtherID=1, right?)
Basically, the trigger isn't cascading. Can anyone point out what I need to do?
When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.
Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?
I am doing report development against cube (OLAP) and I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do we achieve this in a cube environment? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Hope this helps. If you have questions, please let me know.
In my report I want to make certain parameters to depend on the previous parameters. I think we can use those as cascading parameters. How to make a parameter a cascading parameter? and how to use those cascading parameter ? It would be nice of you if you can help me.
I haven't used cascading deletes in the past but we're starting a new database and it seems like a good way to go to keep data clean. Or at least it did seem like a good way until I ran some tests. I have 3 tables.
In this structure, the peopleemails table is simply an association table between the email and people table. I have setup up relationships in a diagram so that when a person is deleted, it cascades to peopleemails and removes the entry there. I also had a cascade set up hoping that when an entry was deleted from peopleemails, it would remove it from the email table but this is not happening. The relationship between email and peopleemail is primary key table email.emailid and foreign key table peopleemail.emailid. Is there a way to get this to work to remove the email address if a peopleemail entry is removed? Thanks.
We have a situation that occurs every so often with blocking ofvarious databases on one server (Win200 SQL7). It appears to happen atrandom, so I'm assuming it originates from something a user does andnot a regularily run process.We've examined the data available to us and used the very helpfulblocking code on http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html(Thanks Erland).Were getting closer to finding the problem, but need some advice onwhat to look for.This is a bit of guesswork, but we suspect that we get into asituation where blocking takes places, and this then cascades to otherprocesses which then block others in turn. The original culprit thenfinishes, but the blocks continue as the newer processes are holdingsomething else up. A bit like dominoes. It seems to take a while tofree this up.The problem we have is determining the start of this process. Once weare made aware of blocking issues, we can find out who is doing what,but almost always get a different answer/user and think we're gettingto it a little late.Ideally, I want to log the blocking somewhere so I can examine thefiles when this occurs and can therefore establish a pattern etc...Any ideas or suggestions would be welcome.
I use cascading delete on my SQL Server Database. I am experiencing along query time on my highest level delete, 10 minutes. If I deletefrom each table manually and then delete the parent, I will usually bedone in less than a minute. Any suggestions?
I have a report that is based on 2 listboxes, the second one's values dependent on the value selected in the first box. How would I display all values in both list boxes on the report if I so decided?
