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