Thursday, March 29, 2012

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?