Dynamic Insert Script

Hello everyone,

I am running into a challenge with a simple package that I am developing. The package is doing the following:

1. Read data from SQL Server in form of insert statements - "Insert Into DestinationTable Values ('a', 'b')". This is getting stored in a variable <Insert Script>.
2. There's a SQL Script that's nothing but the reference to the variable created above - <Insert Script>.
3. Finally there's a step that invokes the SQL Script against a custom ODBC Data Source.

The challenge is that the size of the variable is limited. Script ends abruptly and the task ends in failure, due to partially completed SQL statement - direct result of a cutoff driven by max length of the variable.

What are my options here? The final step has to remain unchanged. I have to execute SQL Insert statements against the destination. ODBC Destination is limited in what it accepts as input.

I know I could introduce a loop and execute about 30 statements at a time, but that seems like a waste of cycles. Is there anything that would let me extend the max length of the variable, or say execute a SQL Statement stored in a file, instead of a variable?

Thanks for your expertise!

Dmitriy

Maybe I am missing something by why do you need all this complexity when you can just load data directly into your custom ODBC source from an SQL server?

Dmitry

We are keen to help you but you have not posted a single screenshot so we do not know anything about your package

Mike, thank you very much! That's actually brilliant. Not sure why I didn't think of it myself. So you are suggesting just creating a Linked Server for the ODBC Data Source and inserting directly from SQL Server instance? I think that would work!

I am going to give it a try a today and post screenshots of the package, in case it does not work for some reason. I appreciate your help! Very cool idea.

Dmitriy

Thank you,
Please keep us posted on your progress