June 29, 2004 at 7:13 am
Probably a dumb question: I am unable to connect to connection objects with an Execute SQL Task, as shown below. When I choose WorkFlow--on success, it always returns an error indicating they are not proper objects to connect. Can't you do this? I would like to avoid using a Transform task, since Exec sql is supposed to be faster.
Connection1 (sqldb1)
V
Execute SQL Task
V
Connection2 (sqldb2)
By the way, I am able to connect it at the begging of a Data Transform Task, like below...
Execute SQL Task
V
Connection1 (sqldb1)
V
Data Transfor Task
V
Connection2 (sqldb2)
Thanks
smv929
June 29, 2004 at 9:22 am
If all you want to do is run an execsql, you will only have one conn object. You won't "connect" it to anything with a workflow. The execsql will use only one conn. You will need to qualify the name of the other database in your code.
Workflows are about precedence - that is - the order in which steps are performed. If you don't have two steps, there is no need for a workflow and the gui won't let you add the "candycane". Once you have both the transformation and the execsql, you have two steps. That's why you can add the "candycane" when you have the transformation after it.
[font="Courier New"]ZenDada[/font]
June 29, 2004 at 9:27 am
By the way, when using transformations - if all you are doing is copy columns, go into the transformations tab, delete all, select all, and select copy columns. You will run the whole thing as a single com object, instead of separate com objects for each field. That will reduce overhead and speed it up.
One of my favorite books is Wrox SQL Server 2000 DTS. You should check it out!
[font="Courier New"]ZenDada[/font]
July 1, 2004 at 10:39 pm
Jules, thanks a bunch for your comments. Regarding the first one...duh. I should have thought of that. Two, I'll try the performance tip.
Very helpful.
smv929
July 2, 2004 at 7:20 am
Sorry. I must be missing something. How can I use a sql task to get data from a source to a destination by executing a simple select * from table query. Here's what I did:
1. I created a single connection (to the datasource - an odbc connection to a Progress database).
2. I created a SQL task that uses that previous connection as the Existing Connection. In the query box, I entered a simple "Select * from table" statement.
3. That's where I'm at a loss. How do I then get it into a destination datasource. I created a destination connection, but can't get them connected.
The only reason I'm using a SQL Task over Transform task is because it's supposed to be faster. Thanks!
smv929
July 2, 2004 at 9:17 am
If you are going to do everything in an ExecSQL task, you create one connection icon. It stands alone. You create one ExecSQL task. it stands alone also. Do not create another connection icon for the destination. Your ExecSQL statement will then be an insert statement:
insert into destinationDB.tbl_Foo
select field1, field2 from tbl_Bar
See how you simply qualify the destination with the database name?
A simple select statement only works in a transformation between two connection icons. Try it! Don't believe everything you hear about "supposed to". Test it yourself.
[font="Courier New"]ZenDada[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply