Wondering how this is handled by Multicast component. If multiple copies are created in the memory and the size of the dataset is large, this could cause some performance problems. Any thoughts? TIA
View 8 RepliesI have a dataflow where each row passes through about 20 validations. This data flow could contain 200,000 to 20 million rows. Initially I set it up with a multicast component sending data each of the validations.
This got to be pretty messy.
Then I thought about using several multicast to subdivide the validations and make the data flow easier to follow. I tested using two multicasts to see if it would affect the performance of the dataflow. Results were inconclusive.
Before I continue adding and testing different multicast configurations, I thought I would check here to see if anyone else has tried and measured this?
Thanks for any insight you can provide.
I'm getting 3 copies of the result set expected, could someone take a look and tell me why. I know I covered this in school, but I can't remember the issue. Thanks
SELECT P.Quantity as Qty,
P.ItemID,
P.VendorCode,
P.Descr as Description,
P.UnitPrice as Price,
P.Amount,
I.Freight,
Rcvd = 0,
I.QtyRcvd as Ship
FROM PurchaseOrderItems P, ReceivedItems I, Received R
WHERE P.POID = R.POID AND R.IntRcdID = I.PRID AND P.POID = 193
I am creating a program that will take a master database and create separate databases for class room training.creating my own app to do this since it will have other stuff to do.i will have a master database that i will need to create multiple copies of. 2-20 copies, it is about 7GB large. it is used in a classroom training course for our company software. it will also copy a folder on the server onto multiple subfolders.each computer in the classroom will access its own copy of the database/windows folders.
What i am looking for is a fast/reliable way to create the multiple database copies. then when the training class is over and a new one is getting started, we will run my program to reset everything back to start.Should i detach/copy/attach or create a master backup and restore it 20 times. What kind of user access pitfalls will i need to look out for.
Hi,
I am new to reporting services and I'm really stuck on a design problem. Can someone please help me?
I would like to design my own print function. When a user clicks on the print icon (preferably the one that came with reporting services), the report is automatically printed twice, once with "For Person A" and the second time with "For Person B" on it. It doesn't matter where these two labels are placed on the page. These two reports need to be printed on letter-size paper regardless of user's selection. How do I do this with minimum amount of code?
Any help would be greatly appreciated!
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
Hi
Finding this forum really useful...I wonder if you could help me with this.
I've got a very simple script component and I just want to use a Dataset in it.
When I declare the Dataset i get a warning and then consequently an error. herers an image grab of whats happening:
http://www5.webng.com/hopelist/error.jpg
If you can't see the image please let me know.
Essentially VS gives me a hint to add a required assembly which it needs....but when I click to add I get a 'Visaul Basic Compiler has encountered a problem and needs to close' type error.
Anyone got any idea whats going on and why I'm having such a hard time just accessing a Dataset??
thanks
Andy
Hello,
I have a data-flow containing 150000 rows that is taking a long-g-g-g- time to run.
The bottleneck seems to be a SORT component. The SORT is immediately preceded by a MULTICAST.
Now, all 150000 rows have gone down the other output from my MULTICAST all the way to the destinaton, admittedly very slowly. My SORT is still caching the 150000 rows.
I'm wondering if the MULTICAST is causing a problem. Does each output fro the MULTICAST have its own buffer?
Can an asynch component that caches all the data (e.g. SORT) cause backpressure on the MULTICAST that might cause problems.
As I write, the package has been running 20 mins and the SORT component still has not cached all the rows.
Any clues would be welcome.
cheers
Jamie
I split a source into two. I get an Id from one after doing couple of lookups and maxId from other after doing a lookup and and an Aggregate. I want to compare Id and max Id in the same data flow. How can I do that please?
View 10 Replies View RelatedI have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.
How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?
I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? Thank you for the help.
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
Hello, I am using a multicast with 5 outputs, I attempting to pass about four thousand rows to different destinations. However, upon execution only two of the outputs send the rows to the destination. After deleting the multicast and reinserting it, different destinations received the data. The number of destinations is also not consistant, sometimes 3 work and sometimes only 1 works.
Thank you.
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
Hello,
We are trying to convert our Crystal XI reports to SQL Reporting Services 2005. Our crystal reports get their data from ADO datasets which are populated through code at run time. Is it possible to do this in SQL Reporting Services?
The only options for a dataset seem to be query and stored procedure. When i have a blank dataset it throws an error. When i try to link my dataset to code, it throws an error.
Thanks for your help!
Hi
I am new to Reporting service, Trying to create a report RS2005.
I defined two dataset (query getting data from same database)
In report designer, I inserted a group and drap columns from first dataset. and inserted second group, when i dragcolumns from second dataset it shows like this
=First(Fields!GroupStatus.Value, "ParentGroup")
in my first group columns shows like
=Fields!Member.Value
when i run the report i am getting one row in second group.
this looks like very basic mistake I am doing, since I don't have any experience in RS2005, i am posting this question,
thanks in advance
kal
Hi,
I need to dynamically create a script component (by code).
Is it possible?
Thanks ahead,
Eran
I want to do the following in a package:
Create a table at the beginning of a package (using a ExecuteSQLTask component) and then use the created table as a OLE DB destination component, later on the package.
Is this possible in SSIS?
The problem I run into is that I have to point the OLE DB destination component to a table and set up mappings, however as the table does not exist until the package is running, it does not seem to be possible.
I've looked at:
http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
Which is slightly similar to what I want, but the table I create would not be a temp tables, and I need to set up mappings and I don't see how this is possible.
Thanks
Hi,
I have to design a tabular report where the data in all the columns of the table comes from the cube, but for one column where the data comes from the ODS table.
Could anyone please let me know if it is possible to combine the results of the query from a cube with query from an ODS table and display together in a single report?
Any thoughts on this would be greatly appreciated
Thanks.
I usually use Access for my database work, but a recent request needs data from a table with about 8 million records - a mite outside of Access' league.
So, I am using SQL Server Report Services to create this report.
Essentially, I need to be able to use a table from database A and a table from databse B in a single dataset for the report I'm making.
I'm self-learning this package and have not been able to locate if this is even possible, let alone how to do it. I can't see it not being possible, so I figure the procedure is simply eluding me.
I've seen a thread on a similar - if not the same - problem, and the answers given all seemed to be based upon the user being able to write to the database server or in some other manner manipulate the server. I have no such capabilities. All I can do is look at the data and create a report based upon what I see. No write access at all.
This wasn't a problem in Access, because, though the main data was on the server, I linked to any needed tables and everything else was local to my box.
Thanx in advance for any help!
HI
If I create an Active Script like this :
function Main()
Dim fso
Dim a
Set fso = CreateObject("Scripting.FileSystemObject")
Set a = fso.CreateTextFile("c:est.txt")
a.WriteLine("This is a test")
a.Close
end Function
I got an error
ErrorCOde: 0
ErrorSource : VBScriptENgineError
Error Description : ActiveX Component can't create object : CreateObject
The error is at line Set fso=CreateObject....
Any idea ?
THanks
hi all,
i am working on a small "Biztalk" engine, by creating dynamic ssis packages that change according to the client source file definition.
in order to create a row-number to each row in my input file, i am tryng to add the Konesans's Row Number component to the dynamic package by using SSIS API, but i get a lot of errors. the component is not created as a"rowNumber" component, but as a General Managed Component, though i use the ComponentClassID as the classID in the RowNumber component.
has anybody try to do this ?
is there any way to get the row number other then this way?
any ideas?
thank you!!! for all your help until now!!
Hi
I wonder if it is possible to create a dataset in code and then feed it to a Reporting Svcs (RS) report and have it rendered on the data from this dataset.
My collegues does this with Crystal and it would break my heart if I cant do this with (RS)...
I have tried to find a solution but so far, no luck.
Anyone have any ideas?
/F
I can add more datasources and how can i frame my query based on the tables present in the various datasources?
View 5 Replies View RelatedI have some questions about creating SQL Server CE databases. Based on my experiments and what I've read on these forums, it looks like there are a couple ways to create a database schema. I can edit the database schema via the Server Explorer in Visual Studio, or use an external program like SQL Server Management Studio and somehow convert those files to .sdf files.
I find Visual Studio's built in tools to be cumbersome to use and limited in functionality, and using SSMS seems like a roundabout way of approaching the problem. I understand Microsoft will be releasing better tools with Orcas, but in the meantime, I'm wondering if there are alternative ways to generate database schemas.
For instance, I find Visual Studio's DataSet designer fairly easy to use. The DataSet designer generates schema definitions (.xsd files), and an instantiated DataSet can both read and write schema definitions via Read/WriteXMLSchema. Furthermore, DataAdapter's Fill and FillSchema methods can be used to push a schema from a database to a DataSet. So, can I somehow go the other direction and push a schema from a DataSet to a database? It seems like all the tools are there...
For example, if I create the DataSet schema, could I use a small app to create a new .sdf file, instantiate a DataSet, write the schema from the DataSet to the database, and then save the .sdf file? Or given the generated .xsd file, is there any way to create a SQL database from that?
Thanks in advance for any replies.
Hi,
I wonder if someone might be able to help me with scripting a script component. I'd like to include error redirection of rows within my script.
If the conversion of any of my inputs fail i'd like to catch the error and then just output all values to another output path. The output path will just take the input values without converting them from string data types and output them to an error table.
In the script i imagine i would use try catch statements and if it fails then set the output. I am not entirely sue as to how to go about switching between outputs though.
Any help on this matter would be greatfully recieved.
Cheers,
Grant
Hi,
Is it possible to populate a dataset with tables returned by a stored proc?Consider this:
BEGINSELECT * FROM Table1SELECT * FROM Table2SELECT * FROM Table3END
If that is my stored proc, could I call it from a page and automatically populate a dataset with all 3 tables (if yes, then how?), or would I have to make 3 seperate calls to the db for each table?
Thanks
Hi,
I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data.
I have seen the problem earlier and could not resolved it. Please let me know if any one can help.
Thanks in advance
Rohit
I'm looking for some advice on how to manage reports that use the same query in their datasets. I have multiple reports that use several datasets that are the same. If I need to make a change to one dataset, I need to remember to update the other datasets. Of course I don't always remember to do that!
Is there a way to create a dataset in a single location and then share it? I was thinking of using a View but I don't think it'll accept the parameters.
I've been cutting & pasting the entire query as I make change but I'm afraid it'll mess that up or forget to update a dataset.
How do you do it?
Any suggestions you have would be helpful.
Rob
I would like to know how to dynamically change the And/Or value on the Filter tab within a dataset.
Here are my two expressions within the filter:
Code Snippet
=Switch(Parameters!TotalCostFilterOp.Value = "=",Fields!tcost.Value = Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">",Fields!tcost.Value > Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">=",Fields!tcost.Value >= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "between",Fields!tcost.Value >= Parameters!TotalCost.Value AND Fields!tcost.Value <= Parameters!TotalCost2.Value,Parameters!TotalCostFilterOp.Value = "ie", ISNothing(Fields!tcost.Value), Parameters!TotalCostFilterOp.Value= "<",Fields!tcost.Value < Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<=",Fields!tcost.Value <= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<>",Fields!tcost.Value <> Parameters!TotalCost.Value)
Code Snippet
=Switch(Parameters!CompanyFilterOp.Value = "%",LCASE(Fields!company.Value) like "*" & LCASE(Parameters!Company.Value) & "*", Parameters!CompanyFilterOp.Value = "=",Fields!company.Value = Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "ie", ISNothing(Fields!company.Value), Parameters!CompanyFilterOp.Value = "?", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value), Parameters!CompanyFilterOp.Value = "<>", Fields!company.Value <> Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "?%", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value) & "*")
The operator for both these is set to "=", and the value is set to "=true".
Based on user selection in a report wizard, the 4th column (And/Or) may need to be changed from 'and' to 'or', or vice-versa.
Is there anyway to accomplish this programatically?
Thanks in advance for you help!
Hey everyone,
I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use?
thanks,
Keith
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies View RelatedI have OLE Db data source (SQL Server 2008) with 5 rows. One of the column in claimID. Another data source is IBM DB2 Iseries database. The table in DB2 has 93 million rows. I need to get only rows from DB2 table where ClaimID matces to OLE DB datasource dataset. In fact its just inner join but in two different serves. How do I create new dataset from these two tables in SSIS. I tried using Lookup transformation. I cannot use OLE DB as datasource for DB2.
View 5 Replies View RelatedCan someone please point me to one or more examples of a Source component that has two or more outputs?
I wrote a source, which works with a single output, but after adding a second output, I see no rows there. I've single-stepped the code in the debugger, and it looks like it should be adding rows to the second output, but the downstream component never sees any.
Thanks.