|
I have a data source that returns rows to me. If any row is already present in the destination database (based on the primary key value)it should get updated with the content of the data source row.
Rows that are not present in the destination database should get inserted.
Can u pls tell me how to achieve this in a SSIS package.
Hariom
|
|
|
|
|
Hello there,
I have a huge spreadsheet which I have to load into SQL server every month for reporting purposes.
The structure of this sheet is totally different from the SQL server table, otherwise I would have simply used a datareader to load the data fron the spreadsheet and 'bulk copy' it into the server.
Ther procedure I'm currently following is that I create a user table (dataset) in memory similar to the SQL table structure, and then use Table.addRow method to populate this dataset from the Excel sheet. There are around 180,000 rows in each spreadsheet, but filling the data set does not take more than 15-20 seconds max.
My real problem is when I save this table to my server, it take ages because it has to check the status of each row (new, modified, deleted,...obvoiusly they are all new in my case) before inserting it to the SQL table.
If I apply 'AcceptChanges' on the dataset, nothing gets saved, which is understandable.
Unless you have a better solution, I have two options in mind:
1) Read the user table in a datareader and use 'BulkCopy' to copy the entire data in one go, but I'm not sure if a datareader can read a dataset? If it's possible, then this will definitely solve the problem.
2) The other alternative is to save the dataset into a temp XML file and then use a DTS package to import the XML file into the server.
Your help and advice are greatly appreciated,
Hani
|
|
|
|
|
You can open the excel sheet as if it were a database table (either in a dataset or a datareader) using the System.Data.Oledb classes and the appropriate connection string.
This article[^] has most of the necessary details.
|
|
|
|
|
Thanks Rob for your advice and quick reply.
I'm sorry if I didn't explain my case well.
I'm alraedy accessing the Xls file with a datareader, using OleDB reader. The problem is after opening the Xls file and then populating it in a dataset in a different order from the spreadsheet, I then have to copy the dataset into my SQL server.
As I mentioned earlier, there are around 180,000 rows and the dataset, and this takes long time for copying into the server, since the dataadapter has to check the rows one by one to see if it is new, modifie, deleted or unchanged, and accordingle will issue the respective command: Insert, update, delete, or ignore.
What I'm looking for is a way for bulkcopying the 180,000 rows into the server in one go.
My problem would be solved if I can find a way to populate the 180,000 rows in a data container that can be later retrieved by a datareader and use the bulkcopy command of the datareader. Is that possible.
Thanks again for your support.
|
|
|
|
|
In my library I have a routine that takes a DataReader, cobbles up the appropriate Insert statement, and passes the Command and the DataReader to a wrapper for ExecuteNonQuery.
public virtual ulong
InsertData
(
string TableName
,
PIEBALD.Data.DataReader TheData
)
{
System.Text.StringBuilder columns = new System.Text.StringBuilder() ;
System.Text.StringBuilder values = new System.Text.StringBuilder() ;
string comma = "(" ;
for ( int runner = 0 ; runner < TheData.FieldCount ; runner++ )
{
columns.Append ( comma + this.WrapName ( TheData.GetFieldName ( runner ) ) ) ;
values.Append ( comma + "@" + TheData.GetFieldName ( runner ) ) ;
comma = "," ;
}
return ( ExecuteNonQuery
(
"INSERT INTO " + this.WrapName ( TableName ) + columns.ToString() + ") values" + values.ToString() + ")"
,
TheData
) ) ;
}
|
|
|
|
|
I have a large file of 29,000 student pictures that are stored by studuent number (stu_num). I am trying to post a report on the internet that is password protected, includes all pertenint data and a picture of the student for teachers, administratrs etc. How do I do this. I have read and searched the internet twice over and can't seem to get an answer that doesn't require me to enter one student at a time.
Thank you fro any help you can give me/us.
RevCuevas
reply to cuevasj@gm.sbac.edu
|
|
|
|
|
You've searched the whole Internet twice over? Blimey, that's pretty impressive...
Have a look at this article[^]. It explains how to use the ASP.NET GridView control to display repeating text and image data.
Paul Marfleet
|
|
|
|
|
Hi.
How can I get script from my DB in SQL Server?
(a script for run it on another computer to generate my tables, views, relations, stored procedures and ...)
Best wishes
|
|
|
|
|
If you have Management Studio, right-click the database node in Object Explorer and select Tasks -> Generate Scripts. Follow the wizard instructions.
Paul Marfleet
|
|
|
|
|
Hi all
I've got a problem that's driving me a bit mad, so any help gratefully received!
I've got a Data Transformation Task that is calling a parameterised stored procedure:
exec [myDB].[dbo].[vlGetOutputFile] 'Sep 28 2007', 6, 86
Within that stored procedure, I'm creating and dropping a couple of temporary tables and then returning the result set. If I click preview, I get the first 200 lines I'd expect, but as soon as I go to transformations I get the error 'Invalid object name #temp' and no columns to select from.
What am I doing wrong?
Thanks in advance
Ben
|
|
|
|
|
Presumably SQL Server.
The temp tables are created in the stored proc correct?
If so why are you dropping them?
|
|
|
|
|
Yes, SQL Server.
Fair point, just my natural tidiness 
Anyway, dropping the drops doesn't solve the issue....
Cheers
Ben
|
|
|
|
|
The reason you are getting this error is because you cannot use temp tables with DTS packages.
This is because DTS packages cannot resolve temp tables in design time.
This is a 'feature' of SQL Server 2000.
My advice is not to use temp tables with the DTS package.
What I do is pipe the result of the stored procedure to a table then call the DTS package from within a stored procedure (using the freshly populated table in the DTS package).
You can then drop the table or delete the rows once the DTS package has been run.
Alternatively if you really want to use temp tables check out this link where I explain how to get around this problem.
The solution is not pretty.
Basically my advice is to avoid temp tables with DTS packages - this is a documented 'feature'.
I hope this helps.
-- modified at 16:32 Tuesday 23rd October, 2007
-- modified at 16:33 Tuesday 23rd October, 2007
You always pass failure on the way to success.
|
|
|
|
|
Thanks for the pointer. Seems most odd that that this isn't supported - is there a reason they opted not to allow this?
Cheers
Ben
|
|
|
|
|
Bjohnson33 wrote: Seems most odd that that this isn't supported
Yes this is what we call a 'bug' and microsoft calls a 'feature'
What I tend to do nowadays is always pipe the results into a table then run the DTS from one table.
This has a tendency to simplify things a lot as processes, including file copying etc, are visible within the stored procedure and not hidden within the DTS package (I avoid the DTS vb scripting feature as although it looks good - try debugging a DTS package with all that code hidden away in various places).
You always pass failure on the way to success.
|
|
|
|
|
How can we reset the start of an identity column.
|
|
|
|
|
Use DBCC CHECKIDENT (tablename, RESEED, 9999). This would reset the next identity to 10000 on the table in question.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Use DatabaseName
DBCC CHECKIDENT (tablename, RESEED, 0)
OutPut : reset the column identity next value start to 1
Mitesh Darji
|
|
|
|
|
Hi there,
Well it seems I have stumbled upon a rather basic problem and I just cant seem to work it out. I have written a simple update query as follows:
<br />
private readonly string ACCESS_CUSTOMER_UPDATE = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID";<br />
I have used the approach to execute the query in the method listed below. For the life of me I cant seem to work out why the update wont be taking place. I have executed the query listed above in Access without any problems but when encapsulated in the method below it just wont update.
I'd appreciate if someone could point out the mistake I have overlooked.
Thanks
<br />
private string PARAM_ID = "@ID";<br />
private string PARAM_NAME = "@Name";<br />
private string PARAM_AGE = "@Age";<br />
<br />
private string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Temp\\temp.mdb";<br />
<br />
private OleDbConnection connection = new OleDbConnection(connectionstring);<br />
<br />
public void UpdateCustomer(CustomerInfo customer)<br />
{<br />
OleDbParameter[] parameters = new OleDbParameter[]{<br />
new OleDbParameter(PARAM_ID,OleDbType.Integer),<br />
new OleDbParameter(PARAM_NAME,OleDbType.VarChar),<br />
new OleDbParameter(PARAM_AGE,OleDbType.Integer)};<br />
<br />
parameters[0].Value = customer.ID;<br />
parameters[1].Value = customer.Name;<br />
parameters[2].Value = customer.Age;<br />
<br />
OleDbCommand command = new OleDbCommand();<br />
command.Connection = this.connection;<br />
command.Connection.Open();<br />
command.CommandText = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID";<br />
<br />
for (int i = 0; i < parameters.Length; i++)<br />
{<br />
OleDbParameter parm = (OleDbParameter)parameters[i];<br />
command.Parameters.Add(parm);<br />
}<br />
<br />
try<br />
{<br />
int x = command.ExecuteNonQuery();<br />
Debug.WriteLine(x.ToString());<br />
}<br />
catch (Exception e)<br />
{<br />
throw new Exception(e.Message, e.InnerException);<br />
}<br />
finally<br />
{<br />
command.Connection.Close();<br />
command.Dispose();<br />
}<br />
}<br />
edit:
Added the connection string.
|
|
|
|
|
Can you also write the error which you are getting?
Arun Singh
Noida.
|
|
|
|
|
I think that you're going to find that the order of the parameters going into your oledbcommand for access is important...
|
|
|
|
|
I am getting no error. The output of ExecuteNonQuery is an "int" type. The value of this int variable indicates how many rows have been modified. In my case its zero when infact it should be one. I have one row of data in the table that needs to be updated so it should return 1 not 0.
I can verify that order of parameters matches what is in the table.
Any other suggestions I can try out ?
Edit:
Running the code like this works fine:
<br />
public void UpdateCustomer(CustomerInfo customer)<br />
{<br />
string query = "UPDATE CUSTOMER SET Name = " + "'" + customer.Name + "'," + "Age = " + customer.Age + " WHERE ID = " + customer.ID;<br />
OleDbCommand command = new OleDbCommand();<br />
command.CommandText = query;<br />
command.Connection = this.connection;<br />
command.Connection.Open();<br />
try<br />
{<br />
int x = command.ExecuteNonQuery();<br />
Debug.WriteLine(x.ToString());<br />
}<br />
catch (Exception ex)<br />
{<br />
throw new Exception(ex.Message, ex.InnerException);<br />
}<br />
finally<br />
{<br />
command.Connection.Close();<br />
command.Dispose();<br />
}<br />
}<br />
|
|
|
|
|
UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID
private string PARAM_ID = "@ID";
private string PARAM_NAME = "@Name";
private string PARAM_AGE = "@Age";
What i think you'll find is happening here is that the sql statement is being executed and is trying to set name = id, age = name where id = age. Awkward problem.
|
|
|
|
|
Your problem is with the order of your parameters. System.Data.Oledb does not work like sqlclient (or Access internally) The parameter names are irrelevant, only order matters.
change your parameters to:
parameters[0].Value = customer.Name;
parameters[1].Value = customer.Age;
parameters[2].Value = customer.ID;
So that they match the order of occurrence in the query.
|
|
|
|
|
Thanks for the tip, I'll have to makesure I am more observant next time
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
