DTS from 2 sources ??

hi
i want to load Data from 2 sources (access and mysql) the 2 source have
same schema ;how to load to cube the data and retrive the data of each
source and how configure DTS to continue from the last ID of the last
load exp :
source1 : product(id,,name)
(1,nokia 3310)
source 2 : product(id,,name)
(1,nokia 3310) ?
john1425
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1052215.html
simply create a package where you do a lookup to get the last ID from a copy
of your table, then load the mysql database with a filter on the last ID,
then do the lookup again to get the last ID again and load the access table.
(store the lastid into a dts variable, and use it has a parameter for a
filter "select * from table where id > ?")
you'll have 1 copy of the table without duplicated IDs.
But regarding your configuration, you can directly merge information using
linked servers.
In SQL Server create 2 linked servers (1 to access 1 to mysql)
create a view like: select ... from mysql.db..table union select ... from
access...table
the union eliminate duplicated rows (but its a slow solution)
there is other solutions.
"john1425" <john1425.1orlsz@.mail.webservertalk.com> wrote in message
news:john1425.1orlsz@.mail.webservertalk.com...
> hi
> i want to load Data from 2 sources (access and mysql) the 2 source have
> same schema ;how to load to cube the data and retrive the data of each
> source and how configure DTS to continue from the last ID of the last
> load exp :
> source1 : product(id,,name)
> (1,nokia 3310)
> source 2 : product(id,,name)
> (1,nokia 3310) ?
>
> --
> john1425
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1052215.html
>
sql

DTS from 2 sources ??

hi
i want to load Data from 2 sources (access and mysql) the 2 source have same
schema ;how to load to cube the data and retrive the data of each source an
d how configure DTS to continue from the last ID of the last load exp :
source1 : product(id,,name)
(1,nokia 3310)
source 2 : product(id,,name)
(1,nokia 3310) 'simply create a package where you do a lookup to get the last ID from a copy
of your table, then load the mysql database with a filter on the last ID,
then do the lookup again to get the last ID again and load the access table.
(store the lastid into a dts variable, and use it has a parameter for a
filter "select * from table where id > ?")
you'll have 1 copy of the table without duplicated IDs.
But regarding your configuration, you can directly merge information using
linked servers.
In SQL Server create 2 linked servers (1 to access 1 to mysql)
create a view like: select ... from mysql.db..table union select ... from
access...table
the union eliminate duplicated rows (but its a slow solution)
there is other solutions.
"john1425" <john1425.1orlsz@.mail.webservertalk.com> wrote in message
news:john1425.1orlsz@.mail.webservertalk.com...
> hi
> i want to load Data from 2 sources (access and mysql) the 2 source have
> same schema ;how to load to cube the data and retrive the data of each
> source and how configure DTS to continue from the last ID of the last
> load exp :
> source1 : product(id,,name)
> (1,nokia 3310)
> source 2 : product(id,,name)
> (1,nokia 3310) '
>
> --
> john1425
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1052215.html
>

DTS Export Data Pump appending file

Hi!!
I've a "little" problem...
I must generate a txt file with this structure:

- one header
- n details

Header and details have different structure and i must write header before details.. I've already a table with details.. i could create table for header, but this table design it's too different than details design.. and i can't use the union statement in the output query..
Can i write the header line and write details in append on a txt file??
with data pump i can create and replace file.. but not append??
I know that i could use the filesystem object to modify the file, but i would bypass it with tasks of SQL server..
it's possible?

Thanks!!!

PS: I hope that you understand my problems..
I know poor english.. ;-)Why not post your DDL and DML...

Check out

SELECT Data_Row FROM (
SELECT '"Owner","Job"' As Data_Row, 1 AS Row_Group
UNION ALL
SELECT '"' + l.Name+ '","' + j.Name + '"', 2 AS Row_Group
FROM msdb..sysjobs j
INNER JOIN master..syslogins l
ON l.sid = j.owner_sid
) AS XXX
ORDER BY Row_Group|||thanks..
i've already done in that mode... ;-)sql

DTS -Copy tables

Hi
I have a DB2 database server.It has 2000+ tables...And I have a MS SQL database server.It has got the same tables.With same schema...
I want to take all of that tables datas to MS Sql database's tables...
I try to use DTS Import Data Wİzard but it gace an erro which is Unknown Error!!!

Now;
I try to make a DTS which is capable with doing this mission...
I want to create one DTC package and the source table and destination table names will be changed dynamically...Probably i should use ActiveX Task or Dynamic PropertTask...
Despite i have searched from net very much i couldnt find any helpfull article or sample for 3 days...I have also checked this one;
http://www.sqldts.com/default.aspx?246

But it hasnt helped me too..

I really wish thatsomebody can help me...
Thank u all

Osman AYHANpersonally...I would set up unloads from DB2...then ftp the files to the server, where I would have several jobs wake up and do bcp's or bulk inserts into the tables...after truncating them

Is there RI on the SQL server database?|||Hi brett

i dont know whatz unloads which is in DB2...
What do u mean while saying ftp files to server?Which server and which files?
Also for BCP's or Bulk inserts i need a traverse betwwen all tables to do this programatically...
And last question whatz RI?

Sorry...
I was thinking i am good at MS SQl
but after your questions i feel myself poor|||what version of DB2 and what platform?

Are you the DB2 DBA?

And are you planning on doing a full replacement of the Data from DB2?|||The Version Of the DB2 is V 8.1
And i am not DBA.I am only software engineer...
The main goal of this project is taking back up of the main ERP system which is running on DB2 to MS SQL Server...
I wrote code for this project but it runs so slowly.Becouse of DTS is one of the inner programmes of MS SQL it runs faster and faster than my programme.So I decided to use DTS.For the biggest table which contains 6million row of data it took the datas from DB2 to MS SQL in 43 minutes.And this is an already accepted time manner.
SO i think if i can write an ActiveX on DTS or whatever instead of creating 2000+ DTS package (I have nearly 2000+ table) i may write a complex DTS which will handle this..
SO this is the problem..ANd in my company here is not any MS SQl DBA or DB2 DBA...I try to handle all of this with myself...

Best Regards...

Osman AYHAN|||Brett at last i wrote an ActiveX code for getting all table names in the source database...
Do u have any idea to lighten my road;
how can i send this table names for processing at the workflow...
Probably there should be a connection for Datasource 1 for destination and a ActiveX script..
I want to pass values from ActiveX to Datasource...sql

DTS connection

Hi
I'm using DTS to transform Data between 2 SQL server .My package uses Activex transformation with lookups.first I used 2 connections one for the Source and another for the destination and I used the destination connection to fetch the data for thr lookup but while executing I faced an error saying Connection is busy with results from another command. I creaed another connection for the lookups and it worked. I started the profiler while the package was running and I noticed the the lookup connection is opend and closed once per each lookup (in my case I use up to for lookups per row) which leaks my performance down .so i have these questions which I hope any one thankfully answer:
1- why can't I use the Destination connection for the lookup?
2- why the connection is open and closed each time it looks-up?

Thanks in advanceHi Eisa,

This is a forum for the next version of DTS, called Integration Services, so you probably won't get enough qualified eye balls looking at your question to answer it.
Try the microsoft.public.sqlserver.dts newsgroup instead, that should have more activity on it.

thanks,
ashsql

DTS Code works only with VS 2003 but not with VS 2005 ?

Hi;

I wanted to use the following code to run a DTS package from a 2005 Web Page code behind partial class. This code works fine in a VB 2003 module

going against SQl Srvr 2000.

Here is the code: (It initiates from a button click handler)

Dim conn As New SqlConnection("initial catalog=MY_Data;server= XYZ081552X7X441\TRSQL;integrated security=SSPI")

Dim hold1 As Integer

Dim hold_source As String = ""

Dim hold_desc As String = ""

Try

conn.Open()

Catch ex1 As Exception

MsgBox("The Test connection failed to open" & vbCrLf & ex1.Message)

End Try

MsgBox("About to create a DTS object")

Dim oPackage As New DTS.Package2Class (Compiler doesn't like this line) Type DTS.Package2Class is not defined.

Dim oStep As DTS.Step (Or this one) Type DTS.step is not defined.

oPackage.LoadFromSQLServer("XYZ81552X7X441\TRSQL", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "cpyPrinters2Excel", )

For Each oStep In oPackage.Steps

oStep.ExecuteInMainThread = True

Next

oPackage.Execute()

For Each oStep In oPackage.Steps

If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then

oStep.GetExecutionErrorInfo(hold1, , )

Else

End If

Next

oPackage.UnInitialize()

oPackage = Nothing

conn.Close()

Has Microsoft changed the DTS objects so that they work only with SQL Srv 2005 ?

Thanks for your insights.

DTS has been replaced with SSIS, so yes, plenty of things have changed.

DTS questions can be asked over at the DTS news group, if that helps: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||Thanks very much.

DTS between SQL 7 and SQL 2000

Hi!!!
I am a newbie and need know how can I use the DTS to transfer one DB from
SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
SQL_Latin1_General_CI_AS.
What I need to check during DTS operation to preserve SQL 7 code page in
order to avoid weird characters in SQL 2000?
Thanks for any information.> I am a newbie and need know how can I use the DTS to transfer one DB from
> SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
> SQL_Latin1_General_CI_AS.
> What I need to check during DTS operation to preserve SQL 7 code page in
> order to avoid weird characters in SQL 2000?
In SQL 2000, you can create a database with different collation than servers
default collation. So you can have a database on SQL 2000 with the same
collation that is present on SQL 7.0.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

DTS between SQL 7 and SQL 2000

Hi!!!
I am a newbie and need know how can I use the DTS to transfer one DB from
SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
SQL_Latin1_General_CI_AS.
What I need to check during DTS operation to preserve SQL 7 code page in
order to avoid weird characters in SQL 2000?
Thanks for any information.
> I am a newbie and need know how can I use the DTS to transfer one DB from
> SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
> SQL_Latin1_General_CI_AS.
> What I need to check during DTS operation to preserve SQL 7 code page in
> order to avoid weird characters in SQL 2000?
In SQL 2000, you can create a database with different collation than servers
default collation. So you can have a database on SQL 2000 with the same
collation that is present on SQL 7.0.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

DTS - Oracle Schema name

Hi

I have created a very simple DTS. The DTS is used to trasfer data from Oracle to MSSQL

Eg: Select empno, empname from schemaname.employee where isactive ='Y'

I want to transfer the above data from oracle to MSSQL. I am able to create the DTS and run the DTS successfully.

Right now the schema name is harcoded. But in real scenario, the schema name is known during run time. I want to pass the schema name during run time. How to do this?

I am using Transform Data task to transfer data from oracle to mssql.

any idea?

JtamilI have never done this, but have a look at the dynamic properties task. You may be able to declare the table as a variable of some sort.|||MCrowley is right...use a dynamic variable

in your dts, right click and add a dynamic properties task
then after you declare the variable just supply it to the connections

I use dynamic variables to supply my database name
You maynot need this, but I call my dts through a bat file and i supply the values:
dtsrun /S<database> /U<user> /P<password> /N<dts name> /A sourceDB:8=cpt4

sourceDB is my dynamic variable and 'cpt4' is the value (used to point to the correct database)

When you look at the connections, one should be for your sql server data and one should be your oracle connection. Just put the created variable as the value. Then when you run it just make sure you supply the value and everything should work

DSO Cell Calculation Problem

Hi
I am trying to set up a cell calculation using DSO and can not figure
out why it fails. The code is in VB6 (also tried in c#) but to no
avail.
Any help - thanks a lot
--
Dim dsoServer As New DSO.Server
Dim dsodb As Variant
dsoServer.Name = "ServerName"
dsoServer.Connect "ServerName"
Set dsodb = dsoServer.MDStores.Item("DBName") ' Name of AS Database
Dim dsoCube As DSO.Cube
Set dsoCube = dsodb.MDStores.Item("Test") ' Name of Cube
Dim dsoCommand As Variant
' Remove if found
If dsoCube.Commands.Find("CommandName") Then
dsoCube.Commands.Remove ("CommandName")
End If
Set dsoCommand = dsoCube.Commands.AddNew("CommandName")
dsoCommand.CommandType = DSO.CommandTypes.cmdCreateCellCalculation
dsoCommand.Description = "CommandDescription"
dsoCommand.Statement = "CREATE CELL CALCULATION [Test].[x] FOR
'{[Account].&[5102]}' AS '1001'"
dsoCube.Update
dsoServer.CloseServer
--
It creates a cell calulation object and saves it in the cube, but when
I look at it in analysis manager I get 2 errors...
1. Unable to pase the CREATE CELL FORMULA command. Please use DSO to
edit this command. Automation Error.
2. Unable to pase the CREATE CELL CALCULATION command. Please use DSO
to edit this command. Automation Error.
thanksSOLVED
The Statement needed "( )" around the sets in the FOR part (which would
normally indicate a tuple of the sets - which does not make sense to
me)... so it looks like this...
dsoCommand.Statement = "CREATE CELL CALCULATION CURRENTCUBE.[x] FOR
'({[Account].&[5102]})' AS '1001'"
I also used CURRENTCUBE instead of naming the cube.
No I can put the other 400 calcs in the cube and see how it performs
Hope this saves someone some time.