SSIS and Raw files

  • 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.

  • 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

  • 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!!

  • 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

  • This might be a simple question but how do I upload it

  • 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

  • This a sample of my package, I have changed extension to txt from dtsx.

    Hope this is enough.

    Cheers

  • " 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.

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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...

  • 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