SSIS column transformation does not work

pat dev 1 Reputation point
2021-03-29T16:27:46.267+00:00

hi all,

I am trying to use SSIS package to update and delete records from flat file to db.

source: Flat file
column: ID (DT_STR) --> changing on the source to (DT_I4)

destination:

DB - table
column : ID (INT)

when i try to connect to traget with oledb command and use command (delete from table where ID(destication) != ID (source)) it deletes all records other then it should be.

Please guide!!!

thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,437 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Monalv-MSFT 5,891 Reputation points
    2021-03-30T02:23:36.037+00:00

    Hi @pat dev ,

    1.Could you please share the example data in your flat file and DB-table and your desired output?

    2.We can use Lookup Transformation to find the Match Data and NoMatch Data between flat file and DB-table.

    3.May I know if you want to load Match Data or NoMatch Data from flat file to DB-table?

    Please refer to the following pictures:
    82505-df.png
    82611-flatfilesource.png
    82506-dataconversiontransformation.png
    82612-lookuptransformation-general.png
    82557-lookuptransformation-connection.png
    82585-lookuptransformation-columns.png
    82558-matchdata-nomatchdata.png

    Best regards,
    Mona


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Olaf Helper 40,156 Reputation points
    2021-03-30T06:17:31.757+00:00

    use command (delete from table where ID(destication) != ID (source)) it deletes all records

    You get the ID one by one and so you delete all records, where destination ID is unequal to source ID = you delete all records. Rethink your approach.

    0 comments No comments

  3. pat dev 1 Reputation point
    2021-03-30T20:58:43.577+00:00

    Hello @Mona ,

    you solution helped quite a bit in the solution.

    1) data is like as followed inf flat or csv file:
    id pid sdate edate cid
    "132","148334" ,"1/2/2018 12:00:00 AM", "32342121"
    "1584","148334" ,"1/18/2018 12:00:00 AM", "32347863"
    "981","148334" ,"1/2/2018 12:00:00 AM", "32342121"
    "1232","153726" ,"1/2/2018 12:00:00 AM", "32347863"
    "185","153726" ,"1/2/2018 12:00:00 AM", "32342121"
    "6853","123865" ,"2/2/2018 12:00:00 AM", "28383728"
    "7563","148334" ,"1/2/2018 12:00:00 AM", "26351735"

    I want to load new data, update anything has changed and delete if not exist in source but the one i have is working with conditional split is taking hours to delete about 1M records.

    thanks

    0 comments No comments