1

I want to only have 1 row in my table. I am populating a row from Server1 to Server2 on a single table using SSSIS. I am updating Execution End time that I get from a table in server1 to a table in server2. Here is the query I use to populate :

SELECT TOP 1 EXEC_END_TIME    
FROM cr_stat_execution cse    
WHERE cse.EXEC_NAME = 'ETL'     
ORDER BY exec_end_time DESC

The problem: I only want to update server2's table with the recent record only or rewrite previous days data. I don't want to have a history on my table, how can I modify my query to only populate the most recent data from Server1 to Server2 without having rows of history.

Austin
  • 2,203
  • 3
  • 12
  • 28
WhoIsNotActive
  • 13
  • 4

1 Answers1

0

Your package will have two Connection managers. In this case, I'll assume OLEDB but ADO.NET or ODBC will also work and further assume they point to Server1 and Server2 and are named as such.

The pattern you are going to have is two Execute SQL Tasks in serial. The first Execute SQL Task will ask Server1 what the value of EXEC_END_TIME is and store that to an SSIS variable.

Create a variable named LastExec and set the data type as datetime and initialize it to something like 1900-01-01 at midnight

In the Execute SQL Task, change the result type from None to Single Row and then on the Result Set tab, map the 0th element to the variable

See also How to set a variable using a scalar-valued tSQL function in SSIS SQL Task

The second Execute SQL Task will update statement as Panagiotis describes and the "magic" will be using the SSIS variable in the query.

UPDATE ThatTable SET ThatField=?

The ? is the place holder for OLEDB and ODBC connection manager queries. The difference being OLE is 0 based ordinal and ODBC is 1 based. ADO.NET will used named parameters so the original comment query would work.

In the Parameter Mapping tab, you will need to associate the SSIS variable with ordinal position 0 of your query. Sample screen Logging information when executing remotely SSIS from ASP.NET

Get the first execute sql working first. Once you can query the database and assign to a variable, getting the next one (set as a successor) in line should be a snap.

Data flow approach

You could continue with your data flow approach. Instead of an OLE DB Destination, you'll use an OLE DB Command. Use the same query and this time, you'd map the source column to the zero-eth element.

It's overkill so that's the reason I did not advocate for its approach.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • I already deployed the SSIS package, the way I did it is I used a DATA FLOW TASK and used OLE DB Source and OLE DB Destination. then confirgured the connection from there. So instead of doing Data Flow task, you're suggesting to use Execute SQL Tasks? hmm I will try it now and update you...Thank you!! – WhoIsNotActive Sep 19 '22 at 18:38
  • I configured the first part for Server1, however working on server2 sql execute task: I set the connection type to OLE DB, SQLSourceType = DirectInput, SQLStatement = UPDATE TableName SET EXEC_END_TIME=?... Is there anything I need to configure on Parameter Mapping, Result Set or Expressions Tab? It failed when I executed the task on Server2 or Destination Server. – WhoIsNotActive Sep 19 '22 at 19:16
  • Updated answer but yes, you need to configure parameter mapping to get the variable to work with the second execute sql task – billinkc Sep 19 '22 at 20:45
  • Yes, I am lost on the "you need to configure parameter mapping to get the variable to work with the second execute sql task".... Can you please help on this part? Tnx – WhoIsNotActive Sep 19 '22 at 21:00