I'm using a script task to split my control flow before performing different data flow tasks depending upon the result of the script.
The dataflow tasks populate a single dataset with data before entering a foreach loop where the data in the dataset is consumed.
The foreach loop is valid for both dataflow tasks but I can€™t get it to accept multiple inputs.
I could have 2 copies of the foreach loop, one for each branch, but this seems wasteful and also means I have to maintain the same chunk of code in 2 places.
Can I join control flows or is there a better way to achieve this?
When I'm designing a merge join in BIDs, in particular a large one with say a couple hundred columns on the left part of the join (and only 1-2 columns on the right side), it takes 2-3 minutes to close and I notice it pegs the CPU on the computer during that time. I'm developing on a quad dual core 64 bit machine with 32 Gb of RAM.
What makes this problematic is if you have a several merge joins in the pipe, and you do something like add a column. Then you have to re-open every merge join to include that column, waiting 2-3 minutes for it to close. So adding a column to a pipe with 10 merger joins will take 20-30 minutes, most of which is just waiting for edit boxes to close. I've tried the options of "work offline" and setting the ValidateExternalData to false in the merge join property but those don't help. Anyone encounter this problem?
So I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.
I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.
Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.
What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.
Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one? 'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.
Easy: read a SQL table with 500 fields, transform, write to flat file using SSIS.
But, I have hundreds of transformations to define using Lookups and Aggregates, Derived Column transformations. I wan to group the data flow transformations in usable (reasonable size) groups (packages, containers, subroutines, whatever you want to call it).
I cannot figure out a simple easy way of doing this most "simple" obvious thing.
Am I the only one on the planet who needs to do this?
Good Afternoon, I have a package with reads data from a text file and persist that data in a table "X" on sql server. I have one dataflow wich does this operation. In the sequence, i have this dataflow connected to the next dataflow, wich read the data from the table "X" and do some joins with another tables and persist that new data on a table "Y". The first problem, the table "Y" have a foreign key to table "X" and i have to do this operation with transaction, the package give problem, it's something like "Cannot enlist this connection on distributed transaction...", but i have many packages, wich I exec with transaction and runs sucessfully. If i turn off the transaction, runs perfectly, when i put the transaction,it fails.
I've 6 data flow tasks in my package. I need to put all of these dataflows into a transaction and rollback if any one of the task fails. I dont want to use MSDTC.
Perhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.
Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.
What's the recommend method to do this now - since row level processing of variables seem to be a no-no?
I have a package that has several data flows that run concurrently after some initial tasks and an initial data flow. I want transactions on each of the data flows and have set the transaction option to Required on the data flows (not on the package itself). I am also using checkpoint restart on the package. A couple things are happening.
1) the first data flow is successful and that releases the several that are waiting. Some of these complete OK but inevitably one or two will fail. The failing data flows will be different from run to run, sometimes one and sometimes two will fail. The error says:
Error: 0xC0202009 at Provider_NF_Code, Delete Provider_NF_Code [130]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
My hunch is that DTC is getting the transactions mixed up. I think it is committing one just after another data flow has already started work expecting the transaction to still be active. That would explain why the failing data flows are random. Plus, if I set the MaxConcurrentExecutables to 1 the entire package is successful. BUT, why have concurrent tasks if you can't run them concurrently.
2) when the package fails with the DTC problem I restart it with the checkpoint file. I was expecting the package to restart with the failed data flows. Instead, it restarts with the initial data flow (that all the other flows wait for in the package). This data flow has always been successful. It's as if the transactions I have put on the individual data flows are actually placed on a single virtual container that all of them are in, and when down stream data flows fail the entire data flow chain is rolled back and set to restart.
How can I get multiple concurrent data flows to run with transactions?
Why are successful data flows being restarted? Can I get just the failed tasks to restart?
We receive data files from different external customers, and these files have identical layouts.
I'm planning to set up a package for each customer. Each package will contain a flat file source -> OLEDB transformation dataflow, (followed by other customer-specific data flows).
What I'd like to do is just create this dataflow once, parameterising the flat file and table names. Is it possible to include this dataflow in each customer package so that if the flat file layout changes, I can just modify the connection managers in the one place, and then recompile each package to pick up the changes?
I'm stuck here when I try to open an existing package which contains several data flows, SSIS tries to validate each data flow and after a while a Visual Studio error message pops up and I can't do anything.
The error message says : "Unable to cast COM object of type 'System._ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject90'. This operation failed because the QueryInterface call on Com component for the interface with IID '...GUID...' failed due to the following error : The application called an interface that was marshalled for a different thread. (Exception from HResult: RPC_E_WRONG_THREAD)"
So I have to make a fairly dynamic Data flow. I will get the most of the configuration from a database table. I will look up the name of the procedure to run as a source (I can use expressions or a script component source for this), I will lookup columns names from a database table.I can use expressions (maybe) or a destination script component for the destination including the destination table name and column names, these will be looked up in a database table.What I am not sure is how I will do the mapping. How can I make this dynamic? The logic for mapping will be in the database as well. Could I create a custom dataflow all in one script? A source, destination and mappings all in one script? Is there an example of this out there.my task ios to make the data flow completely dynamic.all config info would be kept in a SQL Server database.A complete custom script component dataflow task.
I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:
Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:Temp.txt".
Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.
I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?
I have a package with 10 synchronous dataflows, which, combined, load about 300MB of flat file data to a database. This package would run successfully on 2 of our database servers, but would regularly fail on a third. The server on which it was failing is a 4 processor box with 16GB Ram with Windows Server 2003, SQL 2005, SSIS and SSRS installed - much more robust than one of the others that the package worked on. The SSIS error messages returned alternated between the following (with no apparent reason why one would show up rather than another, though the first was the most common):
"The file name "\Server1Folder1File1.txt" specified in the connection was not valid."
"The file name property is not valid. The file name is a device or contains invalid characters."
"An error occurred while initializing the flat file parser."
For the first error message, the error would report different connection managers and their associated file as invalid from run to run. All of the files across the 10 dataflows resided in the same network folder, and the package would read in and process a few of them before failing, so the problem was definitely not the connection string.
Searching the forums, etc. for these errors provided no useful information - given the real cause of the problem, these error messages are worse than unhelpful, they send you looking in the wrong direction. It was only when trying to track down another problem on the same server that I discovered the issue. When trying to copy database backups greater than 12GB over the network to this server, the operation would fail with an "Insufficient System Resources" message.
Some research led to the discovery that problem was caused by the /3GB switch in the boot.ini file of the server (don't let your Server team use that switch if you have 16GB of memory or more). Removing the switch and setting SQL to utilize AWE, fixed both the file copy problem AND the SSIS package failure problem. The SSIS package failed, not due to a bad connection string, but rather to insufficient server resources (read memory) to handle the simultaneous connections.
I hope this may help any others trying to track down this kind of SSIS package failure.
I will also provide here what I have gleaned about setting up Memory usage for SQL Server 2005 running on 32 bit Windows Server 2003 (with the caveat that I am no expert €“ corrections and additional information are welcome).
The following links got me started in my research (thanks to the folks who provided such useful information): http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55191 http://articles.techrepublic.com.com/5100-10878_11-6091280.html http://www.simple-talk.com/community/blogs/brian_donahue/archive/2007/09/30/37747.aspx http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx http://www.modhul.com/2007/11/10/optimising-system-memory-for-sql-server-part-i/
Also, search BOL for: Server Memory Options Enabling Memory Support for Over 4 GB of Physical Memory Enabling AWE Memory for SQL Server
Windows Server 2003 provides access to 4GB of virtual address space. By default, 2GB is assigned to the OS and 2GB to applications. This default can be change to 1GB for the OS and 3GB for applications by the use of the /3GB switch in the boot.ini file.
Physical memory over 4GB can be addressed by enabling Physical Addressing Extensions (PAE), which is done by setting the /PAE switch in the boot.ini file. This does not increase the systems virtual address space, rather it increases the size of the page table (which is maintained within the virtual address space), adding entries to reference the physical memory above 4GB.
It is important to note that these two switches are not interdependent (they do different things and you can turn each on or off regardless of the others status), though the combination of them has an impact on server performance and the maximum amount of physical memory which can be addressed.
The /3GB switch only impacts the allocation of the first 4GB of memory (virtual address space) between the OS and applications (default 50/50 % split, with switch on - 25% OS and 75% applications). The /PAE switch enables the system to reference/manage physical memory above 4GB, but does not alter the allocation percentages of the first 4GB of memory between the OS and applications. However, when PAE is enabled, the OS requires more memory within the first 4GB to manage the physical memory above 4GB (due to increased page table entries). With the /3GB switch, the OS has only 1GB of virtual address space, and only enough space to manage a total of 16GB of physical memory. If 32GB of physical memory is installed, 16GB of it will go to waste.
Address Windowing Extensions (AWE) is an API that allows an application to address more than the 2-3GB of memory that is available to applications within the virtual address space (first 4GB of memory). SQL Server can utilize AWE to take advantage of memory above the first 4GB that is made available via PAE, and can even reserve portions for its own use. I believe (though I can€™t remember where I got this bit) that SQL utilizes AWE memory only for the page cache (buffer pool €“ which seems to be a misnomer), and not for other operations.
To enable AWE, see the BOL references above.
The big question: what are the recommended settings for all of these? That all depends on what you have running on the server. You need to leave space for the OS, SQL Server and any other applications you have.
The hard and fast rules: If you have more than 4GB of RAM, you must use the /PAE switch in order to take advantage of it. If you have more than 16GB of RAM, you must NOT use the /3GB switch in order to take advantage of it.
Based on anecdotal evidence, I€™ve noticed the following generally recommended guidelines €“ assuming the server is dedicated to SQL.
Use of the /3GB switch seems to be a generally accepted practice if you have 8GB of RAM or less. For between 8 and 16GB, some say never use the /3GB switch, others say you can use it up to 12GB and still others up to 16GB. I interpret this to mean that it all depends on what types of loads are being placed on the server and that testing on individual servers will be required to determine whether or not to use the switch. Certainly that was my experience - the /3GB switch worked fine with 16GB RAM, until the server encountered a certain workload. For me, no more /3GB switch.
For setting SQL to use AWE, most seem to agree that it should be enabled if you have more than 4GB RAM. The setting of max server memory is more complicated. BOL seems to suggest (the €˜Server Memory Options€™ entry) a formula of Total Physical Memory minus 1-2GB for the operating system. Based on a desire to be a bit more conservative, I am now using the following formula:
max server memory = total physical memory
minus
4GB for the OS and application processes (since the AWE memory is utilized for page cache, not SQL processes)
minus
AWE memory required by other applications, including other instance of SQL Server
If anyone has additional insight, or a more refined equation, I could certainly benefit from it.
Hi All, I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
UPDATE #2: When it said "Do you want to install Microsoft SQL Server" I said "yes" and that caused it to work. I exited and re-ran and now the print runs w/o the "install SQL Server" (If the prompt had said "Do you want to install the print dialog" we wouldn't be having this discussion...)
UPDATE: After posting this i discovered that the same thing occurs when attempting to print the report direct from IE6: First a dialog pops up "Do you want to install this software?" Name: Microsoft SQL Server. When I click "Don't Install" I get the dialog "unable to load client print control." Since this happens direct from IE6 I suspect it's browser settings. I'll resume tomorrow and post a followup.
My WinForm C# app integrates Reporting Services by calling them from WebBrowser controls. The problem is attempts to print cause a dialog: "unable to load client print control."
I've read prior posts that say "enable Active-X in your browser" - I don't know how to do that from a WebBrowser control.
Any ideas how to support Reporting Services "Print" from within a WebBrowser control?
Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file. Let's explain a little more:
I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.
Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.
What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?
Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows? Thanks for your help!
I have a data flow task which has around 5 data flows (like the 2nd diagram shown here). These 5 simple flows with just a row count transformation in between. Now, I want to fail the entire task immediately even if one of the data flows failed. Right now if one flow fails the remaining flows fails after a long time, not immediately. How can I make it fails immediately.
The other I would like to do is Can I place these 5 data flows in a transaction, so that if one data flow fails, others data flows also roll backs? ( I assume its not possible)
I have database on SQL Server 2000 set up with a merge publication.This publication is configured with a number of dynamic filters toreduce the amount of data sent to each client. Each client has ananonymous pull subscription. The merge process can be triggered by thewindows sync manager and my application.To improve performance I have created some helper tables to hold themapping between user login and primary keys of selected entities.For the replicated data to be correct the contents of the helper tablesneeds to be up to date.I need to fire off a stored procedure on the publisher beforereplication starts to verify that this data is up to date. I can notsee any documented way of doing this however I have been experimentingwith some unorthodox systems.Firstly has anyone any ideas?I have been considering adding a trigger to some of the tables used bythe Microsoft replication code - yes I know this is very nasty.My problems arise because executing this stored procedure will causesome data to be updated. In updating data we could create a newgeneration in the database. I must therefore run my stored procedurebefore any the Microsoft code makes any generation checks / updates.Anyone done anything similar, Anyone have any better ideas?Any comments would be gratefully received.
I'm using merge replication to maintain a backup copy of my main (publisher)MSDE database. A push subscription periodically (1 per minute) updates the backup DB. It's intended that if the main db goes down then the backup (subscription) db can be configured as a publisher. This must all be performed via scripting. The initial configuration of the main publisher and subscription is controlled via scripting, which works fine. The problems occur when I try to configure the subsciber to become a publisher. A script is executed on the subscriber but fails at the point when it's configuring the publisher detail. The error is something like "unable to configure a publication for a database setup as an anonymous subscription". I'm guessing that there are subscritpion artifacts added to the database which need to be removed before it can be configured as a new publisher.
My development environment includes Visual Web Developer, SQL Server 2005 Express and SQL Server Management Studio Express. I have a .sql control string that creates a database. The control string is in my App_Data folder within my web site. When I use SQL Server Managment Studio Express to run the .sql control string and create the tables etc. it does it in my SQLEXPRESS folder and not in the web. How do I run the .sql file so it will create the database in my App_Data folder within the web site? Thanks, Kyleq
I wanna know how to retrieve & insert an image from/to a Sql Server database. using image control or any other control I’ve done that with windows form picture box. As that was explained in MS tutorials Code…. -------------------------------------------Dim ms As New MemoryStreamPictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)Dim arrImage() As Byte = ms.GetBuffer ms.Close() Dim strSQL As String = _ "INSERT INTO Emp (EmpName,EmpSalary,Picture)" & _ "VALUES (@EmpName,@EmpSalary,@Picture)" Dim cmd As New SqlCommand(strSQL, ConnEmp) With cmd .Parameters.Add(New SqlParameter("@Picture", _ SqlDbType.Image)).Value = arrImage .Parameters.Add(New SqlParameter("@EmpName", _ SqlDbType.NVarChar)).Value = txtEmpName.Text .Parameters.Add(New SqlParameter("@EmpSalary", _ SqlDbType.Decimal)).Value = txtEmpSalary.Text End With cmd.ExecuteNonQuery() ------------------------------------------- But with a web form’s image control I DO NOT know how to do it I real appreciate your help Thank you
Hi,i have a question, i have a VS2005 and a SQL server 2000 enterprise in my office that i used for web developement,iam new to this, i made a data driven site using some automated controls available in the toolbox, but i need to made some manual process, like i need to make a LABEL show the SUM of specific number fields from my data base, they say i have to make a OLEDB connection using VB, but i don't know how,can you tell me how?Thanks
hi friends, I created sqldatasource control. In select command i written the query like this "select * form emp" and bounded in grid.How can I change the query for searching the details according the date wise when i click the search button.
Hello experts i have a SqlDataSource Control on my web form. Here is the source for the control <asp:SqlDataSource ID="sqlSearchDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DbefomsConnectionString %>" SelectCommand="SELECT [icon], [file_name], [path] FROM [tblfile] WHERE ([file_name] = @file_name)"> <SelectParameters> <asp:ControlParameter ControlID="txtSearch" DefaultValue="0" Name="file_name" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource> This control displays icon, file_name, path in GridView Control The problem is How can i store the above field values in an asp.net variable. Thanks Regards Ali
Hi! I'm building a web application with ASP.NET, and using MS SQL 2000 for my database server. How should I do to guarantee the integrity of the data in spite of the concurrent access? Meaning... how can I make sure that more than 1 user can update 1 table at the same time, while no error will occur? Do I need to add some codes at my aspx file? Or do I need to do something to my database? Or do I not have to worry about it? Thank you.
If i use the TimeTracker app from the starter kit suite and I enter my SQL connection in the web.config file it works fine,
If i build a simple app with just a sqladapter, sqlconnection and dataset control then enter in the page_load procedure
sqlDataAdater1.fill(dataset11) datagrid1.databind() It keeps giving my errors see below: I created a connection with the sqlconnection control and used the same userid and password I used in the time tracker stater kit's web config file which works - I have no idea what is going on
*** is it better to create the sqlconnection control first then create the sqladapter or vise versa. Server Error in '/WebApplication2' Application.
Login failed for user 'IssueTrackerUser'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'IssueTrackerUser'.Source Error:
Line 55: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Line 56: 'Put user code to initialize the page here Line 57: SqlDataAdapter1.Fill(DataSet11) Line 58: DataGrid1.DataBind() Line 59: Source File: c:inetpubwwwrootWebApplication2WebForm1.aspx.vb Line: 57 Stack Trace:
Do you have to use the sqldatasource in conjuction with another control like gridview or dropdown list? I'd like to check to see if a record exists in one table before inserting data into another.
Hello.I'm new to ASP.NET and trying to write data to a Microsft SQL Server. I have created a SqlDataSource control, which is 'connected' to my SQL server.Now my question is how i can put data in the database throw the control, and read data from it. I've read the site, but it's still unclear for me..Greetings!
