Call me stupid, but I need help.

I'm converting from a Dbase5 file to SQL database. I have a "dbase5"
connection for the source database and a "OLE ..SQL" connection to the
destination database. Between the two connections I have a Data
Transformation Task. I have a global variable called "DestDatabase"
which is the name of a database where I'm copying data to and the SQL
connection is set to this. I plan to use DTSRUN to run the package and
pass in the destination database as a parameter on the command line.

The problem is that when setting up the Data Transformation Task with
"Copy Column" transformation, the Destination tab requires that you
specify a database along with the table and this database name can't be
changed nor does it respond to the connection which is correctly set.
For example, in the destination it will show "[shoe].[dbo].[Widths]"
where "shoe" is the database name and "Widths" is the table name.

The SQL connection will show the correct database and will create
tables as needed when executing SQL tasks, but no matter what the SQL
connection is connected to the Data Transformation Task will only
operate on the database that was hardcoded when created.

How do I get around this?

Thanks, Blake

Would doing this help

Right click on the designer surface
Choose "Disconnected Edit"
Open up The tasks node of the tree.
Find your particular task and highlight.
Now look in the right hand pane you should see a property called
DestinationObjectName with the valkue you want to change
Double click on it and remove the database part of the string.


--


Regards


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"Blake" <blake.causey (AT) nationaldatatrust (DOT) com> wrote

Allan,

Your wonderful. That's all I needed. It seems to work on one of my
Data Transformation Tasks so I'm going to try it on the others. It was
a simple solution and that makes it even better.
I just disconnected and then removed the database name just like you
said. It worked perfectly.

Thanks again,
Blake

Allan M,
Could you please help on this
I need a favour that I have a table with all differnt clients with
qutarly data
Client Data1 Data2 Date
AAAAA d1 d2 1/1/2006
AAAAA d3 d3 1/1/2006
AAAAA d4 d4 2/1/2006
AAAAA d5 d5 2/1/2006
something like this. I need to create a DTS which export data from this
TABLE and put into EXCEL with REPORT_AAAAA.XLS in this data1 & data2 &
date coulmns should display for each month it should create a Sheet
Jan(All Jan Data) Next sheet FEB all feb data.

OK So for a sheet read a CREATE TABLE statement.

You can issue a CREATE TABLE statement against the XL connection to
create the sheet. You then change the DestinationObjectName of the
transform data task to throw data into the right sheet.


Because the structure/metadata of the transform data task will not
change it should be relatively straight forward.

You will need to loop through your input dataset to get the rght values
as well so you can change your SourceSQLStatement.




--


Regards


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"achyutmurari (AT) gmail (DOT) com" <achyutmurari (AT) gmail (DOT) com> wrote

Allan
Thanks for the response. Do you have any sample? so, that I can use as
starting point..


Allan Mitchell wrote:

Have a look at www.SQLDTS.com we have a number of examples of the types
of thing that you are going to need to be doing.

--


Regards



Allan Mitchell


Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com


"achyutmurari (AT) gmail (DOT) com" <achyutmurari (AT) gmail (DOT) com> wrote