February 18, 2010 at 8:53 am
Hi all
Does anybody have an idea if you can use raw files to update records in a table using SQL server 2005
I have trawled the internet but with no avail. I tried to using a TSQL update statement.
I have about 5000 records to update and need it to be fast.
Can use a OLE DB Source and OLE DB Command. but it is to slow.
Example code would be much appreciated.
February 19, 2010 at 12:36 am
clucasi (2/18/2010)
Does anybody have an idea if you can use raw files to update records in a table using SQL server 2005
I have trawled the internet but with no avail. I tried to using a TSQL update statement.
I have about 5000 records to update and need it to be fast.
Can use a OLE DB Source and OLE DB Command. but it is to slow.
Can you give some more details regarding your question? Are you trying to read from RAW files as a source or do you want to use them to speed up things?
In SSIS, you can use the Raw File Source and the Raw File Destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 23, 2010 at 1:25 am
Yes I want to use the RAW file as a source and then update records in the database (tried using TSQL update but not sure about how to get the RAW data into it) It needs to be fast!!
February 23, 2010 at 2:20 am
Upload your current dtsx package for us to see what you need and assist.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 23, 2010 at 5:31 am
This might be a simple question but how do I upload it
February 23, 2010 at 5:43 am
open attachment
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 23, 2010 at 7:47 am
This a sample of my package, I have changed extension to txt from dtsx.
Hope this is enough.
Cheers
February 23, 2010 at 9:11 am
" Yes I want to use the RAW file as a source and then update records in the database (tried using TSQL update but not sure about how to get the RAW data into it) It needs to be fast!! "
--Here you have to be more specific in terms of what kind of updates u want to perform in database. Based on that there are several transformations available in SSIS for ex., derived column, conversion etc. and all of them can read data from ur RAW file source.
Also, reading data from ur RAW file source is relatively much faster than flat, oledb sources because it does not require parsing and translation of data.
February 24, 2010 at 12:44 am
Here is a sample of the data I am trying to update
"21,""U"",8,100012714788,1,2,2001-04-18,""ZW"",,367903.00,403212.00,1,4250,2001-04-18,,2008-04-07,2001-04-18,"""",""00BWGG"","""",,,"""""
The U stands for an update, there are other codes for Insert (I) and Delete (D) but I have split these off using a conational split
The only field that wont change will be the "100012714788" all the rest could potentially change
February 24, 2010 at 5:12 am
One of the quickest way to do updates in bulk manner is to delete the rows from the destination table first then add the updates in the same way as you would the the inserts
February 24, 2010 at 5:47 am
steveb. (2/24/2010)
One of the quickest way to do updates in bulk manner is to delete the rows from the destination table first then add the updates in the same way as you would the the inserts
If you feel uncomfortable deleting rows, you can write your 'update-rows' to a temp table and then use a Execute SQL Task to perform a UPDATE-FROM statement. But I believe the method described by steveb is the fastest.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2010 at 10:58 am
I looked at you r package and you have a Raw Source then OLEDB Command and OLE DB Destination... which yu dont need, If you just want to update your values You will just need a Raw Source, a script component to split columns accordingly and then OLE DB destination to update the values... Also your OLE DB Command should like BELOW instead Of:
UPDATE BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21
SET RECORD_IDENTIFIER = [Temp Update].F1, CHANGE_TYPE = [Temp Update].F2, PRO_ORDER = [Temp Update].F3
FROM BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 INNER JOIN
[Temp Update] ON BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21.UPRN = [Temp Update].F4
UPDATE BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21
SET RECORD_IDENTIFIER = ?, CHANGE_TYPE = ?, PRO_ORDER = ?
FROM BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 INNER JOIN
[Temp Update] ON BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21.UPRN = ?
Where "?" represents parameter value.. you just need to map the column from the script component as a parameter to the OLE DB Command
February 24, 2010 at 12:59 pm
True, you can do almost everything with a script component/task, but there are solutions that don't require scripting.
Some people aren't comfortable with VB.NET or C# and what if the guy who does the future maintenance/support doesn't know how to code?
(the company where I currently do a project has even a policy that says to minimalize the use of script components/tasks and to only use them when there is no other option)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2010 at 1:57 pm
This can be done in many ways.. one of them being using script task..I think that Everyone working on SSIS needs to know basic scripting because there are many things that cannot be achieved using the components available.. i used to hate writing scripts when i started working on SSIS but later had to learn basics to get the work done...
In this case we can also use derived column instead of script component...
February 25, 2010 at 5:02 am
hi SSC Rookie
Yhanks for your solution
is there a way of using a use a raw file instead of a Temp_update table.
what the sql be for that
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply