March 20, 2020 at 12:24 pm
Hi ,
I have an on-premise SSIS package which connects to SQL Server database(Source).
On connecting source using onpremise SSIS need to connect Azure SQL database , after connecting Azure SQL database need to truncate the stage table first and insert the source data.
On success of source data to destination table (stage table), need to call one stored procedure in same Azure SQL database and merge the output of stored proc and destination table(stage table) and insert to main table.
What component should be used here?
Using ADO.net i am able to connect Azure SQL database and only able to manage mapping of source and destination alone, not able to use truncate statement or not able to call the stored procedure.
What would the be best component to use for the above scenario.
Using Visual studio: 2017 for SSIS.
Source database : SQL server 2016
Destination database : Azure SQL database
Thanks!
March 20, 2020 at 1:14 pm
To truncate a table or exec a stored proc , use an ExecuteSQL task.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 20, 2020 at 1:32 pm
Thanks !
How about using the stored proc and merging the output ? Merge the output from SP and stage table, how can we achieve this ?
March 20, 2020 at 1:50 pm
OK, you need to do that differently.
You'll need to create a data flow. Inside the data flow, create two data sources, one which selects from the stage table and one which executes the proc and outputs the results.
The results from both of these data sources should be sorted in the order of the match keys (the columns you will join on).
Add a MERGE JOIN component and connect both of the data source outputs to it. You will be able to perform your join here. It's been a while since I used one, but make sure you track down the IsSorted (?) property on both the sources to the MERGE JOIN and set it to true.
Add a suitable destination component and you're good to go.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 20, 2020 at 2:13 pm
Once again thanks for time & suggestion!
So in this case , we need to create 2 data flow task to achieve? One to insert data in stage table , on success in stage table , call second data flow task , inside the second data flow it will have two data sources ? which component will work on Azure SQL to execute the store proc in data flow.
Can we achieve with one data flow task ?
March 20, 2020 at 2:43 pm
You are right: two data flows will be required.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 20, 2020 at 2:55 pm
Thanks , but how about 2 data sources in data flow task , used one data source as ADO.net for Azure SQL database stage table, which data component to use for Azure SQL Stored procedure execution ?
March 20, 2020 at 3:45 pm
Add an OLEDB source, with suitable connection.
Set Data Access mode to SQL Command.
In SQL command text, add the command to execute your stored proc: EXEC dbo.SomeProc
(If your proc selects its results from a temp table, a bit more than this is required, but we can get to that if it's relevant.)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply