DTS Fully process dimensions code

Using the following code to fully process dimension in a catalog on SQL Server 2000.

for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process processFull
Next

However this appears to do an incremental process of dimension rather than full process. (I would expect the cubes to be unprocessed after a full process.

Whats wrong with the code.
Cheers.
Mark.for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process (1)
Next

Have tested this and believe it to work. Still do not understand why above did not work.|||I assume you are using vbscript to process. If so you need to reference to the Constant "processFull" so that it applies it correctly. Otherwise it always just does it as a process default.sql

DTS Fully process dimensions code

Using the following code to fully process dimension in a catalog on SQL Server 2000.

for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process processFull
Next

However this appears to do an incremental process of dimension rather than full process. (I would expect the cubes to be unprocessed after a full process.

Whats wrong with the code.
Cheers.
Mark.for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process (1)
Next

Have tested this and believe it to work. Still do not understand why above did not work.|||I assume you are using vbscript to process. If so you need to reference to the Constant "processFull" so that it applies it correctly. Otherwise it always just does it as a process default.

DTS FTP Task: Is it possible to get beyond the root directory of the ftp site

Hi, I have the following problem
I am trying to download files from a ftp site using FTP task in DTS. The files reside in a subfolder of the root directory but I don't see a way to get there. Any ideas how to go around this?
ThanksI don't know what version of SQL server you are using, but in 2000 the FTP task allows you to specify the directory to retrieve the file from. After you enter the id and password you can go to the 'Files' tab and navigate through the directory structure.

DTS FTP Task "Unable to connect to internet"

I created a DTS FTP task to connect to an FTP site. The problem I'm having is the folder I want to access you cannot see under the Files Tab. So if I put the ftp site/subdirectory in the FTP Site box, I get the error "Unable to connect to internet! Please check the source site information, your username and password"

If I go to the FTP site/subdirectory in IE all the files show up fine. I believe this is limitation of DTS, but I would like to hear from you all first.

To see what error I'm talking about:
Create a DTS FTP task
Use: ftp.microsoft.com/bussys/ with no username or password
Click the Files Tab

Normally you can just put the server name in, and see all the folders/files, but the way my FTP site is set up I cannot see the folder I want, I can only access it.What do you see in the Address field of the browser when you click on this site? Something like this?

ftp://<user_id>:<password>@.ftp_site.com

If this is the case then that's the reason why you can see files and folders using your browser vs. DTS package designer.

You NEED to specify user_id and password to access FTP sites unless they are set up to allow anonymous access.

Exception to it would be a setting on an FTP site where browsing is not allowed while accessing a specific file is. Consult with the FTP site admin.

DTS FTP problem

Is there any way to enter file names in the DTS FTP task even if the files are not presently there in the remote machine?I always implement FTP via t-sql e.g.
http://www.nigelrivett.net/s_ftp_PutFile.html

But in dts you can access the object model in an activex script and change the filename.

DTS from XML

Does anyone know how to create a dts package that will take a XML document and import the contents to a new table in sql.
I have looked in the tasks of DTS and cannot find the xml connection.
Any pointers to some samples would be great.
ThanksConsider upgrading to SQL Server 2005, which has extended support for XML including data import through the Integration Services application. (Interestingly, Integration Services does NOT export XML. Go figure...).sql

Dts From Vb

Hi all,
I tryed posting this in VB section but no response, so I'm trying here.
How would I make my DTS connection async. When I exeute SQL DTS package from VB, the program locks up until the DTS is done, so how would I make it async for it to not lock up?
Here is my Sub:
Public Sub ExecuteEDIPackage(FileName As Variant)

Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String

sMessage = sMessage & "<<<Executing import on " & FileName & ">>>" & vbCrLf & vbCrLf

Set oPKG = New DTS.Package

' Set Parameter Values
sPackageName = "EDIPackage"

' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

sMessage = sMessage & "<<<Import on " & FileName & " is done.>>>" & vbCrLf & vbCrLf

End SubUse a timer to start another thread to run the package then your thread can carry on executing.|||Is there a way to do it without another thread? Like an async call with ADO?

DTS from stored procedure.

This issue has come up in our office.
Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?
blindmanI think this one has been addressed before here (but I can't find it right now either). I think it basically involved using sp_start_job to start a job linked to the DTS task.

Regards,

hmscott

Originally posted by blindman
This issue has come up in our office.

Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

blindman|||http://www.houseoffusion.com/cf_lists/index.cfm/method=messages&forumid=6&threadid=227

for your reference, I copy the code in the above link and post it here

the user executing this package must have execute rights to the sp_OA* sps
in master.

CREATE PROC <SP Name> as

DECLARE @.hr int, @.oPKG int

EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

--Loading the Package:
-- DTSSQLServerStorageFlags :
-- DTSSQLStgFlag_Default = 0
-- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @.hr = sp_OAMethod @.oPKG,
'LoadFromSQLServer("<servername>", "<user>", "<password>", 0, , , , "<DTS
Package Name>")',
NULL
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

--Executing the Package:
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN
END

--Cleaning up:
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END|||Why don't you want to use xp_cmdshell?|||Originally posted by blindman
This issue has come up in our office.

Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

blindman

the holy book also lists how to do the same from VB|||Thanks everybody.

blindman

DTS from SQL Server to DB2

I tried to copy tables from SQL Server 2000 to DB2 UDB by using DTS
package through ODBC, but always got an error that says:

"The number of failing rows exceeds the maximum specified.
[IBM][CLI Driver] CLI0150E Driver not capable, SQLSTATE=S1C00"

However, I can copy tables inversely from DB2 to SQL Server without
problem, and I can also copy tables to other non DB2 databases through
DTS.

DB2 Client is properly installed on SQL Server side, and can access to
DB2 Database successfully.

When I run DTS package, it can talk to DB2 database, and create a target
table (i.e. no connection problem), but failed to add data into the
table.

The problem appears after we upgraded Windows 2000 Server Service Pack
from SP2 to SP4. According to the error message, it seems that DB2 CLI
Driver is not happy about something.

Can anybody shed some light on the issue?

Thanks a lot

Jian

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORGI am having a similar problem.

When using DTS designer when I click on the destination tab of the
data pump I get this error:
Error Description: Unspecified error
[IBM][CLI Driver][DB2/AIX64] SQL1131N DARI (Stored Procedure) process
has been terminated abnormally. SQLSTATE=38503

But the DB2 table comes up fine.

Then when I run the dts package I get this error:
The number of failing rows exceeds the maximum specified. [IBM][CLI
Driver] CLI0150E Driver not capable. SQLSTATE=S1C00

Does anyone have any ideas how to resolve this?

Thanks,
Sarah Larson|||I have just completed a migration to a new SQL Server and this problem
has reared its ugly head. I can continue to import data to SQL Server
from DB2, I can run an 'Execute SQL Task' to delete data from a DB2
table, but any attempt to insert fails instantly with 'The number of
failing rows exceeds the maximum amount specified.

The 'old' server was at W2K Advanced Server SP2 with SQL Server 2000
Ent at SP3 and ran exports to DB2 without issue.

The new server is W2K Advanced Server SP4 with SQL Server 2000 Ent
also at SP3.

It does seem that the W2K SP4 could be the cause of the problem and if
anyone has a solution to this issue I should be very grateful to know.

Regards

Clare Hilditch|||I had the same issue and resolved it by changing the connection type in DTS from the regular ODBC driver to IBM OLE DB Provider for DB2. Everything worked after that change.

DTS From SQL Server 2000 to Btrieve

Hi,

This is my 1st time out here so please bear with me.

I am trying to transfer data from a SQL Server 2000 DB to a Btrieve file. I have everything set up and used the Import/Export Utility to create the DTS Package. Everything seems to wotk fine until I hit a field in the SQL Server 2000 DB that has a signle quote (') in a name field. Btrieve seems to bomb on that record. If I take the single quote out, it works fine until the next name with a single quote. What can I do to get these records imported?

Thanks much,
Rayuse the following function

ltrim(replace(columnname,'''',''))

you are replacing a single quote in a column with a null and you trimming that with ltrim function.......|||Thanks for the info, but I actually need the single quote. I have set my transformation to change the signle quote into a ~ (tilde). My C program the changes the ~ back to a single quote when updating the main data file from the temp one SQL Server updated.

Ray Fantasia

DTS from MS SQL to Excel Spreadsheet Issue

I'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. Im using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named Results. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. Ive Googled this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.

Error I See:

Running DTS package with passed variables
...
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnStart: Drop table Results Step

DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003027

Error Detail Records:

Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027

Any ideas would be great.

Thanks.
Jimright click on the spreadsheet and go to properties. what do you see?

the application dev team spent a week tossing something similar to this with Access for one their internal processes. I pointed it out a couple minutes after they asked me. I spent an hour laughing at my team of geniuses.

I am a joy to work with.|||Thanks for the idea. I thought of the read only flag as soon as I saw the read only error in the error log. Unfortunately that wasnt it. The issue was within the DTS package. If you open up the connection properties and click the top option to New Connection in an attempt to re-name the object, you actually create a new object with a new name leaving the older one in tacked but hidden in the background. This can only be seen if you go into Disconnected Edit under connections. There was an object names Connection1 and Connection2. These old connection objects where pointing to the development environment where the functional ID that was used to run the DTS did not have access to. Oops

Again thanks for the idea.

Jimsql

DTS from mapped drive problem

Hey guys

I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.

CREATE PROC spExecuteDTS
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS

SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Check Pkg Errors
EXEC @.ret=spDisplayPkgErrors @.oPKG

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

RETURN @.ret
GO

that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?

thank youAre you getting an error message?|||Are you getting an error message?

*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.|||Use the UNC path|||Does the login you are executing the OA_ stored procs as have permission to execute them?|||Wow

What to say

Usually people use DTS to avoid sprocs...but you're combing the 2

Why?

What does the sproc do?

Just load a flat file?

Why not just use bcp and xp_cmdshell?

DTS from Informix database

Hi,

I want to transfer data from an Informix database using DTS. What driver do I need to use for that? What are the settings for the Data Source Name? Has anyone of you accomplished DTS from Informix to SQL Server?

Let me know if you have any ideas that you can share.

Thank you.

VivekYou can get the informix oledb provider from the informix client sdk cd - then use that provider in your dts package.|||There is a known bug in that package. Check out IBM website for more information on that. It will save you a lot headaches.|||Which version and bugs ...|||Also, as with any informix product - make sure you read the release notes.|||How much will I have to pay for this driver that you are talking about?|||Also what version of the Informix Client SDK should I get hold of? I have no experience in Informix.|||Here is the link to ibm/informix client sdk (includes release notes):

link (http://www-306.ibm.com/software/data/informix/tools/csdk/)

DTS from Hosting

I am trying to export from an SQL Server on a hosting company to my own computer using DTS. I'm new to SQL Server manipulations like this so please go easy on me...

I can't seem to manage to do this properly - If I select "Copy tables & data only" it will not copy the identity constraints and primary keys. If I select "copy all objects" it starts giving me permission issues which I cannot seem to resolve, because I guess the tables on the hosting server are owned by my username up there, but when they come to my computer they are orphant?

Isn't there an easy way to do this? It's a very simple database, tables and that's it, not even any stored procedures.Ah , you have made a common mistake of having - you - rather than dbo owning objects.

I would export all the data to the new machine like you are doing, then change ownership on EVERY object to be dbo.

Yes, its long winded and a hassle...but this shows the reason why its so easy to have broken ownership chains if dbo doesnt own all objects.

Cheers,

SG|||Thanks - I have actually done that meanwhile and it solved the problem.
However - I have no control under what user the objects are created in the remote database as it is hosted - and I have to login in the user they supplied me, therefore all objects will be created under that user name.

Or am I mistaken? Can I still override that and force the remote DB to create new objects under dbo?|||The user dictates the ownership of the object.

Unless you manually run a script to change owner to dbo - or - the user is sa level ( NOT recommended!!!! )...

Cheers,

SG.

DTS from Excel to SQL?

I tried to use DTS for copying a sheet from Excel to SQL. For some
reason, the last column shows up error saying source's column 5 buffer
is too big.. and failed it.. is there anything that I need to watch
out?ebug@.hotmail.com (Kelvin) wrote in message news:<191e0546.0404261422.4bad806@.posting.google.com>...
> I tried to use DTS for copying a sheet from Excel to SQL. For some
> reason, the last column shows up error saying source's column 5 buffer
> is too big.. and failed it.. is there anything that I need to watch
> out?

Does this apply to your case?

http://support.microsoft.com/defaul...kb;EN-US;281517

Simon

DTS from Excel problems

I am trying to import an Excel spreadsheet into SQL Server. Why can I impor
t
an empty Excel column only if it is a text field? If the field is a number
or date field, I get a conversion error.
i.e.
create table labresults
(
analyte varchar(50),
sampledate datetime,
result numeric(19,6)
)
The excel worksheet has 3 columns:
analyte (formatted as text)
sampledate (formatted as date)
result (formatted as number)
If the first 7 rows in the analyte column are empty, the file imports fine,
but if the first 7 rows of sampledate or result are empty, I get a
"Conversions invalid for data types" error.
Anybody have any good suggestions on how to fix this issue?
Thanks.
ArcherHi
Sounds like it is related to:
http://www.sqldts.com/default.aspx?254
John
"bagman3rd" wrote:

> I am trying to import an Excel spreadsheet into SQL Server. Why can I imp
ort
> an empty Excel column only if it is a text field? If the field is a numbe
r
> or date field, I get a conversion error.
> i.e.
> create table labresults
> (
> analyte varchar(50),
> sampledate datetime,
> result numeric(19,6)
> )
> The excel worksheet has 3 columns:
> analyte (formatted as text)
> sampledate (formatted as date)
> result (formatted as number)
> If the first 7 rows in the analyte column are empty, the file imports fine
,
> but if the first 7 rows of sampledate or result are empty, I get a
> "Conversions invalid for data types" error.
> Anybody have any good suggestions on how to fix this issue?
> Thanks.
> Archer
>sql

DTS from excel file (excel filename is different everyday)

Good Day to all,
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.You will need to create a variable in your DTS package for the file name, and then construct the filename dynamically.|||Hi blindman,
I can't seem to figure out how will I do that.
Could you be more specific, pls.
Thanks for taking the time to answer my queries.
God Bless.|||Look here:

http://www.sqldts.com/default.aspx?234|||use the following DTS steps for this

1) create a Global variable of name say "aa" of string type
2) add a ActiveX Task where u assign the value of global variable from system date. something like
DTSGlobalVariables("aa").Value = "d:\Data" & "0" & month(date()) & day(date()) & year(date()) & ".xls"
3) add a Dynamic Property task. select the Excel connection and assign the "data Source" to that global variable.
4) place a work flow so that the execution sequence is ActiveX>>Dynamic Prop>>Other Steps that u already have.|||Hi,
I can't seem to get it yet. I am presented w/ so many information from all the websites and help files that I am reading, and I end up more confused. :eek:
I'm a newbie in SQL and I need instructions for dummies. :D

Here's what I did:
1.) I created a global variable named gVarPath through the DTS Package Properties.
2.) I'm adding now a "ActiveX Script" Task in the DTS Designer. Here's my script:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Main = DTSTaskExecResult_Success
DTSGlobalVariables("gVarPath").Value="D:\PROJECTS\Attendance-Excel\" & RIGHT('0'+ RTRIM(CAST(MONTH(GETDATE()-2) AS CHAR)),2) & RIGHT('0'+ RTRIM(CAST(DAY(GETDATE()-2) AS CHAR)),2) & RIGHT(YEAR(GETDATE()-2),2) & "_ALB.xls"
End Function

There's a syntax error. I will debug this later.

3.) I'm adding a "Dynamic Properties" task.
Question: Where can I select the excel connection? And how can I assign the data source to my global variable?
4.) And how can I place a workflow.

Please help :o|||Hi upalsen,

I got it already!
I followed your instructions. Many thanks to you. :)

Now, I have another question.:D
I need to import data from 24 excel files everyday. Excel filenames are like these:

100206_AAA
100206_BBB
100206_CCC
up to
100206_XXX

wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...100206_XXX).
How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.

Here's how my ActiveX Script looks like:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* *********************

Option Explicit

Function Main()
Dim vDay, vMonth, vYear, vDate

vDay=RIGHT(RTRIM("0" & DAY(DATE()-2)),2)
vMonth=RIGHT(RTRIM("0" & MONTH(DATE()-2)),2)
vYear=RIGHT(YEAR(DATE()-2),2)
vDate=vMonth & vDay & vYear

DTSGlobalVariables("gVarPath").Value=vDate & "_AAA.xls"

Main = DTSTaskExecResult_Success
End Function

Thank you so much... :)
God Bless.|||i am not sure if those branch codes r really fixed and hardcoded as AAA, BBB etc? or they will come from another table? assuming they are hard coded, u can ...
create another global variable, say vCounter. start with vCounter=1. add another ActiveX step. put it at the end of the existing workflow. add the following code

Function Main()
if vCounter <= 24 then
vCounter = vCounter+1
DTSGlobalVariables.Parent.Steps ("<NAME_OF_STEP1>").ExecutionStatus = DTSStepExecStat_Waiting
end if

Main = DTSTaskExecResult_Success
End Function

in your starting ActiveX script consider vCounter and write code to get branch code for each value

if vCounter = 1 then
BrCode = "AAA"
elseif vCounter .....

.......
DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"|||Hi upalsen,

Yup. The branch codes are fixed and will be hardcoded.
Following your instructions, I created another global variable named "gVarCounter". How can I referenced "gVarCounter" in my Dynamic Properties Task? In my first global variable "gVarPath", I referenced it by assigning the data source of the excel connection to it.

And another question, how will I know the ("<NAME OF STEP1>")?
Here's my ActiveX script:
IF gVarCounter<=24 then
gVarCounter=gVarCounter+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
END IF

I saw it in the Dynamic Property Task under Steps. Am I correct?

Thank you so much. :)|||u need not reference gVarCounter in your dynamic property task. all that u need to do is use gVarCounter in preparing the value of your previous gVarPath variable. like below. and dynamic prop will still use only gVarPath.

if gVarCounter = 1 then
BrCode = "AAA"
elseif gVarCounter=2 then
BrCode = "BBB"
.....

DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"

yes, u r right. step names r listed in dynamic prop under "steps" heading.

dts from command line

if a user runs a dts package from a command line say something like this
C:\>dtsrun /s ServerName /u username /p P1l0t /n DTS_Package
using that /p password switch is the passwork example above P1l0t saved off
somewhere in a log file?
same question for if the command fails.
thanksNo...it won't report or log the password that was used.
-Sue
On Tue, 11 Oct 2005 15:05:05 -0700, "jason"
<jason@.discussions.microsoft.com> wrote:

>if a user runs a dts package from a command line say something like this
>C:\>dtsrun /s ServerName /u username /p P1l0t /n DTS_Package
>using that /p password switch is the passwork example above P1l0t saved off
>somewhere in a log file?
>same question for if the command fails.
>thanks|||thanks sue
"Sue Hoegemeier" wrote:

> No...it won't report or log the password that was used.
> -Sue
> On Tue, 11 Oct 2005 15:05:05 -0700, "jason"
> <jason@.discussions.microsoft.com> wrote:
>
>

DTS from AS 400 to Sql server

Hi DBA's:

I need to transfer data from my source database which is Client Access iSeries AS 400
running DB 400 to my database in SQL Server 2000. Here is what I need to do -

There are files in the Production Enviornment on the DB 400. I need to connect to these files from my SQL Server 2000 database using DTS Wizard and tranfer these files into the tables of my SQL Server 2000 database. Can someone walk me through with the options on the source side of the DTS that I need to choose in order to connect to my AS 400 and get the data.

Thanks in anticipation.I'm assuming you mean DB2, not DB 400...

Look up sp_addlinkedserver in BOL

DTS from 2005 to 2000

Hi All,
I am trying to move data (nightly) from a SQL Server 2005 machine to a
SQL Server 2000 box. Since the backup/restore solution is not an
option. I had only the choice of DTS.
So the problem that I am facing is :
SQL Server 2005 machine ( call it X )
SQL Server 2000 machine ( call it Y )
I start the Import/Export task on Y choose all the tables to import
from X.
Save it as Local package and Schedule it. And then I get the error of
Failed to make the Share OMWCIZC. So I check my package I am using
Windows Authenticitaion for both boxes with the same user ( the user is
in the local Administrator group of both Computers) and has
serveraccess for Sql Severs and the interested Databases.
But I still get the error of server access for the user. Cannot get
info from domain.
So I perform a mock test with few tables and schedule that and it
works.
But When I try my nightly 40 tables transfer; it fails.
Is there something that can be done remedy this issue ?
Is there another way ?
Thank you all for any comments and helpful tipsTry to do one be one on success, tough thing
What i could understand is your mock test survived, its becasue those tables
dont have any sql 2005 added features so migration happend without issue.
When you tried a bulk it failed.
So better option those which gets migrated do it that way and others do
manually.
FYI,
http://expertanswercenter.techtarge...1150843,00.html
Once you do it, share your experience.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Shyam Madhavan" wrote:

> Hi All,
> I am trying to move data (nightly) from a SQL Server 2005 machine to a
> SQL Server 2000 box. Since the backup/restore solution is not an
> option. I had only the choice of DTS.
> So the problem that I am facing is :
> SQL Server 2005 machine ( call it X )
> SQL Server 2000 machine ( call it Y )
> I start the Import/Export task on Y choose all the tables to import
> from X.
> Save it as Local package and Schedule it. And then I get the error of
> Failed to make the Share OMWCIZC. So I check my package I am using
> Windows Authenticitaion for both boxes with the same user ( the user is
> in the local Administrator group of both Computers) and has
> serveraccess for Sql Severs and the interested Databases.
> But I still get the error of server access for the user. Cannot get
> info from domain.
> So I perform a mock test with few tables and schedule that and it
> works.
> But When I try my nightly 40 tables transfer; it fails.
> Is there something that can be done remedy this issue ?
> Is there another way ?
> Thank you all for any comments and helpful tips
>sql

DTS from 2005 to 2000

Hi All,
I am trying to move data (nightly) from a SQL Server 2005 machine to a
SQL Server 2000 box. Since the backup/restore solution is not an
option. I had only the choice of DTS.
So the problem that I am facing is :
SQL Server 2005 machine ( call it X )
SQL Server 2000 machine ( call it Y )
I start the Import/Export task on Y choose all the tables to import
from X.
Save it as Local package and Schedule it. And then I get the error of
Failed to make the Share OMWCIZC. So I check my package I am using
Windows Authenticitaion for both boxes with the same user ( the user is
in the local Administrator group of both Computers) and has
serveraccess for Sql Severs and the interested Databases.
But I still get the error of server access for the user. Cannot get
info from domain.
So I perform a mock test with few tables and schedule that and it
works.
But When I try my nightly 40 tables transfer; it fails.
Is there something that can be done remedy this issue ?
Is there another way ?
Thank you all for any comments and helpful tipsTry to do one be one on success, tough thing :(
What i could understand is your mock test survived, its becasue those tables
dont have any sql 2005 added features so migration happend without issue.
When you tried a bulk it failed.
So better option those which gets migrated do it that way and others do
manually.
FYI,
http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1150843,00.html
Once you do it, share your experience.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Shyam Madhavan" wrote:
> Hi All,
> I am trying to move data (nightly) from a SQL Server 2005 machine to a
> SQL Server 2000 box. Since the backup/restore solution is not an
> option. I had only the choice of DTS.
> So the problem that I am facing is :
> SQL Server 2005 machine ( call it X )
> SQL Server 2000 machine ( call it Y )
> I start the Import/Export task on Y choose all the tables to import
> from X.
> Save it as Local package and Schedule it. And then I get the error of
> Failed to make the Share OMWCIZC. So I check my package I am using
> Windows Authenticitaion for both boxes with the same user ( the user is
> in the local Administrator group of both Computers) and has
> serveraccess for Sql Severs and the interested Databases.
> But I still get the error of server access for the user. Cannot get
> info from domain.
> So I perform a mock test with few tables and schedule that and it
> works.
> But When I try my nightly 40 tables transfer; it fails.
> Is there something that can be done remedy this issue ?
> Is there another way ?
> Thank you all for any comments and helpful tips
>

DTS from 2000 to 2005

I migrated a DTS package from my 2000 test server to my 2005 test server and
I seem to be lost... The log file shows that everything converted fine,
where will I find that package? PLEASE HELP ME!!!1 THANK YOU>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
In SQL Server Management Studio, in Object Explorer, connect to the
Integration Services. Then chack whether the paclage is registered in the
local msdb database. If it is somewhere on the file system, search for the
*.dtsx files with Windows Explorer.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Im SSMS click on connect and choose Server Type-- Integration Services
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:ujXNdIX8GHA.1188@.TK2MSFTNGP05.phx.gbl...
>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>|||something just doesn't seem right here >> I have SQL2000 installed as the
default instance, and 2005 as a named instance, when I register integration
services I only see the server name (which is by default the default
instance) I do not see, and can not find the 2005 named instance. So I
tried to work with that and I registered it and drilled down to the MSDB
container, when I open that container I get this error >>
TITLE: Microsoft SQL Server Management Studio
--
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
The SQL server specified in SSIS service configuration is not present or is
not available. This might occur when there is no default instance of SQL
Server on the computer. For more information, see the topic "Configuring the
Integration Services Service" in Server 2005 Books Online.
Client unable to establish connection
Encryption not supported on SQL Server. (MsDtsSrvr)
--
BUTTONS:
OK
--
<< I am just guessing here but is this because I have the wrong SQL server
registered in integration services '
ANYbody have any ideas, pleases... Thank you!!
========================================================"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> >I migrated a DTS package from my 2000 test server to my 2005 test server
> >and I seem to be lost... The log file shows that everything converted
> >fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
> In SQL Server Management Studio, in Object Explorer, connect to the
> Integration Services. Then chack whether the paclage is registered in the
> local msdb database. If it is somewhere on the file system, search for the
> *.dtsx files with Windows Explorer.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>|||I'm a bit confused but I think your issue is that you are
using a named instances for 2005 and possibly didn't modify
the configuration file as indicated in the Books Online
topic so that SSIS would work with the named instance.
Locate the file MsDtsSrvr.ini.xml in your 2005 installation
path Program Files\Microsoft
SQL Server\90\DTS\Binn
The server name entry for a named instance should be
something along the lines of:
<ServerName>YourServer\YourInstanceName</ServerName>
-Sue
On Tue, 17 Oct 2006 09:39:53 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>something just doesn't seem right here >> I have SQL2000 installed as the
>default instance, and 2005 as a named instance, when I register integration
>services I only see the server name (which is by default the default
>instance) I do not see, and can not find the 2005 named instance. So I
>tried to work with that and I registered it and drilled down to the MSDB
>container, when I open that container I get this error >>
>TITLE: Microsoft SQL Server Management Studio
>--
>Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
>For help, click:
>http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>--
>ADDITIONAL INFORMATION:
>The SQL server specified in SSIS service configuration is not present or is
>not available. This might occur when there is no default instance of SQL
>Server on the computer. For more information, see the topic "Configuring the
>Integration Services Service" in Server 2005 Books Online.
>Client unable to establish connection
>Encryption not supported on SQL Server. (MsDtsSrvr)
>--
>BUTTONS:
>OK
>--
><< I am just guessing here but is this because I have the wrong SQL server
>registered in integration services '
>ANYbody have any ideas, pleases... Thank you!!
>========================================================>"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
>message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
>> >I migrated a DTS package from my 2000 test server to my 2005 test server
>> >and I seem to be lost... The log file shows that everything converted
>> >fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>> In SQL Server Management Studio, in Object Explorer, connect to the
>> Integration Services. Then chack whether the paclage is registered in the
>> local msdb database. If it is somewhere on the file system, search for the
>> *.dtsx files with Windows Explorer.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor, www.SolidQualityLearning.com
>> Anything written in this message represents solely the point of view of
>> the sender.
>> This message does not imply endorsement from Solid Quality Learning, and
>> it does not represent the point of view of Solid Quality Learning or any
>> other person, company or institution mentioned in this message
>>
>

DTS from 2000 to 2005

I migrated a DTS package from my 2000 test server to my 2005 test server and
I seem to be lost... The log file shows that everything converted fine,
where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
In SQL Server Management Studio, in Object Explorer, connect to the
Integration Services. Then chack whether the paclage is registered in the
local msdb database. If it is somewhere on the file system, search for the
*.dtsx files with Windows Explorer.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
|||Im SSMS click on connect and choose Server Type-- Integration Services
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:ujXNdIX8GHA.1188@.TK2MSFTNGP05.phx.gbl...
>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>
|||something just doesn't seem right here >> I have SQL2000 installed as the
default instance, and 2005 as a named instance, when I register integration
services I only see the server name (which is by default the default
instance) I do not see, and can not find the 2005 named instance. So I
tried to work with that and I registered it and drilled down to the MSDB
container, when I open that container I get this error >>
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
ADDITIONAL INFORMATION:
The SQL server specified in SSIS service configuration is not present or is
not available. This might occur when there is no default instance of SQL
Server on the computer. For more information, see the topic "Configuring the
Integration Services Service" in Server 2005 Books Online.
Client unable to establish connection
Encryption not supported on SQL Server. (MsDtsSrvr)
BUTTONS:
OK
<< I am just guessing here but is this because I have the wrong SQL server
registered in integration services ?
ANYbody have any ideas, pleases... Thank you!!
================================================== ======
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> In SQL Server Management Studio, in Object Explorer, connect to the
> Integration Services. Then chack whether the paclage is registered in the
> local msdb database. If it is somewhere on the file system, search for the
> *.dtsx files with Windows Explorer.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>
|||I'm a bit confused but I think your issue is that you are
using a named instances for 2005 and possibly didn't modify
the configuration file as indicated in the Books Online
topic so that SSIS would work with the named instance.
Locate the file MsDtsSrvr.ini.xml in your 2005 installation
path Program Files\Microsoft
SQL Server\90\DTS\Binn
The server name entry for a named instance should be
something along the lines of:
<ServerName>YourServer\YourInstanceName</ServerName>
-Sue
On Tue, 17 Oct 2006 09:39:53 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:

>something just doesn't seem right here >> I have SQL2000 installed as the
>default instance, and 2005 as a named instance, when I register integration
>services I only see the server name (which is by default the default
>instance) I do not see, and can not find the 2005 named instance. So I
>tried to work with that and I registered it and drilled down to the MSDB
>container, when I open that container I get this error >>
>TITLE: Microsoft SQL Server Management Studio
>--
>Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
>For help, click:
>http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
>--
>ADDITIONAL INFORMATION:
>The SQL server specified in SSIS service configuration is not present or is
>not available. This might occur when there is no default instance of SQL
>Server on the computer. For more information, see the topic "Configuring the
>Integration Services Service" in Server 2005 Books Online.
>Client unable to establish connection
>Encryption not supported on SQL Server. (MsDtsSrvr)
>--
>BUTTONS:
>OK
>--
><< I am just guessing here but is this because I have the wrong SQL server
>registered in integration services ?
>ANYbody have any ideas, pleases... Thank you!!
>================================================= =======
>"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
>message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
>

DTS from 2000 to 2005

I migrated a DTS package from my 2000 test server to my 2005 test server and
I seem to be lost... The log file shows that everything converted fine,
where will I find that package? PLEASE HELP ME!!!1 THANK YOU>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
In SQL Server Management Studio, in Object Explorer, connect to the
Integration Services. Then chack whether the paclage is registered in the
local msdb database. If it is somewhere on the file system, search for the
*.dtsx files with Windows Explorer.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Im SSMS click on connect and choose Server Type-- Integration Services
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:ujXNdIX8GHA.1188@.TK2MSFTNGP05.phx.gbl...
>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>|||something just doesn't seem right here >> I have SQL2000 installed as the
default instance, and 2005 as a named instance, when I register integration
services I only see the server name (which is by default the default
instance) I do not see, and can not find the 2005 named instance. So I
tried to work with that and I registered it and drilled down to the MSDB
container, when I open that container I get this error >>
TITLE: Microsoft SQL Server Management Studio
--
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
--
ADDITIONAL INFORMATION:
The SQL server specified in SSIS service configuration is not present or is
not available. This might occur when there is no default instance of SQL
Server on the computer. For more information, see the topic "Configuring the
Integration Services Service" in Server 2005 Books Online.
Client unable to establish connection
Encryption not supported on SQL Server. (MsDtsSrvr)
--
BUTTONS:
OK
--
<< I am just guessing here but is this because I have the wrong SQL server
registered in integration services '
ANYbody have any ideas, pleases... Thank you!!
========================================
================
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> In SQL Server Management Studio, in Object Explorer, connect to the
> Integration Services. Then chack whether the paclage is registered in the
> local msdb database. If it is somewhere on the file system, search for the
> *.dtsx files with Windows Explorer.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>|||I'm a bit confused but I think your issue is that you are
using a named instances for 2005 and possibly didn't modify
the configuration file as indicated in the Books Online
topic so that SSIS would work with the named instance.
Locate the file MsDtsSrvr.ini.xml in your 2005 installation
path Program Files\Microsoft
SQL Server\90\DTS\Binn
The server name entry for a named instance should be
something along the lines of:
<ServerName>YourServer\YourInstanceName</ServerName>
-Sue
On Tue, 17 Oct 2006 09:39:53 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:

>something just doesn't seem right here >> I have SQL2000 installed as the
>default instance, and 2005 as a named instance, when I register integration
>services I only see the server name (which is by default the default
>instance) I do not see, and can not find the 2005 named instance. So I
>tried to work with that and I registered it and drilled down to the MSDB
>container, when I open that container I get this error >>
>TITLE: Microsoft SQL Server Management Studio
>--
>Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
>For help, click:
>http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
>--
>ADDITIONAL INFORMATION:
>The SQL server specified in SSIS service configuration is not present or is
>not available. This might occur when there is no default instance of SQL
>Server on the computer. For more information, see the topic "Configuring th
e
>Integration Services Service" in Server 2005 Books Online.
>Client unable to establish connection
>Encryption not supported on SQL Server. (MsDtsSrvr)
>--
>BUTTONS:
>OK
>--
><< I am just guessing here but is this because I have the wrong SQL server
>registered in integration services '
>ANYbody have any ideas, pleases... Thank you!!
> ========================================
================
>"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
>message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
>

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 from .NET (C#)?

I have a C# app that loads and runs a DTS package. It works great on my dev system but when I move it to the production system (which has SQL Server 2000 installed) it doesn't work. As soon as my app runs, it throws the exception:

"File or assembly name Interop.DTS, or one of its dependencies, was not found."

I've manually done: "regsvr32 dtspkg.dll" which succeeds but doesn't fix the problem.

Any ideas?Nevermind, I figured it out. DTS is a COM object and .NET generates a glue DLL: "Interop.DTS.dll".

I just needed to copy this DLL as well as my .exe

cool...|||Yeah!

DTS Foxpro to SQL 2000

I want to send parameters to a DTS (Foxpro to SQL 2000). I got it figured out with a DTS(Exel to SQL 2000) but i can't find a way to get the thing to work for foxpro.

I want to do this because i have a stored procedure that calls the xp_cmdshell function and then by running the dts and just passing a parameter to it for the old order i want in my new application database...

Please any help would be appreciated.....all you need is the oledb foxpro driver for the version of foxpro you are using installed on your sql server and then you should be ablle to select foxpro as your data source.|||I can select Foxpro as my datasource. The dts is working 100 % except for the fact that i cannot pass parameters to query on the transformation task.

If i do Select * from foxprotable where uniqueid = 1234

the data transoforms correctly, but if i should say

Select * from foxtables where uniqueid = :parameter

then i get message that i am missing an operand ??|||sorry for the :parameter it should be a : with a parameter

dts Foxpro MEMO Field

how can I import memo field from Foxpro table'
Many Thanks in Advance!!You can transform it to a large varchar() column or a text column.
Which of these to use would depend on the average and max lengths of the
memo field.
* in vfp
select max(len(theMemoField)) as max_len from theTable
If' the max length is over 8000, you'll have to use text. If it's <=8000
_and you can say with assurance_ that the max length will stay <=8000,
I'd tend to go with a large varchar column.
SQLbeginner wrote:

>how can I import memo field from Foxpro table'
>Many Thanks in Advance!!
>|||Thanks Trey.
"Trey Walpole" wrote:

> You can transform it to a large varchar() column or a text column.
> Which of these to use would depend on the average and max lengths of the
> memo field.
> * in vfp
> select max(len(theMemoField)) as max_len from theTable
> If' the max length is over 8000, you'll have to use text. If it's <=8000
> _and you can say with assurance_ that the max length will stay <=8000,
> I'd tend to go with a large varchar column.
>
> SQLbeginner wrote:
>
>|||Thanks very much for the helps
"SQLbeginner" wrote:

> how can I import memo field from Foxpro table'
> Many Thanks in Advance!!|||I was able to import the MEMO field using the Foxpro ODBC driver, however,
not all information from the memo fields are imported. For example, a recor
d
has 11 lines of information, it would only import part of it and some record
s
didn't even get any information transferred. On the SQL database table, I
use 'text' as the data type.
Does anyone has a solution?
Many Thanks in Advance.
Nancy
"SQLbeginner" wrote:

> how can I import memo field from Foxpro table'
> Many Thanks in Advance!!

DTS for insert/update

I'm new to DTS. I read some docs before adventuring into this matter.
I still haven't found in all the docs I read if there is some "built-in" DTS
task or function or wathever, to do a mixed "insert/update" import from a
source, giving a unique field as primary key.
I'll try to be more specific. The problem I would like to solve is this:
I have a source file (csv) but it could be any source. I must check this
file for all the records and compare them with the ones in the db (giving a
unique field as a way of checking), so that all the records that already
exist, are UPDATED, and the others are INSERTED.
I guess this is one most common task to accomplish, when you have a local
based application that you regularly update and then you want to export the
data to another "slave" application without using replication. But even if
this sounds to me like a common task, I found no "buil-in" function for that
in DTS. I read something about "lookups" but it don't know if it's related
somehow.. it wasn't very clean.
Thanks in advance for suggestions.

--
:: Massimiliano Mattei
:: Project Leader
:: E.xtranet V.irtual A.pplication
:: www.evagroup.net :: www.commy.bizMassimiliano,
Yes, that is a very common task. The best way to handle this is to
import directly into a temp table and then run a procedure to
check/insert/update your data into a production table.
If you need a specific example to follow, we have one on our website at
www.TechnicalVideos.net. $19.99 buys all of our videos for 3 months. Our
DTS videos will have an expert show you step by step how to do this.

Hope that helps,
Best Regards,
Chuck Conover
www.TechnicalVideos.net

"xMANIGHTx" <manight@.ufologia.net> wrote in message
news:c2aav5$noi$1@.lacerta.tiscalinet.it...
> I'm new to DTS. I read some docs before adventuring into this matter.
> I still haven't found in all the docs I read if there is some "built-in"
DTS
> task or function or wathever, to do a mixed "insert/update" import from a
> source, giving a unique field as primary key.
> I'll try to be more specific. The problem I would like to solve is this:
> I have a source file (csv) but it could be any source. I must check this
> file for all the records and compare them with the ones in the db (giving
a
> unique field as a way of checking), so that all the records that already
> exist, are UPDATED, and the others are INSERTED.
> I guess this is one most common task to accomplish, when you have a local
> based application that you regularly update and then you want to export
the
> data to another "slave" application without using replication. But even if
> this sounds to me like a common task, I found no "buil-in" function for
that
> in DTS. I read something about "lookups" but it don't know if it's related
> somehow.. it wasn't very clean.
> Thanks in advance for suggestions.
> --
> :: Massimiliano Mattei
> :: Project Leader
> :: E.xtranet V.irtual A.pplication
> :: www.evagroup.net :: www.commy.biz|||Thanks Chuck!
Your videos are really a good idea!! I checked the demo one and they are
kool... maybe I'm going to subscribe, even if I'm italian.
I must say that the language is well understandable even if a bit "American"
:)
You say that the best pratice is to import first all teh data in a temp
table (maybe to use the bulk inserta advantages) but what if the records are
really too much? Say I have to import 100.000 records or more.. Would you
use a #temp_table, just a "memory" table or a real table?
I don't know if SQL Server writes down #temp_tables just like it does with
real ones, otherwise the server would have to mantain 100.000 records in
memory...
Wich type of table would you suggest? Maybe SQL Server has memory
optimization routines wich write to disk data of #tables not to hold them
only in the RAM?
Do you think this is the best method? DTS is for data trasformation so it's
ODD it doesn't have something that lets you
import/check/transform/insert-update data without writing a specific
procedure for that.

--
:: Massimiliano Mattei
:: Project Leader
:: E.xtranet V.irtual A.pplication
:: www.evagroup.net :: www.commy.biz|||Massimiliano,
Sorry. I know we are too American. We really need to get out more.
My explanation was not very good. By "temp" table, I mean a table that
is not a production table. We can call this a "working" table. So, I would
use a real table to temporarily store the data for your bulk upload, not an
actual temp table that gets stored in memory. I would do it this way even
if you only have a few records. Then, my DTS package would look like this:

- delete all records from the working table
- upload my data file to my working table
- run stored procedure to check/insert/update data row by row from the
working table to my production table

This way, we can execute each step one at a time, and browse the working
table to make sure the data went into each field correctly.
Hope this helps,
Chuck Conover
www.TechnicalVideos.net

"xMANIGHTx" <manight@.ufologia.net> wrote in message
news:c2f4ku$ccj$1@.lacerta.tiscalinet.it...
> Thanks Chuck!
> Your videos are really a good idea!! I checked the demo one and they are
> kool... maybe I'm going to subscribe, even if I'm italian.
> I must say that the language is well understandable even if a bit
"American"
> :)
> You say that the best pratice is to import first all teh data in a temp
> table (maybe to use the bulk inserta advantages) but what if the records
are
> really too much? Say I have to import 100.000 records or more.. Would you
> use a #temp_table, just a "memory" table or a real table?
> I don't know if SQL Server writes down #temp_tables just like it does with
> real ones, otherwise the server would have to mantain 100.000 records in
> memory...
> Wich type of table would you suggest? Maybe SQL Server has memory
> optimization routines wich write to disk data of #tables not to hold them
> only in the RAM?
> Do you think this is the best method? DTS is for data trasformation so
it's
> ODD it doesn't have something that lets you
> import/check/transform/insert-update data without writing a specific
> procedure for that.
> --
> :: Massimiliano Mattei
> :: Project Leader
> :: E.xtranet V.irtual A.pplication
> :: www.evagroup.net :: www.commy.bizsql

DTS for Import Export TO And From EXCEL

Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:

> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>

DTS for Excel problem

Hello

I have a problem and need some assistance.
I have a SS that I am loading into a SQL2000 database
I am using DTS for this.
Previously uses used an ADP file to load the spreadsheet.

My problem is that the ADP file uses the ACTIVESHEET for its import.
And the in the DTS you must select the SS you wish to insert.
And the SS name is not standard.

I am trying to use the ADP file and rename the worksheet
any idea how to do this or any idea howelse I can accomplish my goal.

TIA :confused:I will try to help but what does SS and ADP mean? With this explained I can try to help you. :rolleyes:

DePrins
:)|||SS = Excel Spreadsheet within a workbook
ADP = MIcrosoft Access file

I have a spreadsheet with a non specific name
I transfer that file to a shared drive and need to rename the ActiveSheet there. Using access or SQL Server|||Thanks, i will look into it.
:)

DTS for Dummies

Hi,
i am new to DTS and need to work with DTS.
Would you happen to have a Web link that contains a DTS for dummies online tutorial.
Is there a web site for step-by-step on how to use DTS ?
i will be thankful for your reply.
Regards,
Babbuhttp://www.sqldts.com/|||B user

Thank you for your reply.
i was looking for the web site.

Regards,
Babbu

DTS for 100 text files

Hello DBA's:

I want to upload 100 text files to a single table on SQL Server 2000.
Records from these text files would be selected on the basis of a where clause.

What would be the best way of accomplishing this? Using DTS, I can do only one file at a time. Is there a faster approach.

Thanks

VivekTill the time you get a better solution you can use some file concatenation utility to join all those files into a single one and do the data load using DTS.

http://www.jddesign.f2s.com/concat-s.htm|||Does this utility join files OR append them one after the other. I want to append the records not join them? Let me know.|||it appends them|||Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one with a cursor and then archive them after it's loaded...

What format are the files in? Are they the same structure?|||sounds like a job for perl (every admins friend) or heaven forbid vbscript.|||You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?|||Originally posted by Brett Kaiser
Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one with a cursor and then archive them after it's loaded...

What format are the files in? Are they the same structure?

Brett ... no way you can accomplish this without a cursor ...

I challenge everyone to make a set based solution for the same

LOL ... am working on a similar procedure ... need to uload files daily of the form LocationCD_Extracttype_yyyymmdd.txt ... tab seperated files .. any ideas other than bcp and dts ..|||It is a one-time event. I have no experience in shell scripting, unfortunately.
Originally posted by rnealejr
You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?|||How about this?

Create 2 DTS packages. An Outer and an Inner Package.

The Outer package has an ActiveX script task the uses the FSO to indentify all the files in a directory, then for each file set the text file connection of the inner package to the path of the text file identified in the Outer package. Then call the Inner package which just has a text file connection and a DB connection with a trasnformdata task.
Loop through all the files repeating the above.

This would be very automated and fairly quick depending on the size of the text files. And actually pretty easy to implement.

Stevesql

DTS Fixed Field length File Limitations

Hi,
I am trying to upload a fixed field text file to a sqlserver table using the DTS wizard. The txt file has 111 columns and the total length of a single row is 5897. The problem is when I use the wizard to specify the starting and ending of each column, its not allowing me to specify the columns beyond the position 4095.
Is there a limitation on this? if so is there a work around ? to solve this.
Any help on this is truly appreciated.

Thanks much. :)I've never encountered this problem but then I have never had a file quite that wide....

Personally what I would do is write a quick wee app or ActiveX Script to slice the file in half and then do the import in two stages... probably doesn't help much but it's the best suggestion I can give you.

dts fired from sp

Hello,
Is it possible to fire a DTS from within a stored procedure?
tia,
bob mcclellanIf you know the command line to pass to dtsrun, and you can use xp_cmdshell,
then sure:
exec master..xp_cmdshell 'dtsrun.exe ... '
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:u2uNrUY4FHA.2552@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Is it possible to fire a DTS from within a stored procedure?
> tia,
> bob mcclellan
>|||Select 'You''re fired' from table where job = 'DTS'
Ok, I know. It's lame but I have my reasons..
It's Friday, I'm tired, I can't wait to get home and my first grandson was
born yesterday.
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:u2uNrUY4FHA.2552@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Is it possible to fire a DTS from within a stored procedure?
> tia,
> bob mcclellan
>|||Great.
I just needed a place to start in BOL.
Thanks much Aaron.
bob.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23mYIbYY4FHA.1396@.TK2MSFTNGP12.phx.gbl...
> If you know the command line to pass to dtsrun, and you can use
> xp_cmdshell, then sure:
> exec master..xp_cmdshell 'dtsrun.exe ... '
>
>
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:u2uNrUY4FHA.2552@.TK2MSFTNGP10.phx.gbl...
>|||Congrats Ray.
Enjoy the wend with your new grandson.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eY3hVaY4FHA.700@.TK2MSFTNGP15.phx.gbl...
> Select 'You''re fired' from table where job = 'DTS'
> Ok, I know. It's lame but I have my reasons..
> It's Friday, I'm tired, I can't wait to get home and my first grandson was
> born yesterday.
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:u2uNrUY4FHA.2552@.TK2MSFTNGP10.phx.gbl...
>

DTS file name to include System Date/Time

We have an application group that wants to pull date from SQL Server and write it to text file on the server. They want the file format to be 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a second set...and so on. The '12100' is fixed, but the rest of the file name will always have to include the system date/time. Is there an easy way to do this within a DTS package (when writing to the output file)?

I would really appreciate help on this. Thank you.Hi

You can set a global variable to the fixed part at the start of the process. This should help http://www.sqldts.com/default.aspx?200 although you want to set a global variable rather than a local one.

John
"steven virnig" <pezguy@.mn.rr.com> wrote in message news:o1ovd.114089$ye4.34831@.twister.rdc-kc.rr.com...
We have an application group that wants to pull date from SQL Server and write it to text file on the server. They want the file format to be 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a second set...and so on. The '12100' is fixed, but the rest of the file name will always have to include the system date/time. Is there an easy way to do this within a DTS package (when writing to the output file)?

I would really appreciate help on this. Thank you.

DTS File Created After Stored Proc executed now what?

I'm trying to fire a DTS package through a stored procedure.

After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.

How would I use the DTS file to update my data.

Thanks,

RB

RB,

There is no direct way to do this via a SP. In my experience, I've seen three ways to work around this:

1) Use xp_cmdshell. This simply executes a command in the command shell. Usefull if you already have a batch script running your DTS.

2) Use OLE. You can create and work with Com objects via the sp_OA* methods. You will need to create the DTS objects and run properties from them.

3) Use a scheduled Job. Saw this recently, the idea is to create a new scheduled job that runs your DTS, then run the job, then delete the scheduled job.

None are pretty, but they all work.

-- Alex

DTS file

I find some .DTS file and I want to know what it is doing , but I don't
know how to open them, any one know how to do this? thanks a lotAnswered in .dts. Please do not multipost.
--
Jacco Schalkwijk
SQL Server MVP
"Catelin Wang" <Catelin Wang@.discussions.microsoft.com> wrote in message
news:50232336-1890-42F8-9139-348BEFB871CD@.microsoft.com...
>I find some .DTS file and I want to know what it is doing , but I don't
> know how to open them, any one know how to do this? thanks a lotsql

DTS Failure

I have an existing SQL Server database that we want to move to a bigger
dedicated server. When I DTS from the old to the new, after about 45
minutes I get the error that Bulk transfer failed. There is not further
explanation.
It appears that the tables and data was moved, but none of the non clustered
indexes, stored procedures or jobs were copied. I have tried the transfer
twice withe the same results.
What can I do to get this to work?
TIA,
Mike
I would recommend that you give up on DTS and either backup the database, copy the backup to the other server and restore using the with move option or if you can take the database off-line either use sp_detach_db or stop the SQL Server and copy the .mdf
and .ldf files to the other server and use sp_attach_db.
"Michael Beck" wrote:

> I have an existing SQL Server database that we want to move to a bigger
> dedicated server. When I DTS from the old to the new, after about 45
> minutes I get the error that Bulk transfer failed. There is not further
> explanation.
> It appears that the tables and data was moved, but none of the non clustered
> indexes, stored procedures or jobs were copied. I have tried the transfer
> twice withe the same results.
> What can I do to get this to work?
> TIA,
> Mike
>
>
|||Thanks for the suggestion. The detach and attach works great.
Mike
"Norman" <Norman@.discussions.microsoft.com> wrote in message
news:7A9FDCDB-715A-4123-90AF-7B3C182DFFA1@.microsoft.com...
> I would recommend that you give up on DTS and either backup the database,
copy the backup to the other server and restore using the with move option
or if you can take the database off-line either use sp_detach_db or stop the
SQL Server and copy the .mdf and .ldf files to the other server and use
sp_attach_db.[vbcol=seagreen]
> "Michael Beck" wrote:
clustered[vbcol=seagreen]
transfer[vbcol=seagreen]

DTS Failure

I have an existing SQL Server database that we want to move to a bigger
dedicated server. When I DTS from the old to the new, after about 45
minutes I get the error that Bulk transfer failed. There is not further
explanation.
It appears that the tables and data was moved, but none of the non clustered
indexes, stored procedures or jobs were copied. I have tried the transfer
twice withe the same results.
What can I do to get this to work?
TIA,
MikeI would recommend that you give up on DTS and either backup the database, copy the backup to the other server and restore using the with move option or if you can take the database off-line either use sp_detach_db or stop the SQL Server and copy the .mdf and .ldf files to the other server and use sp_attach_db.
"Michael Beck" wrote:
> I have an existing SQL Server database that we want to move to a bigger
> dedicated server. When I DTS from the old to the new, after about 45
> minutes I get the error that Bulk transfer failed. There is not further
> explanation.
> It appears that the tables and data was moved, but none of the non clustered
> indexes, stored procedures or jobs were copied. I have tried the transfer
> twice withe the same results.
> What can I do to get this to work?
> TIA,
> Mike
>
>|||Thanks for the suggestion. The detach and attach works great.
Mike
"Norman" <Norman@.discussions.microsoft.com> wrote in message
news:7A9FDCDB-715A-4123-90AF-7B3C182DFFA1@.microsoft.com...
> I would recommend that you give up on DTS and either backup the database,
copy the backup to the other server and restore using the with move option
or if you can take the database off-line either use sp_detach_db or stop the
SQL Server and copy the .mdf and .ldf files to the other server and use
sp_attach_db.
> "Michael Beck" wrote:
> > I have an existing SQL Server database that we want to move to a bigger
> > dedicated server. When I DTS from the old to the new, after about 45
> > minutes I get the error that Bulk transfer failed. There is not further
> > explanation.
> >
> > It appears that the tables and data was moved, but none of the non
clustered
> > indexes, stored procedures or jobs were copied. I have tried the
transfer
> > twice withe the same results.
> >
> > What can I do to get this to work?
> >
> > TIA,
> > Mike
> >
> >
> >

DTS Failure

I have an existing SQL Server database that we want to move to a bigger
dedicated server. When I DTS from the old to the new, after about 45
minutes I get the error that Bulk transfer failed. There is not further
explanation.
It appears that the tables and data was moved, but none of the non clustered
indexes, stored procedures or jobs were copied. I have tried the transfer
twice withe the same results.
What can I do to get this to work?
TIA,
MikeI would recommend that you give up on DTS and either backup the database, co
py the backup to the other server and restore using the with move option or
if you can take the database off-line either use sp_detach_db or stop the SQ
L Server and copy the .mdf
and .ldf files to the other server and use sp_attach_db.
"Michael Beck" wrote:

> I have an existing SQL Server database that we want to move to a bigger
> dedicated server. When I DTS from the old to the new, after about 45
> minutes I get the error that Bulk transfer failed. There is not further
> explanation.
> It appears that the tables and data was moved, but none of the non cluster
ed
> indexes, stored procedures or jobs were copied. I have tried the transfer
> twice withe the same results.
> What can I do to get this to work?
> TIA,
> Mike
>
>|||Thanks for the suggestion. The detach and attach works great.
Mike
"Norman" <Norman@.discussions.microsoft.com> wrote in message
news:7A9FDCDB-715A-4123-90AF-7B3C182DFFA1@.microsoft.com...
> I would recommend that you give up on DTS and either backup the database,
copy the backup to the other server and restore using the with move option
or if you can take the database off-line either use sp_detach_db or stop the
SQL Server and copy the .mdf and .ldf files to the other server and use
sp_attach_db.[vbcol=seagreen]
> "Michael Beck" wrote:
>
clustered[vbcol=seagreen]
transfer[vbcol=seagreen]

DTS Failure

Whats happens when moving a sql server database to another sql server database and the DTS fails. Does it copy half the data or it doesn't copy any data??

No it does not that is why we usually back up the current copy before implementing a new one if roll back may be needed. Hope this helps|||Oh Ok, Thank u so much, that was my interview question.

DTS fails on Stored Procedure warning

Hi,
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Mo
you can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>
sql

DTS fails on Stored Procedure warning

Hi,
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Moyou can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>

DTS fails coes it refuses to fill the unique id colum

THe unique Id column is set to identity seed. Yet when transfering 1500 records it fails to stuff them all becuase it says there is a NULL in unique id column.

"Cannot insert the value NULL into column UID column does not allow"

Yet in the transformation screen I am setting the UID column to <ignore> meaning I do not have that value in my source and I am wanting the destination table to do it's thing and incrememnt by 1 the identity seed.

Is this common?

THe source and destination tables are in the same databes.

When you click on "Transform" uncheck where it says "Enable Identity Insert"

|||

Thank you very much.

It worked.

I am not sure I "logically" understand that though.

|||

When you have enable Identity Insert checked in your DTS wizard what it does is says "I want you to insert the identity field that is coming from the table I'm copying from". This is useful when you have a truncated table and you want to keep your identity field values for data integrity.

Now comes the however.

When you set to Ignore the Identity column in your transformation you were essentially telling it to insert null values, and Identity columns can't be null. Had you not selected that then you would have most likely received the error that says you can't insert duplicates into your identiy column as it violates the unique constraint.

DTS fails at customer site with "Too many columns", works locally

I am having the most baffling problem with DTS... :confused:

I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Step Error code: 80043013
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID:0

Step Execution Started: 11/16/2004 6:37:51 PM
Step Execution Completed: 11/16/2004 6:39:39 PM
Total Step Execution Time: 107.415 seconds
Progress count in Step: 515000

The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.

The only microsoft KB article referencing anything resembling my problem is
http://support.microsoft.com/default.aspx?scid=kb;en-us;292588
but this does not hold because I am not specifying fixed width, but rather comma delimited.

If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.This may not be helpful...but have you considered just using a stored procedure instead?|||What happens to that row when you try to import the file into access? If you create an extra column at the top of the flat file, it should insert whatevers in that column for the five offending rows right? Once you get it into a table query it with a NOT NULL. It might give you a clue as to what the offending characters are.

If your stuck with the file then you might just have to use the insertfail phase to make the pump task skip to the next record when it finds an offending row. Read up on multiphase to find out exactly how you'd do this.

Sorry can't help you more.|||Modify the DTS package to use an Execute Process Task and then use bcp.

-or-Use Execute SQL Task and the Bulk Insert Transact-SQL command.|||...the file imports fine here with the exact same DTS package, so I don't want to modify it to address a problem that isn't really the problem. IN other words, there is nothing to indicate there is anything actually wrong with the data itself - no whitespaces, no bad characters or problem causing characters, no datatype mismatch, nothing; it looks just like the last row. Here are the rows before and after as well as the one that failed:

737,10/15/2004,09:11:39,114,15536,1
737,10/15/2004,09:11:49,114,18408,1
737,10/15/2004,09:11:54,714,18024,1

I am not using column 5, but all the others. From last month to this month the number of offending rows increased from 1 to 7, so I don't want to start throwing away data that for all other intensive purposes looks good automatically in case it starts multiplying.

Since it works here but fails there, it has to be something environmental, maybe with character sets or??|||Generating files from SAS...Like from a mainframe?

I betcha you got some low values [CHAR('00') ] going on...

I know you don't want to alter your process, but I ALWAYS create a staging environment and load the data to it, then audit the data to look for problems...then I move the data in after I verify it...

And it's all done with a stored procedure|||Thanks for the tip. I am looking into how these "low values" occur and how these EBCDIC to ASCII conversions can get hung up. I'm sure the answer lies somewhere in there.

Well, the front end application will run a custom DTS package, but not a custom SP. At least the staging need is moot, because it rolls the whole thing back if one record fails...