Updating A Permanent Table From A Dataset Within The Data Flow
Feb 14, 2007
I have a dataset that was created via a source + lookups + derived columns.
I wish to take this dataset and treat is as a table within a sql statement so that I can update a permanent table with the a specific value within the temp dataset.
In sql this is what I am trying to do:
SELECT COUNT(*) AS Count_of_Employees, DEPT
FROM Employees
GROUP BY DEPT
UPDATE Departments
SET Number_of_Employees = Count_of_Employees
WHERE Dept = dept.
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
I could use some help on how to save the result of my data-flow in an existing table.
I have created a data flow. I have no trouble storing the result of the flow in a new table. But I'm trying to store the result of my data flow in an existing table. (The flow involves fuzzy grouping, and i would like to store the result in the original table on which the fuzzy grouping was performed). But I cannot get it to work.
I can select the existing table as OLE DB destination. I can write an SQL to manipulate this table, but how do i address the result of the flow in the SQL?
I have a data flow task with a single source and destination task. I'm having the source task creating a table from a variable expression and the destination table also created from a variable expression. I'm running this under 3 scenarios in which each scenario has a different source and destination table. They are different in name but close in table structure with the exception of one column being different. The Metadata for the source flow path seems to be "sticky" in that it is not modifying the source table structure in the flow to account for this different column. I'm not sure how to adjust this. Any ideas? I've modified several properties in the task and data flow but nothing seems to make this adjustment in run-time.
Hi, I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions? SqlCommand snUpdate = conn.CreateCommand(); snUpdate.CommandType = CommandType.Text; snUpdate.CommandText = "Update TestTable set shipdate = @shipdate"; snUpdate.Parameters.Add("@shipdate", SqlDbType.Char, 10, "shipdate"); string jdate =""; for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++) { jdate = ds.Tables[0].Rows[i]["shipdate"].ToString(); ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate); } da.Update(ds, "Table1"); conn.Close();
Is possible to create a new permanent table by a procedure? I mean not a temporary table. If it is how do I do with the name? Because I would like put for each new table created as name as "forum1", "forum2", etc ... and not just numbers as I'll show you in the example. I tried to create the procedure, but I got stuck with permissions e primary key. create procedure new_forum
Insert into foruns (title,creation,country,[views],[description]) values (@title,getdate(),'England',0,@description)
CREATE TABLE [dbo].[@totalForuns]( [thread_id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [last_user_id] [int] NOT NULL, [title] [nvarchar](50) NOT NULL, [creation] [datetime] NOT NULL, [last_answer] [datetime] NOT NULL, [answers] [int] NOT NULL CONSTRAINT [DF_threads_answers] DEFAULT ((0)), [vizuais] [int] NOT NULL CONSTRAINT [DF_threads_views] DEFAULT ((0)), [fixed] [bit] NOT NULL CONSTRAINT [DF_threads_fixed] DEFAULT ((0)), [votos] [int] NOT NULL CONSTRAINT [DF_threads_votos] DEFAULT (''), CONSTRAINT [PK_threads] PRIMARY KEY CLUSTERED ( [@totalForuns] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[@totalForuns] WITH CHECK ADD CONSTRAINT [FK_threads_users] FOREIGN KEY([user_id]) REFERENCES [dbo].[users] ([user_id]) GO ALTER TABLE [dbo].[@totalForuns] CHECK CONSTRAINT [FK_threads_users]
I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server. Those procedures scheduled to run every 3 hours.
Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?
Hi, all experts here, Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows? I am looking forward to hearing from you and thank you very much in advance for your help. With best regards,
I have a series of data flow tasks that I want to output to a temp table. I've set the data source for RetainSameConnection and the Data Flows are DelayValidation. The OLE DB data source inside the Data Flow works fine, but the data destinations don't offer a # or ## as a target. I've tried every destination that sounds logical, without success.
I'm just testing an SSIS package and am having issues with dealing with locked records.
my situation is as follows:
my source table is oracle, my destination table is in SQL server. my data flow is a very simple update with a lookup transformation and then two OLEDB commands for update and insert.
On each of the OLEDB commands I have set the "command timeout" to 5 seconds (just for testing purposes). also each OLE DB command has a failure path that outputs to a flat file. I'm expecting that if the destination table/records is/are locked then after 5 seconds the record will be output to the flat file.
so to test this I begin a transaction on the destination table and don't commit it. then I start the SSIS job. it doesn't appear to even get to the OLE DB commands. it appears to stop at the beginning of the data flow task. the output window shows this:
"Information: 0x40043007 at Import from Phoenix, DTS.Pipeline: Pre-Execute phase is beginning."
but it just hangs there indefinately. the progress tab tells me that it get's from the validating stage and past the prepare for execute stage but hangs on pre-execute - 0 percent.
I've put the command timeout = 5 on everything that I can find. I've mucked arround with all the possible "validateExternalMetadata" properties even though I only guessed that it may be the cause. is there anything that I'm missing? where should I look next?
(yes it does work perfectly when there is no transaction locking the target table)
I am having the oddest problem. I can update the data in the dataset, but when I use the update command, or try to do a update manually it will not save the values back to the database.
tbl_strt_tmesTableAdapter.Update(lesson_plannerDataSet.tbl_strt_tmes);//this should save the values
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
the table adapter update, should send the values to the database, but for some reason, it is not. i can see the existing values from the database, along with my new values that i added in my loop, but those values are not being sent to the database when I invoke the update command on the data adapter.
Any help will be greatly appreciated. I am making this program for my wife who is a school teacher.
Hi All, I have a Problem while updating one table data from another table's data using sql server 2000. I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms) I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
Kind regards,
Wolfgang
Hello dear Forum,
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source.
I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source,
The SQL Looks like this:
select * from ccst_acctsys_account
where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >?
THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter.
Hi all, I'm trying to update various rows in my DB with a new value once an action occurs. I am using the following code which does not throw any exceptions, but also does not change the values. Can someone steer me in the right direction? public static void ChangeRefCode() { SqlConnection dbConn = new SqlConnection(ConnectDB.getConnectString()); SqlDataAdapter dataAdapt = new SqlDataAdapter("Select * from Posting",dbConn);
Hi,Ive got a class in my code which contains data from several tables. A user profile has a single record in a user_profile tables, for each of these records there is several records in a another table, which itself has several records in a tags table.So far the save method on the class saves the record to the user profile table, then loops through the next two tables info to insert each record. I want to decrease the number of trips to the database and am wondering if anyone can advise on the best way to do this.I am considering using a dataset to retrieve and store the records, instead of a datareader Im using currently. Then when Im done altering the data in the dataset (all three tables) I can commit all the changes in one database transaction?? I have been avoiding datasets due to the fact datareaders are apparently faster.My other option is to try make three calls to the database, one per table, updating my stored procedures to accept arrays of data using XML?? Can anyone tell me what is the best option for me?Thanks,C
I am using ASP.NET 2.0 WebForms and I was trying to use a DataSet to add rows programatically without adding the actual records to the MS SQL Server Databases. Is this possible or should I be doing this another way? DataSet myDS = new DataSet();DataTable myTable = new DataTable("table1");myTable.Columns.Add("col1", typeof(string));myDS.Tables.Add(myTable);myTable.Rows.Add("MyValue"); Thanks.
I have a code to update an access database from one of my dataset tables, but i want to insert columns manually
currently i am using this code to update my db
Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(dtadpt)
AccessConn.Open()
dtadpt.Update(DataSet, "recordsforupdate")
AccessConn.Close()
this code automatically inserts all the columns of dataset table to the access database table.
what i want is to inset the values for eg. in col4 of my dataset table into col5 of my access table, for that i want to manually use "inset into" statement to update the db table, but i am having problem with the syntax for using dataset..can anyone help please
I am relatively new to SQL and am having trouble with an update. I know I cannot use a join update and have to use subquery. I am not sure of the syntax on how to do this. Below is basically what I want to do, but cannot figure out how to do it with a subquery. Any help would be appreciated.
update revisedapps set alternateapp = t.alternateapp from tempalternateapps t join revisedapps r on t.appname = r.softwarename
I'm an sql/SSMSE novice, but have successfully connected to my sql database on a webserver.
My question is: Can I update the table data "directly" using SSMSE's interface, or do i have to run an sql update statement? (By updating table data directly, i mean: can i view the contents of a table, and simply edit the data in a field, like with in an MS Access table?) If I can update the data "directly," how does one do that?
-------   -----         ----         ---- Name    Add          No        RowID -------    -----                ----        -------
aa    #a-1,India                             10 bb    #a-1,India                     11                aa    #a-1,India                             12
 table 1 inserting to Table 2 (Using 1st Data flow)
Table 2:
-------   -----         ---- Name    Add         ID(Note:Here Identity1,1) -------    -----                ---- aa    #a-1,India                1 bb    #a-1,India                2 aa    #a-1,India                3
My Requirement is Update Table 1 set Column::No=Table 2.ID                                                                        based on Exact Match of                                                                         Table1.Name=Table2.Name and                                                                         Table1.Add=Table2.Add
It means Get back the Id for Source Table 1
 2nd Data flow        Source(Table1:Name, Add,No)                    |
  --LOOKUP(Table2:Name, Add::Matched Look Columns Name, Add and Tick Mark on ID)                         |(Match)
  -->OLEDB Command: update Table1 set N0=? where RowID=?(Here Param_0= NO ,Param_1=RowID)
Here My Issue is if Table 1 had Duplicates(same Name, Add, but Row Id is different it is Updating Same ID for Table 1.No It means Get Back ID correctly not updating Result::
Table 1:
-------   -----         ----         ---- Name    Add          No        RowID -------    -----                ----        ------- aa    #a-1,India               1             10 bb    #a-1,India              2       11                aa    #a-1,India              1 12
I have SSIS which will have OLF DB Source, and then have Derived Column component to managering all data from OLF DB Source. I used to have default columns such as Create Date, Update Date set as fixed date. Now we decided to put this default column values into a table to manage. I then have problem to choose which component I should use in order to have this columns selected from default table.
For example: if Create Date is null, I have to select default value from the default table; otherwise, use Create Date value and so on.
I have a data transform from a flat-file to a SQL server database. Some of the flat-file fields have NULL values. The SQL table I'm importing into does not allow NULL values in any field, but each field has a Default value specified.
I need to have it so that if a null value comes across in a field using the data transform, it takes the table default on import. I could of sworn I had this working a few days ago, but I get errors now that state I'm violating table constraints. Has anyone done this before?
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.
I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.
Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?
Also, how would I update the original source table where source.id = objRects.id?
Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.
Dear All! My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database. Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any Thanks
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task.
Is there a way to do that using an SQL statement as the source of the value in a data flow?
OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
I am trying to update a table in one database with data from a temporary table which i created in the tempdb.
I want to update field1 in the table with the tempfield1 from the #temp_table
The code looks something like this:
Use master UPDATE [dbname].dbo.table SET [dbname].dbo.table.field1 = [tempdb].dbo.#temp_table.tempfield1 WHERE ( [dbname].dbo.table.field2= [tempdb].dbo.#temp_table.tempfield2 AND [dbname].dbo.table.field3= [tempdb].dbo.#temp_table.tempfield3 AND [dbname].dbo.table.field4= [tempdb].dbo.#temp_table.tempfield4)
I get the following error: Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield2" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield3" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield4" could not be bound.
