0

I've been assigned a task to create an automated data insert process, where new data is taken from a table that exists in one server, and needs to be inserted into another table in another server. Per the DBA, I am not allowed to add in either of the servers as linked servers and therefore am forced to use SSIS to complete this task. The data that I am to take from the original table is generated based on the below SQL query that outputs an INT column and a VARCHAR column.

SELECT DISTINCT (Primary_Transaction_ID)
    ,Transaction_STATUS
FROM DB1.dbo.Table1
WHERE Primary_Transaction_ID IS NOT NULL;

In addition, the data that I will be inserting into the other table, has to be unique, and I cannot insert in data, that I have already inserted into the table before. I have created the table in the other server with data type INT for "Primary_Transaction_ID" which is also the Primary Key and data type char(8) for "Transaction_Status".

So far I have created an Execute SQL Task in SSIS, which runs the above query, and stores the "Full Result Set" into a variable with data type "Object".

enter image description here

How do I go about, splitting this "Object" variable into two columns, with the same data types as the two columns in the destination table that I have created. While also ensuring that the data that I am inserting, is new data and not data that I have previously inserted into the table.

I plan on running this process daily through task scheduler after it is completed. Thanks in advance, any help with this would be greatly appreciated.

Edit:

This question has been closed and referred to a similar question in which the person is attempting to insert unique data and not duplicates. That question does not answer my question, and does not face the same issue in which SSIS has to be used due to denied permissions on Linked Servers. There are multiple parts to my question, and the referred question, answers maybe a singular portion of my question.

  • DISTINCT is not a function, it's a part of SELECT DISTINCT, and works on the whole selected rows. Skip those extra parentheses and simply write `SELECT DISTINCT Primary_Transaction_ID, ...` to make code clearer. – jarlh Dec 14 '20 at 20:20
  • @jarlh I didn't write this query and am aware, I was simply provided this by the requester of this process. I'm not going to modify his query and simply want to provide him with what he requested of me, which is the automated SSIS process. –  Dec 14 '20 at 20:23
  • Remove your Execute SQL Task. Use a Data Flow Task. Within the data flow task, use your query in an OLE DB Source component. Add a Lookup component after the Source and configure it to point at the target database and table. You want the "no match" output from the lookup and route those rows to an OLE DB Destination – billinkc Dec 14 '20 at 20:23
  • @billinkc How do I go about inserting a SQL Query into a OLE DB Source Component? I couldn't find an answer on google, I'm sure I'm missing an obvious thing here, but if you could let me know how to do that I'd be very grateful. –  Dec 14 '20 at 20:32
  • @randomguy2443 What options do you see when you add an OLE DB Source to your data flow? – billinkc Dec 14 '20 at 20:38
  • @billinkc I am only able to see two tabs, the connection tab and all tab. From the connection tab I am only able to select a server and a database. After it's created I can edit the created source package and add in a table. However I do not see any option to add in a SQL Query to filter the table. –  Dec 14 '20 at 20:43
  • I don't think you're using the correct component https://learn.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-source?view=sql-server-ver15 – billinkc Dec 14 '20 at 20:44

0 Answers0