0

I am using data flow to send data between different DB servers and I do not have a joined server. I need to validate the data with a stored procedure before insert the data from OLE DB source into the destination. May I know is there any way can achieve this?

Noobie
  • 37
  • 1
  • 7
  • A stored procedure can be used as source for a SSIS data flow. So if the procedure can reside in the source database and only requires data from the source, then yes. – Cedersved Apr 29 '21 at 09:57
  • Thank you Cederved. The procedure need reside in the destination database. – Noobie Apr 29 '21 at 13:47

2 Answers2

1

Theoretically it is possible, but it makes everything so much more complex. I prefer to keep my SSIS packages as simple as possible (in case they need to be upgraded one day) and to always have a staging area when transferring data (helps with troubleshooting immensely!).

In your case, I would create a local staging table and do the 1 to 1 data transfer from source to this table (with a truncate, so you always have only the latest load available). It gives you a chance to review the quality of the data before inserting it into the main table. It also gives you more overview over the load process in case the package failed.

sbgdata
  • 46
  • 4
0

In a cross server data transfer, one of the most common patterns is to transfer the unmodified source data to a staging table in the target database, or at least on the target server, and then work with it from there. My personal preference is to create a table with the same name and structure as the source table in a distinct schema within the target database. Something like src., for instance.

If you do that, you can run your data validation against the local replica and, if it passes validation, do your insert from there as well.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35