If you're asking which machine is doing the work, then it would be the machine that has the SSIS package deployed on it. So, in your case, Server 2.

Of course,...

I'd considered CONVERTing to a date, but I figured that doing a date conversion and then comparison would be more expensive than leaving it as an INT.

Thanks for the input...

You probably should remove that info ...

Actually looking over your queries again, I noticed something.

For your queries against the remote server, you have a LEFT JOIN against smartTuition_Info_ScanBatchProcessing.

However, later on in the query, you are saying:...

Short of using replication, you have no option but to use a linked server - your data exists on a remote location, so you need to fetch it somehow. I...

Agreed.

Create/Change your stored proc to execute a stored proc on the remote server. The remote server stored proc will first truncate a local table, then insert the results of a...

Hm. So you're using the max of a string value. That works in all cases? Hadn't thought about trying that, 'cause I wasn't sure how SQL Server handles taking the...

You need to explain more what you mean by "It didn't work"

What are you getting as an output? Do you have any message box appear when you execute the package?

After...

Try changing it to this:

Public Sub Main()

Try

MessageBox.Show("Before connections")

Dim connMgr As ConnectionManager = Dts.Connections("AuthenCustomer")

Dim ConnStr As String = connMgr.ConnectionString

Dim sqlConn As SqlConnection = CType(connMgr.AcquireConnection(Nothing), SqlConnection) ' Object to hold connection

Dim sqlCmd...

Did you try getting the error message?

Change your error handler part to this:

Catch ex As Exception

MessageBox.Show("Failure: " + ex.Message.ToString())

Dts.TaskResult = Dts.Results.Failure

End Try

To answer your original questions then,

"1. Has anybody out there used a technique like this?"

I've never used a technique like this. I have at times constructed dynamic SQL queries using...

It should of course also go without saying that this just screams security problems.

Dynamic SQL in and of itself is a security loophole. There's ways to mitigate the risks, but...

Sean Lange (8/26/2011)

---

kramaswamy (8/26/2011)

---

It seems to me like a better solution would be for you to just add a new column to your table and make use of the new...

It seems to me like a better solution would be for you to just add a new column to your table and make use of the new HIERARCHYID data type...

SSIS is the nicest solution, but probably requires you to learn more than you would want for a test.

If what you're doing is essentially running a block of code in...