imports microsoft.sqlserver.dts.pipeline does not work

I have been trying to follow/implement the examples in the following help topics (thanks to Jamie for these links).

Building Packages Programmatically

(http://msdn2.microsoft.com/en-us/library/ms345167.aspx)

Connecting Data Flow Components Programmatically

(http://msdn2.microsoft.com/en-us/library/ms136086.aspx)

The problem I am having is that MainPipe is not recognized as a valid type in my Script task, even though I have the imports statements that are listed in the example. I get the message "Error 30002: Type 'MainPipe' is not defined". The other and related problem is that when I type "imports microsoft.sqlserver.dts", the intellisense offers only two choices: {}Runtime and {}Tasks. I don't see any choice for Pipeline. Can anyone tell what I am missing? It seems to be some kind of configuration/installation issue, but I have no idea how to resolve it. I have tried this on 3 different machines, with both the RTM SQL 2005 standard edition, and with SP2 installed, all with the same result. Any help is appreciated

Here is my code:

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.wrapper

Imports Microsoft.SqlServer.Dts.

Public Class ScriptMain

Public Sub Main()

'

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

New Microsoft.SqlServer.Dts.Runtime.Package()

Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Make sure you have added a reference (under Project..References) to the Microsoft.SqlServer.DTSPipelineWrap.dll.

|||That was it. Thanks for your help.

importing and archiving

I was wondering how i could use dts to import accessfiles and then archive
them to another folder. I've read some examples on sqldts.com but i still
can't figure it out.

Basically this is what i want:

- import an accessfile which has a name like this <companyname>_<today's
date>.mdb. The importfolder is called in my case d:\import

- for updating some fields, the dts should use the company's name from the
accessfile and check that with a lookuptable to translate the name into a
int value.

- after some processing, the dts should place the file to another folder. In
my case it is d:\archive

If someone can help me with this, i would be very greatful.

--

----------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.comI tend to avoid DTS. I prefer to use xp_cmdshell (to perform Command
line commands) and linked servers. In this case, use xp_cmdshell to
FTP the files down. Create a linked server to the Access MDB.
Process. Then use xp_cmdshell to move the file to the archive folder.

//FTP SP
CREATE PROCEDURE FTP
(
@.ftpserver varchar(50)='myserver' ,
@.ftpuser varchar(50)='myusername' ,
@.ftppwd varchar(50)='mypassword' ,
@.batchfile varchar(100)='\\myserver\path\ftpcmd.txt' ,
@.destfile varchar(100)='\\myserver\path\mydestfile.txt' ,
@.sourcefile varchar(50)='mysourcefile',
@.mode varchar(3)='get' -- get | put
)
as
DECLARE @.cmd varchar(1000)

SELECT @.cmd = 'echo ' + 'open ' + @.ftpserver + ' > ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + @.ftpuser + '>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + @.ftppwd + '>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd

SELECT @.cmd = 'echo ' + @.mode + ' ' + @.sourcefile + ' ' + @.destfile + '
>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + 'quit' + ' >> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'ftp -s:' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'del ' + @.batchfile
EXEC master..xp_cmdshell @.cmd|||"Ezekil" <ezekil@.lycos.com> wrote in message
news:41ffd97b$0$71093$cd19a363@.news.wanadoo.nl...
> I was wondering how i could use dts to import accessfiles and then archive
> them to another folder. I've read some examples on sqldts.com but i still
> can't figure it out.
> Basically this is what i want:
> - import an accessfile which has a name like this <companyname>_<today's
> date>.mdb. The importfolder is called in my case d:\import

Generate the current Access filename and assign it to a package global
variable in an ActiveX step, then use a Dynamic Properties task to set the
Access Connection properties.

> - for updating some fields, the dts should use the company's name from the
> accessfile and check that with a lookuptable to translate the name into a
> int value.

It depends if you're going to load the Access data into MSSQL or not - if
so, then an Execute SQL task could join the tables or call a stored proc.
Otherwise, ActiveX will work again.

> - after some processing, the dts should place the file to another folder.
> In my case it is d:\archive

Another ActiveX step, or just an Execute Process task to move the file.

Simon