April 23, 2008 at 10:23 am
Hi,
I was wondering if there is a way to do this sort of statement in SSIS. I've tried Data Flow tasks, but it forces me to choose only one table when I want the entire database to access any table in that particular database. I also want to be able to just change the data source so that it's easy to re-configure it for any database. Below is an example of something I want to do in SSIS:
insert into DB1..Customer
select *
from DB2..Address a
join DB2..Price p
on a.ID = p.ID
Is there a way to do this? Sorry, I'm new to SSIS... :/
Thank you in advance!!
April 23, 2008 at 11:31 am
What you are attempting to do is use dynamic SQL. The best way to do that is to create a variable and then using that variable, set up your SQL Statement. In the dataflow task you can then select SQL Command from variable which will then run the code you want.
Also, don't forget to evaluate your variable as an expression by setting evaluateasexpression to true in the property box of the variable.
Hope this helps
Marvin Dillard
Senior Consultant
Claraview Inc
April 23, 2008 at 11:34 am
On the dynamic connection, think script task. That should enable you to set the connection dynamically.
Marvin Dillard
Senior Consultant
Claraview Inc
April 23, 2008 at 1:09 pm
daJonx
I understand your issue, however, it would be very hard to get you to the right area without being in an IM chat or conversation as I'm thinking you would not understand the names/nomenclature you would need to know. I'm available via IM during the day and if you like I can give you my IM account to walk you through this issue.
MD
Marvin Dillard
Senior Consultant
Claraview Inc
April 23, 2008 at 1:15 pm
Here is a good web source as well
http://sqldb.wordpress.com/2006/06/21/dynamic-sql-in-sql-server-integration-services-ssis/
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply