Can A Script Handle Conditional Inserts To An OLDB Data Source?
Nov 20, 2007
I have a very simple SSIS task whcih opens a flat file data source, checks the first two characters of each row and if it is equal to "06", inserts that row into an OLDB destination table. However it is inserting EVERY row, even the ones not equal to "06" in first two columns. So, how do I get it to NOT insert the rows I don't want? Any help is very much appreciated and the following is my code:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
My source data is from a audit_trail(log) table. Because of the characteristic of audit table, more than one record of the data belongs to a record in another table (TableA). I am wondering what the most efficient way is to handle (Insert or update TableA based on data in audit_trail). Thanks.
I'm working on the viability of using SSIS to process data files that are (currently) sent by email or FTP and processed by hand. They are .csv or Excel and have some columns that are required, some that are included but the data is discarded, usually have some anomaly (like columns out of place, or columns missing) from source to source, and if they have headers the actual headers are inconsistent from source to source (and sometimes from submission to sumission ). Some sources send only one file, others might send a dozen. Due to the nature of the sources - many are not very technically inclined and sometimes there are other factors involved in how the data is exported - there's not a lot I can do to leverage them into all using the exact same format. Creating an SSIS package for each source (over 100 in all) is not a viable solution.
The compromise that I've come up with is I would like to come up with a universal SSIS package that can:
Take a .csv file with headers (I think we can force the sources to include the right headers if nothing else) as input , with or without quote delimiters. Not be sensitive to the order of the columns. Not be sensitive to the number of columns. Loop through multiple files from one source. Place the ouput in a staging table for further processing. Reading a .csv file, looping and the staging table is not a problem. What I'm struggling with is the middle portion. I've tried to make the column headers dynamic, but then I lose the ability to have quote delimiters. My next instinct was to the source file(s) and create one interim file with a different delimiter (such as pipe or ## or something) but that doesn't seem to work quite the way I want it - since the source has comma delimiters, the output has both the pipe and the comma. Another way I could do it is replace the commas within quote delimiters with a blank space in the data flow, which would eliminate the quote delimiters in the output. That I haven't tried yet.
Any suggestions? If I could bring down the hammer and tell every source "You must send it this way" I would, but my hands are tied.
Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks!
I need to know if there is a better way to construct this SQL statement.(Error handling is omitted)MS SQL Server 2000Insert into FSSUTmpSelect a.acct_no, a.ac_nm, a.ac_type, 10, -1,-1 * sum(CHARINDEX(convert(char(4), b.post_yr), @post_yr) * CHARINDEX('-',CONVERT(char(2), b.post_prd - @post_prd - 1)) * b.prd_trn_amt),-1 * sum(CHARINDEX(convert(char(4), b.post_yr), @post_yr) * CHARINDEX('0',CONVERT(char(1), b.post_prd)) * b.prd_trn_amt)FROM GLAccounts a, GLBalances bWHERE b.cmpny_cd = a.cmpny_cdAND b.acct_no = a.acct_noAND a.cmpny_cd = @cmpny_cdAND ac_ctrl_type between '200' and '219'Group by a.acct_no, a.ac_nm, a.ac_typeThe part I’m wondering about is the 2 sum sections.The GLBalances table has following important fields:Post_yr -- the posting yearPost_prd – the posting periodPrd_trn_amt – The beginning balances if the period is 0, or the nettransactions for periods 1 through 12.The first sum gives the current balance as of the period @post_prd by addingall of the periods from 0 to @post-prdThe second sum is just the beginning balance.It is doing a conditional sum by using CHARINDEX to be 0 if the recordshould not be added and 1 if it should.There is a problem as it stands when you are looking for the balances whenthe @post_prd is 9 or greater because “b.post_prd - @post_prd – 1” willbe –10 or smaller. Then the CONVERT(char(2) ….. is an error, so CHARINDEXis 0 when it needs to be 1.I can fix that by using SIGN and it will work fine. What I what to know, isthere a better way to populate the table, where one of the values is aconditional sum?This is a STORED PROCEDURE from a commercial product, so I can’t changeanything else other than the STORED PROCEDURE.
I'm encountering a very peculiar situation when I'm trying to compare source and target data using conditional split. Following is the Data Flow and how I'm trying to achieve this.
Source Data : Col_A (PK)Â Â Â Â Â Col_2 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 100 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 8 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 500 Target Data : Col_A (PK)Â Â Â Â Â Col_2 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 100 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 700 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 8Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 500 Look-up Target on Col_A to check for existing records. Now we have four columns in Look-up match output: Col_A, Col_B, Lkp_Col_A (Target Col), Lkp_Col_B (Target Col).
Conditional Split: Compare Col_B with Lkp_Col_B
Update target if there is any change in the existing value of Col_B.When I'm running the package for every record in source, the conditional split fails and even when there is no change in Col_B, some of the records (Not all and quite randomly) get updated with the same value. If I run the package for few records, it works absolutely fine.
I'm looking for a way to keep my database in Subversion so I can maintain it in source control.
Seems like the best way to do this would be to dump the database to a text script file. This works fine for creating the database structure and managing those change, however, that database structure is useless without the actual data in the tables.
Is there a good way to generate the inserts for the actual data? Why isn't this an option in the script wizard.
Also, does anyone have a better idea of source control of SQL Server databases in Subversion?
I'm working on a new project using SSIS in SQL Server 2005 and have an issue that I need resolved.
I'm loading an XML file into SSIS using the XML Source Adapter. This maps to an OLE DB destination which reads the data into a SQL table (please see below for the table structure). In between this stage is a data conversion to convert the Unicode characters from XML into non-Unicode characters.
I am running into an issue with the SSIS when I try to load a CSV file that contains double quotes wrapped around a field (CSV files have double quotes when field contains a comma; example: "Streams, Inc")
I have a scenario where we have to handle dynamically changing source columns.
For example , some times in the source files the number of columns will be increased or decreased, new columns can be added in the middle or in the end of the source file.
, Hi In this code how can I create a new data source and new data source view and model and structure that it run dynamic. In this code I have a lot of errors, that they are about server and database don€™t have in current code, In this code, first I should definition server or no?
How can I create data source and data source view and model and structure? Please say code of that, and guide me. databasename and srv is unknown. Do I add other reference with analysis services? Please explain about these codes: ************************************************************************ 1) RelationalDataSource dsNew = new RelationalDataSource( datasourceName, Utils.GetSyntacticallyValidID( datasourceName, typeof(RelationalDataSource)));
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
I need to insert multiple rows for input rows that meet certain conditions.
My input data is as follows.
ZIPCode, Plus 4, Range, City, State
What I need to happen is that if there is a value in the range column that is > 0 then I need to insert a row for every range item.
For instance say I have the following data.
54952, 1001, 10, Menasha, WI
What I need imported is :
54952, 1001, Menasha, WI 54952, 1002, Menasha, WI 54952, 1003, Menasha, WI 54952, 1004, Menasha, WI 54952, 1005, Menasha, WI 54952, 1006, Menasha, WI 54952, 1007, Menasha, WI 54952, 1008, Mensaha, WI 54952, 1009, Mensaha, WI 54952, 1010, Mensaha, WI 54952, 1011, Mensaha, WI
Any help in pointing me in the right direction would be great. Thanks for your help in advance.
This may be a loaded question, but I was wondering if anyone could outline the main options we have available to us for handling data access with ASP.net 2.0 / SQL Server 2005. I know at one extreme you can simply use the Data Controls like GridView, FormView, and DetailsView, specifying the necessary SQL on a page-by-page basis. At the other extreme, my understanding is that you can basically set up a SQLConnection object, explicitly specify your SQL (or call a stored procedure) and then execute these items from within the code. Can anyone either outline the main options or point me to a resource that better explains this? Thanks! Josh
Hi there, I'm having a problem where the data taken from the textboxes is being inserted into a table twice. I looked online and someone on another forum was experiencing the same problem. His solution was making the "submitdsabledcontrols" attribute of the form false. That hasn't fixed the issue for me. Below is my code. I'm still learning all this so this is just a test page, hence the wacky naming conventions. When the button click calls Button1_Click, all it runs is "SqlDataSource1.Insert()" Any ideas? <%@ Page EnableEventValidation="false" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default3.aspx.vb" Inherits="Default3" title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="mainContent" Runat="Server"> <form id="form1" submitdisabledcontrols=false> <br /> <asp:Label ID="Label1" runat="server">Name</asp:Label> <asp:TextBox ID="NameTextBox" runat="server"></asp:TextBox> <br /> <br /> <asp:Label ID="Label2" runat="server" Text="Label">Number</asp:Label> <asp:TextBox ID="NumberTextBox" runat="server"></asp:TextBox> <br /> <br /> <asp:Label ID="Label3" runat="server" Text="Label">Salary</asp:Label> <asp:TextBox ID="SalaryTextBox" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_click" /> </form> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString1 %>" DeleteCommand="DELETE FROM [Table1] WHERE [TableID] = @original_TableID" InsertCommand="INSERT INTO [Table1] ([Name], [Number], [Salary]) VALUES (@Name, @Number, @Salary)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Table1]" UpdateCommand="UPDATE [Table1] SET [Name] = @Name, [Number] = @Number, [Salary] = @Salary WHERE [TableID] = @original_TableID"> <DeleteParameters> <asp:Parameter Name="original_TableID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Number" Type="String" /> <asp:Parameter Name="Salary" Type="Decimal" /> <asp:Parameter Name="original_TableID" Type="Int32" /> </UpdateParameters>
I have a VERY simple program, it contains a datagrid, textbox and button I want it so when I insert something into the textbox and press the button it puts it into the database that is connected to the datagrid and displays it I have the following code and I cannot get it to work: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) SqlDataSource1.InsertParameters.Add("@Name", TextBox1.Text) End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head> <body><form id="Form1" action="Default.aspx" runat="server"> <table> <tr> <td> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>"DeleteCommand="DELETE FROM [Table] WHERE [ID] = @ID" InsertCommand="INSERT INTO [Table] ([Name]) VALUES (@Name)" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString1.ProviderName %>"SelectCommand="SELECT [ID], [Name] FROM [Table]" UpdateCommand="UPDATE [Table] SET [Name] = @Name WHERE [ID] = @ID"> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> </td> </tr> <tr> <td align="center"> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> </td> <td> <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" /> </td></tr> </table></form> </body> </html>
I have data in an old database I would like to capture for my new system but I dont have the original insert scripts. Is there a tool (in SQL Server 2000 or thirdparty) that will help me export the data as SQL inserts?
hi everybody, i want to create data source and data source view for data mining, with using C Sharp. i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully, and have i build data source and data source view, how to do it.?
Today I was making a few reports. When I tested the reports in Visual Studio, they worked great: I got the expected result. But when I deployed the reports to our reportserver the problem started. When I click on the directory in which my reports are deployed, I got my 4 reports. Till now everything worked correct. But when I click on a report to view the results it went wrong. I got an error: "Cannot create a connection to data source 'Live'" (Live is the name of our data source).
We are using the Windows Logons and I am sure that I have all the rights on the server, I gave myself 'sysadmin' rights, so it should work. I also have tried it with all the roles assigned on my account, but then it still won't work.
When I modify the data source, and set it to another server en database it works. The datasource 'Live' exists on a x64 MsSQL server, en the other datasource is on a x86 MsSQL server. Maybe that is the problem?
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
I am using SP as my data set to generate report and there are some multi-value parameters which past as an array back to sql server. Since sql server can not handle the array pass in , how to handle it?
I am having a requirement where I need to load the correct data into the target table and needs to save the bad data for analysis, how can I do that in SSIS.
m inserting some data in big-sized field and small-sized fields, data of varchar type, int's, dateTime, and others... i am using something called a stored procedure to add data into a table.. now when i execute the stored procedure my data gets truncated (although i made the sizes for my fields ridiculously big like 1000 just in case) for example if i want to enter Jasmine it only inserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I am guessing it's a problem of using single vs. double quotes and stuff like that within my insert statement... the following is my stored procedure:
CREATE procedure addLabor @lName varchar, @fName varchar, @mName varchar, @title varchar, @craft varchar, @lastFour varchar, @SSN varchar, @dateOfHire varchar, @currentProj varchar, @status tinyInt, @project_id int, @updateBy varchar, @updateDate varchar, @address varchar, @email varchar, @phone varchar, @zip varchar, @myfeedBack varchar, @ethnicity varchar, @userID int as BEGIN SET NOCOUNT OFF DECLARE @newid INT insert into laborPersonal ( lName, fName, mName, title, craft, lastFour, SSN, dateOfHire, currentProj, status, project_id, updateBy,
When i use 2 single quotes it insert the following string: "@lName" not the actual value of the variable @lName my exec statement is this: EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman', '1234', 'keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==', '6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/2005 2:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345', '123-12-1234', 'African American', '3'
Hello all,I just started a new job this week and they complain about the length oftime it takes to load data into their data warehouse,which they do once a month.From what I can gather, they rebuild the indexes before the insert with an80% Fillfactor, then insert the data (with theindexes enabled), then rebuild the indexes with a 100% Fillfactor.Most of my RDBMS experience is with a different product. We would havedisabled the indexes and Foreign Keys, loaded the data, thenre-enabled them, moving any records that violated the constraints into anappropriate audit table to be checked after.Can someone share with me what the accepted "best practices" are for loadingdata efficiently into a data warehouse?Any thoughts would be deeply appreciated.Steve
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData ( Id BIGINT, --is actually an identity column Manifest_Id BIGINT, Doc_Num BIGINT, Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X) FROM #InvLogData GROUP BY X HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
I am trying to insert a large amount of data from a web form I created into a "text" field in my DB. Problem is its oly entering the 1st 16 characters. I can see why but I dont understand what I need to do to avoid this shortcomming. Ive searched the forums here and in google groups to no avail :( <code> sqlCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Content", System.Data.SqlDbType.Text, 16, "Content")).Value = ftbContent.Text </code> How do I need to restructure the above line? Any help would be appreciated. TIA,Stue
In my trivial example below I have a Nationality entity. The entity just has the code and Name attributes, with the code being an automatically generated Integer. I am inserting one record and then trying to prevent the same record being inserted, by setting the ImportType to 1:
After this I truncate the staging table and repeat the process. Unfortunately a new record is entered into the Entity even though the name is identical to a record that already exists.
basically, is it inefficient to open and close a data connection everytime data needs to be retrieved or is there a way for a user to use the same conenction over multiple pages orfor multiple users to share one global data connection I just wanted to know this before I built a site where data could be constantly being pulled or put into the database.It would be easier to just keep opening and closing the connection since it just means pasting in the small chunk of code I use to do that where I need it. I hear speak of connections sometimes not closing etc and wonder if that is an issue then if you are opening and closing too many of them. I am using SqlConnection SqlCommand and SqlDatareader , my code works, just wondering if my approach lacked scalability before I find out too late and have to rewrite everything . Jim
