question

patdev-3689 avatar image
0 Votes"
patdev-3689 asked Monalv-msft edited

SSIS column transformation does not work

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-transact-sqlsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @patdev-3689 ,

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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 Votes 0 ·

Hi @patdev-3689 ,

Please use Truncate Table DB-table in Execute SQL Task to delete all data in the destination table.
And then load the data from source to destination in Data Flow Task.

83547-cf.png
83530-est.png

Best regards,
Mona



0 Votes 0 ·
cf.png (13.4 KiB)
est.png (25.0 KiB)
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

patdev-3689 avatar image
0 Votes"
patdev-3689 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.