DTS Select using record position

Hi there, it has been a while since i have posted. I am in a
situation where I am stumped. I am learning to build a dts package
where I am connecting to a table in an AS400. This database is being
maintained by an outsourced company and therefore I can't change the
table structure or even ask them to. Anyway, this table currently has
about 104,000 records. I am building a package to check it and pull
out the most recent records and put them where they go in my SQL
Server 2000 tables. The only way I can think of to get the most
recent records is to use a global variable in the package to remember
the record count and then get those records from that record position
on. Problem is, I have no idea how I would go about selecting records
from a record position. Does anybody have any ideas or should I be
using a different approach? There are no time stamps to work from. I
was told that the AS400 records, including updates, are appended to
the table, which is why I thought this approach made sense. I would
truly appreciate any help.phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0403031553.6e5f7de7@.posting.google.com>...
> Hi there, it has been a while since i have posted. I am in a
> situation where I am stumped. I am learning to build a dts package
> where I am connecting to a table in an AS400. This database is being
> maintained by an outsourced company and therefore I can't change the
> table structure or even ask them to. Anyway, this table currently has
> about 104,000 records. I am building a package to check it and pull
> out the most recent records and put them where they go in my SQL
> Server 2000 tables. The only way I can think of to get the most
> recent records is to use a global variable in the package to remember
> the record count and then get those records from that record position
> on. Problem is, I have no idea how I would go about selecting records
> from a record position. Does anybody have any ideas or should I be
> using a different approach? There are no time stamps to work from. I
> was told that the AS400 records, including updates, are appended to
> the table, which is why I thought this approach made sense. I would
> truly appreciate any help.

How would you know where the recorded position is? Does the AS/400
have a row number field, or something similar? And what happens if
rows are deleted in the source table?

Assuming that the AS/400 table has a primary key, then you might be
able to create a linked server from your MSSQL server, and then use a
query like this to insert all the rows which don't exist:

insert into dbo.MyTable (col1, col2, ...)
select col1, col2, ...
from AS400...Table t
where not exists (select * from dbo.MyTable mt
where mt.PrimaryKey = t.PrimaryKey)

Updates would be more difficult, as you'd need to have some way of
identifying the rows which have been modified. You might want to
consider looking at heterogeneous replication for this, so that
changes on the AS/400 are automatically replicated to the SQL Server,
although you would probably need a third-party tool for this.

Simon

DTS Scheduled Time

How can I tell when a DTS package is scheduled to run?
If I right click on the name, and select "Schedule Package", it brings up
default data (12:00 am Daily), and I KNOW that's not the scheduled time.
ThanksThat is the mechanism by which you set up a scheduled job via SQL Agent. By alter the parameters of the screen to the schedule you desire and clicking on OK, the GIU will build a scheduled job under the AGent with the same name as the DTS package.|||Now I see it. Thanks

DTS Oracle Select

Hi there,m

I have a DTS with an Oracle Connection. I transfers some data from oracle Db to SQL Server DB. I need convert my Oracle query into a parmerized query, i mean that the field [T1."DELV_SHIP_DATE_KEY"] can receive the dates like parameters.

I'd try to put:
T1."DELV_SHIP_DATE_KEY" between ? and ?

but it show me the next error when i try to set the parameter info:
Provider cannot deriver parameter information and
Setparameterinfo has not been called

Any idea??,
Maritzita

I use this select statement (for Oracle):
select T1."DELV_ID" "DELV_Delv Id",
T1."CUST_ID" "DELV_Cust Id",
T1."SHIP_CODE" "DELV_Ship Code",
T1."WHSE_ID" "DELV_Whse Id",
(T1."DELV_SHIP_TIME"*1.0e0)/100 "DELV_Delv Ship Time",
T1."DELV_SHIP_DATE_KEY" "DELV_Delv Ship Date Key",
T2."DELVI_ITEM_NO" "DELVI_Delvi Item No",
T2."ORD_ID" "DELVI_Ord Id",
T2."ORDI_ITEM_NO" "DELVI_Ordi Item No",
T2."DELVI_QTY" "DELVI_Delvi Qty",
T2."PROD_ID" "DELVI_Prod Id",
T2."DELVI_TOT_WGT" "DELVI_Delvi Tot Wgt",
T2."DELVI_TYPE_CODE" "DELVI_Delvi Type Code",
T3."ADR_CMPY_NAME1" "ADR_Adr Cmpy Name1",
T4."PROD_DESC_TEXT1" "PROD_Prod Desc Text1",
T4."PROD_ALT_CONV_FACTOR" "PROD_Prod Alt Conv Factor",
T5."ORDI_QTY" "ORDI_Ordi Qty",
T5."ORDI_ASM_GEN_ITEM_NO" "ORDI_Ordi Asm Gen Item No",
T5."ORDI_IS_UNIT_WGT" "ORDI_Ordi Is Unit Wgt",
T4."PROD_MKT_GROUP_CODE" "PROD_Prod Mkt Group Code"
from "VPEMGR"."DELV" T1,
"VPEMGR"."DELVI" T2,
"VPEMGR"."ADR" T3,
"VPEMGR"."ORDI" T5,
"VPEMGR"."PROD" T4
where T1."DELV_ID"=T2."DELV_ID" and
T1."ADR_ID"=T3."ADR_ID" and
T2."ORD_ID"=T5."ORD_ID" and
T2."ORDI_ITEM_NO"=T5."ORDI_ITEM_NO" and
T2."PROD_ID"=T4."PROD_ID" and
T2."DELVI_TYPE_CODE"='IQ' and
T1."DELV_SHIP_DATE_KEY" between '20030501' and '20031231' and
T1."DELV_SHIP_TIME"/100 between 0.00 and 24.00If I understand the question correctly, I think that you will have better luck with OPENQUERY.

hmscott|||I saw this solution on help from SQL, but i culdn't understand it so much. Can u tell me where i can find more information about it? and if i use this funcionality, how i have to do for pass this results of my query to my SQl database without using a data transformation task?

I have many quetions about it.

tks,
Maritzita

Originally posted by hmscott
If I understand the question correctly, I think that you will have better luck with OPENQUERY.

hmscott|||Look under OPENQUERY in SQL Books On Line. They give an example that specifically shows an Oracle Server. I think that the only thing you will have to do is build your Query String (with the substituted parameters) as a variable. I'm not positive, however, that this will work. Example:

DECLARE @.SQL Varchar(4000)

SELECT @.SQL =
'SELECT Columns
FROM MyTable
WHERE MyDate BETWEEN TO_DATE(' + '12/4/2003' + ') AND
TO_DATE(' + '12/5/2003' + ')'
GO

SELECT *
FROM OPENQUERY(OracleSvr, @.SQL)
GO

DTS no error, but no data either...

Hey all - Quick question,
I'm trying to use DTS to move SQL server data to Oracle.

I select the table(s) I want to move, use the oracle oledb driver (i test connected/credentials successfully) and wait...process goes through without a hitch, but when I go to oracle and do a select * I've got nothing.

Any insight into this?Did you get any errors?

How much data?

I would bcp the data out and use SQLLOader on the Oracle side...

But that's just me...|||Does DTS automatically commit the inserts?|||Seems like DTS is working the problem was it is putting quotations around the tablse...ie
"tablespacename"."tablename" is there any way to disable the quotations around the tables?? If i create the table with a script in oracle - it does the same thing.|||Answered my own question. Thanks anyway!

DTS migration wizard, where does it put migrated version?

From SQL Server Management Studio I right click on Management/Legacy/DTS and select Migration Wizard and enter a SQL Server Source and Destination. A list of DTS packages from the source server is displayed and I select one of the DTS packages and give the destination a unique name. The wizard runs and says it's successful. I can't find the new SSIS version of the DTS package with the new name that I've given it, however. Where does it put the new migrated copy of the DTS package.

Thanks,

John

In Sql Management Studio, under the Object Explorer window in the Connect drop down choose Integration Services, and it will show you all stored packages on that instance.|||Thanks, this helps. Now I'd like to find out how to edit the migrated version. How to I get to it from BI Dev Studio?|||

All of the stored packages will usually be in ~\Program Files\Microsoft SQL Server\90\DTS\Packages

DTS Help

I have around 400 different Select -- Insert queries getting data from 200+
tbles. Data in each table can vary from 1 million to 3 million. All the data
is being inserted in the same table.
What's the best way to do this? Insert statement obviously wouldn't work. Is
there a way I can use all the queries inside one DTS package?XXX,
BPC out and BCP or BULK INSERT in might be a faster option. Also, do you
need to bring over all of the records or can you restrict the data being
transfered i.e., date range? One other thing, you might consider a data
archival policy and indexing strategies to make your queries run faster.
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:OXyD%23P6vFHA.2792@.tk2msftngp13.phx.gbl...
>I have around 400 different Select -- Insert queries getting data from 200+
>tbles. Data in each table can vary from 1 million to 3 million. All the
>data is being inserted in the same table.
> What's the best way to do this? Insert statement obviously wouldn't work.
> Is there a way I can use all the queries inside one DTS package?
>|||No, I have a client who does registeration and Profile building in
PepopleSoft.
They are building a new system, where data from several tables (200) gets
merged into 3 tables. I have to get the initial load before the new system
goes live, after that this will be done differently (through PeopleSoft).
To answer your question all the data needs to be pulled out. I was hoping
there was something easier than BCP...
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eyU93T6vFHA.708@.TK2MSFTNGP10.phx.gbl...
> XXX,
> BPC out and BCP or BULK INSERT in might be a faster option. Also, do you
> need to bring over all of the records or can you restrict the data being
> transfered i.e., date range? One other thing, you might consider a data
> archival policy and indexing strategies to make your queries run faster.
> HTH
> Jerry
> "XXX" <sa@.nomail.com> wrote in message
> news:OXyD%23P6vFHA.2792@.tk2msftngp13.phx.gbl...
>|||Before you do the initial loads be sure to increase the transaction log
space and max setting. You might also want to begin with a small load and
monitor the t-log usage using DBCC SQLPERF(LOGSPACE) to ensure adequate log
space. Be sure to perform a backup and shrink the t-log when the loads are
complete (or as you increment the loads if needed).
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:upR0zk6vFHA.464@.TK2MSFTNGP15.phx.gbl...
> No, I have a client who does registeration and Profile building in
> PepopleSoft.
> They are building a new system, where data from several tables (200) gets
> merged into 3 tables. I have to get the initial load before the new system
> goes live, after that this will be done differently (through PeopleSoft).
> To answer your question all the data needs to be pulled out. I was hoping
> there was something easier than BCP...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eyU93T6vFHA.708@.TK2MSFTNGP10.phx.gbl...
>