0

OK this seems like it should be insanely easy, but I cannot figure it out. Every where I look online says to create temp tables and VB scripts and I cannot believe I have to do that. My goal is to insert all the records in a table with a date later than the max date in that destination table. UPDATE The 2 tables are in two different non linked SQL databases So:

Select @[User::Dated] = MAX(Dateof) from Table2

Insert into Table2
Select *
From Table1
Where DateOf > @[User::Dated]

I am trying to do this in SSIS. I declared a variable, the SQL execution step looks like it is assigning the single row output to it. But when I got go into the data flow it give me no parameters to choose, when I force the known parameter which is in the project scope it says no parameter exists

Dave Sexton
  • 10,768
  • 3
  • 42
  • 56
Holmes IV
  • 1,673
  • 2
  • 23
  • 47
  • What you're explaining should work fine. For the data flow, in your data flow source you need to put the correct parameter placeholder in your SQL:, like this: http://stackoverflow.com/questions/18288183/parameterized-oledb-source-query – Nick.Mc Oct 29 '15 at 23:56

2 Answers2

1

Create two OLE DB data sources each pointing at you two databases.

Create a variable called max_date and make its data type String.

Place an Execute SQL Task on the Control Flow, change its connection type to OLE DB and for the connection select the name of the data source that contains Table2. Set the ResultSet to Single Row. Add the following for the SQLStatement:

SELECT CAST(MAX(Dateof) AS VARCHAR) AS max_date FROM Table2

Go to the Result Set pane, click Add and enter the following:

Result Name: max_date
Variable Name: User::max_date

You can now use the max_date variable in an expression to create a SQL statement, for example you could use it in another Execute SQL Task which would use the second Data Connection like so:

"INSERT INTO Table2
SELECT *
FROM Table1
WHERE DateOf > '" + @[User::max_date] + "'"

Or in an OLE DB Source in a data flow like so:

"SELECT *
FROM Table1
WHERE DateOf > '" + @[User::max_date] + "'"
Dave Sexton
  • 10,768
  • 3
  • 42
  • 56
0

You can do this in a single SQL Task if you want:

Insert into Table2
Select *
From Table1
Where DateOf > (Select MAX(Dateof) from Table2)

If you want to use multiple Execute SQL Task items in the control flow, or want to make use of the parameter in a data flow instead, you have to change the General > Result Set option for your MAX() query to Single Row, then move from General to Result Set and Add a new variable for your result set to occupy.

To use that variable in your INSERT INTO.... query via Execute SQL Task, you'll construct your query with a ? for each parameter and map them in the parameter mapping section. If a variable is used multiple times in a query it's easiest to use a stored procedure, so you can simply pass the relevant parameters in SSIS.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • I am going to check into this, but I believe this will not work, sorry let me update, my above. The 2 table are in 2 different data sources :( sorry again for that key info – Holmes IV Oct 22 '15 at 18:42
  • @HolmesIV Yeah if they're using different un-linked servers then you won't be able to do them in one step, still the rest of the answer is relevant for how to set it up using multiple `Execute SQL Task` items. – Hart CO Oct 22 '15 at 18:45