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 text files - DTS help needed

Hi all

Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.

File is >1m rows and >200 columns and is defined by a proprietory (i.e. not
bcp produced) format specification of the form

Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53

and so on.

Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so I
don't have to click 200 times (with all the ensuing errors I could make).

Any help greatly appreciated.

Cheers
Robbut surely there's a way to get DTS to read from a format file so I don't

Quote:

Originally Posted by

have to click 200 times (with all the ensuing errors I could make).

Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"JCC" <gofyself@.wrong.address.comwrote in message
news:fdE1h.31731$w07.17227@.newsfe6-win.ntli.net...

Quote:

Originally Posted by

Hi all
>
Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.
>
File is >1m rows and >200 columns and is defined by a proprietory (i.e.
not bcp produced) format specification of the form
>
Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53
>
and so on.
>
Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so
I don't have to click 200 times (with all the ensuing errors I could
make).
>
Any help greatly appreciated.
>
Cheers
Rob
>

|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:SYG1h.947$m54.525@.newssvr14.news.prodigy.com. ..

Quote:

Originally Posted by

Quote:

Originally Posted by

>but surely there's a way to get DTS to read from a format file so I don't
>have to click 200 times (with all the ensuing errors I could make).

>
Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.
>
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP

I have looked into this - but as I said, I don't want to mess around with
bcp if I can avoid it - I had no end of problems with bcp a while back.

It looks like I can't avoid it though!

Cheers
Rob

Importing Text File to SQL

HI Guys,

I am doing the following to read the data in a text file and inserting it into SQL.

1) Open db connection
2) Open Text File
3) loop through text file all along inserting each row into the db
4) close the text file
5) close the db connection

However, the text file has over 400 rows/lines of data that need to be inserted into the db. Each line in the text file is a row in the db. At anyrate, the above script times out. Is there a better, faster way to do this? I can't use Bulk Insert due to permissions previlages.

Thanks in Advance!DTS would do it. If you can get a DTS pakage set up and a procedure to run it, you can do it by uploading the file to a place the database can see it, and then run the proc that runs the DTS job.

400 rows isn't much. I do a similar thing with up to 100,000 rows. Not an ideal thing, but it was right for the situation. I had to set the timeouts longer, which is what you can do also.

You need to set a longer timeout in three places.

1) server.scripttimeout
2) connection timeout
3) command timeout

Google for examples. 400 is not a lot, so it's not a bad way to do it. If you don't expect that to grow much, just set the timeouts longer and be done with it.|||Hi, I'm new in the programming. I am developing an application that uses text file as input for the data. Therefore, i need to import the text file to SQL server before I could use it. Do you mind if you could share the sample of your code for importing the text file and convert it to sql.

Importing Table/Column Comments for a Data Dictionary

Hello,
I need to create a Data Dictionary with the following contents:
Table Name
Column Name
Data Type
Comments for each Table
Comment for each column for each table
The first three are easy by using information_schema.columns. However,
there are no comments in our database right now.
Is there an easier way to import comments to the database besides using
sp_addextendedproperty? I do have the Erwin tool at my disposal to use.
Any other third party tools might help here?
The issue here is that I have over 500 tables and over 2500 columns to
add comments for and I am looking for the easiest and fastest way to
do this.
Thanks in advance for any help.
LarryFor extended properties, you may want to take a look at AGS
SQL Server Extended Property Editor.
http://www.ag-software.com/ags_SSEPE_index.aspx
-Sue
On 26 Mar 2004 11:33:09 -0800, lschwei@.oaot.com (Larry)
wrote:

>Hello,
>I need to create a Data Dictionary with the following contents:
>Table Name
>Column Name
>Data Type
>Comments for each Table
>Comment for each column for each table
>The first three are easy by using information_schema.columns. However,
>there are no comments in our database right now.
>Is there an easier way to import comments to the database besides using
>sp_addextendedproperty? I do have the Erwin tool at my disposal to use.
>Any other third party tools might help here?
>The issue here is that I have over 500 tables and over 2500 columns to
>add comments for and I am looking for the easiest and fastest way to
>do this.
>Thanks in advance for any help.
>Larrysql

Importing data from web page into SQL Server table

Hi,
I have to find a way to do the following : a company provides for my company a web page displaying data separated with ;
I need to automatically update a table in SQLServer with this data, ie delete the old data, read the web page containing new data as text, and insert these data into the table.
I would actually prefer not to trigger this import process "by hand" ; if there's a solution to schedule it ...
Thanks for your help
JohannYes you can get the content of the page dynamically by using the server.execute then put the content in a text file and read the stream|||Thanks for your help
The problem is that the page containing the data is not mine. I have no control over it, and it's on another server.
All I have is the url to display that page in a browser. So, can I direct a script to that url and make my script read the content ?
Thanks
Johann|||does not matter if you haven't got this page in your server with the server.execute you can capture the html code then play with then pragmatically follow this link with some exampleshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwebhttpserverutilityclassexecutetopic.asp

Importing Data from Excel 2003 to sql server 2005

Hi all. I want to export data from excel 2003 to sql server 2005. I am using the following script:

EXECsp_configure'show advanced options', 1;

GO

RECONFIGURE;

GO

EXECsp_configure'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

Insertinto Pamphlet

Select*FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Pamphlet.xls;HDR=YES',

'SELECT * FROM [Sheet3$]')

But it is giving the following error:

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

The above script is working fine with Excel 2002 but not with 2003. Can someone please provide me exact script of importing data from excel 2003 to sql server 2005. I have also tried the Linked server option but it is still giving the same error.

Please help me.

Please make sure the file path is correct and MS Excel 2003 is closed.

Checkout these links as well:

http://sqljunkies.com/WebLog/madhivanan/archive/2005/07/27/16233.aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=4

Good luck.

Importing data from excel

Hi all,
I use the following to import data from Xl.
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
This creates a new table. How could I alter this statement to update an
existing table
Thanks
RobertINSERT dbo.tablename
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
Good luck
"Robert Bravery" wrote:

> Hi all,
> I use the following to import data from Xl.
> SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
> This creates a new table. How could I alter this statement to update an
> existing table
> Thanks
> Robert
>
>|||Thanks. I must ahad something wrong
Robert
"Derekman" <Derekman@.discussions.microsoft.com> wrote in message
news:C6B20B05-F0BF-4A3E-BD17-1782B9CB1FF2@.microsoft.com...
> INSERT dbo.tablename
> SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
> Good luck
> "Robert Bravery" wrote:
>|||Robert Bravery wrote:
> Hi all,
> I use the following to import data from Xl.
> SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
> This creates a new table. How could I alter this statement to update an
> existing table
> Thanks
> Robert
>
>
Instead of using "SELECT * INTO <newtablename> FROM", use "INSERT INTO
<existingtablename> SELECT * FROM".

Importing csv File into SqlExpress .MDF file.

Hi,
I'm trying to use BCP to import a .csv file into a .mdf file.
At the commandline, I'm using the following command:
bcp commercedb.mdf.crmc_products in test.csv -T
I'm getting errors (below) telling me that I'm not successfully
connecting to the database.
Any help would be appreciated. If you can help me, please understand
I'm new to this so limited g speak would be most appreciated.
ERRORS:
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not
open a con
nection to SQL Server [53].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]An error has occurred while
establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may
be caused by the fact that under the default settings SQL Server does
not allow
remote connections.Data access libraries do not directly access (MDF) SQL Server database files
the same way they can an (MDB) MS Access file. All data access must go
through the SQL Server service itself.
The .mdf database file must first be attached to SQL Server.
http://msdn2.microsoft.com/en-us/library/ms165673.aspx
Once done, you bulk copy specifying the server and database name:
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
<tom.herz@.gmail.com> wrote in message
news:1138982708.766097.92140@.f14g2000cwb.googlegroups.com...
> Hi,
> I'm trying to use BCP to import a .csv file into a .mdf file.
> At the commandline, I'm using the following command:
> bcp commercedb.mdf.crmc_products in test.csv -T
> I'm getting errors (below) telling me that I'm not successfully
> connecting to the database.
> Any help would be appreciated. If you can help me, please understand
> I'm new to this so limited g speak would be most appreciated.
> ERRORS:
> SQLState = 08001, NativeError = 53
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not
> open a con
> nection to SQL Server [53].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 53
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this
> failure may
> be caused by the fact that under the default settings SQL Server does
> not allow
> remote connections.
>|||Hi,
Thanks. This is what I needed. I hadn't attached the database.
I haven't been successful yet, but that is a lack of experience with
all the switches and BCP overall.
Once I got the database attached, things started to gel.
I really appreciate your time, JT.
Have a good day.

Importing Access 2002 into Reporting Services

Getting the following error when trying to import an Access 2002 database into Microsoft Reporting Services:
--The given path's format is not supported--
Please advise if you know what this is referring to.R U Using URL or local path ?|||Originally posted by Ben Mansouri
R U Using URL or local path ?

local path, should I use URL?

Importing a XML doc into relational tables.

HELP! This is driving me mad...
Greetings all.
I am trying to import XML into SQL.
I have the following ingredients:
1 X xml document with 2 levels. Parent > child
1 X xml source object
2 X tables in SQL.
The XML doc is quite simple. Parent element (PLU) and a child element
(DESC)
The SQL tables are PLU (PLU_Code) and PLU_Child (PLU_Code, DESC)
(PLU_Code is a FK.
When setting up the xml, it gives me two outputs (Auto created the xsd)
PLU and DESC.
PLU has PLU_Code and PLU_Id
DESC as PLU_Id and DESC.
As per MS, the _Id field is used to retain the relationship. Fine.
I can populate the main table (PLU) fine. I cannot populate the child
table because of the FK.
If MS provides the PLU_Id, how do I use it?
I do not want to use a lookup (Can't because on the child I do not
have a PLU_Code field).
Any guidance?
Cheers,
CrispinYou have to use the @.mp:id and @.mp:parentid meta-properties to JOIN the
results and get the Parent Key. The problem is you have to use temp tables
due to technological limitations. So it gives something like this:
SELECT * into #ParentTable
FROM OPENXML(@.hDOC, 'ParentNode', flag)
WITH (ParentKey, xmlID int '@.mp:id')
SELECT * into #ChildTable
FROM OPENXML(@.hDOC, 'ChildNode', flag)
WITH (someChildFields ..., xmlParentID int '@.mp:parentid')
Then JOIN the temp tables and INSERT:
INSERT INTO childTable (ParentKey, someChildFields)
SELECT ParentKey, someChildFields
FROM #ParentTable T1 INNER JOIN #ChildTable T2 ON
(T1.xmlID = T2.xmlParentID)
Have fun
"crispin.proctor@.gmail.com" wrote:

> HELP! This is driving me mad...
>
> Greetings all.
>
> I am trying to import XML into SQL.
> I have the following ingredients:
> 1 X xml document with 2 levels. Parent > child
> 1 X xml source object
> 2 X tables in SQL.
>
> The XML doc is quite simple. Parent element (PLU) and a child element
> (DESC)
> The SQL tables are PLU (PLU_Code) and PLU_Child (PLU_Code, DESC)
> (PLU_Code is a FK.
>
> When setting up the xml, it gives me two outputs (Auto created the xsd)
> PLU and DESC.
> PLU has PLU_Code and PLU_Id
> DESC as PLU_Id and DESC.
>
> As per MS, the _Id field is used to retain the relationship. Fine.
>
> I can populate the main table (PLU) fine. I cannot populate the child
> table because of the FK.
>
> If MS provides the PLU_Id, how do I use it?
>
> I do not want to use a lookup (Can't because on the child I do not
> have a PLU_Code field).
>
> Any guidance?
>
> Cheers,
> Crispin
>|||Fleo,
This is the way I could get around it but this method detracts from the
new SSIS pipeline method and would be rather slow.
I could have very large documents which I now have to do the following:
1) Load each level into temp tables.
2) Join the temp tables together using the ID's created.
3) Load them into their final resting place. (This cannot be done in
step two as the keys are not unique)
Within that process, I have to do many lookups etc and find all this
would be rather slow.
Could they not be loaded into datasets and manipulated from there on?
I am busy with that but keep getting an error saying "You cannot add an
output column to the output collection" ('?)
Cheers,
Crispin|||Hi,
Sorry I have no idea, I am fairly new to this. I am trying to do about the
same thing.
Anyone with ideas? Would .xsd schema with annotations help here?
"crispin.proctor@.gmail.com" wrote:

> Fleo,
> This is the way I could get around it but this method detracts from the
> new SSIS pipeline method and would be rather slow.
> I could have very large documents which I now have to do the following:
> 1) Load each level into temp tables.
> 2) Join the temp tables together using the ID's created.
> 3) Load them into their final resting place. (This cannot be done in
> step two as the keys are not unique)
> Within that process, I have to do many lookups etc and find all this
> would be rather slow.
> Could they not be loaded into datasets and manipulated from there on?
> I am busy with that but keep getting an error saying "You cannot add an
> output column to the output collection" ('?)
>
> Cheers,
> Crispin
>

Importing a SQL File from the Internet...

Hello...

I am trying to create a small SSIS Package that should do the following things

1. Fetch a file from a (Later Several) Webserver(s)

2. Parse the file so the Format is "more acceptable"

3. "Normalize" the Data from the file into 3 Tables for each row (Each row contais a "Unit" , a "Location" and a "Owner" - For each of these Items i have to check for "existance" and if they do exist i need to update IF the Values changed). Also if a row is NOT present i need to remove it from my DB...

So since I am stuck on step one... Is there a "premade" Task that will allow me to fetch this/these Files? I know that i could just write a Scriptblock to fetch thse files, but since there is a FTP Task i thought there would also be an HTTP one? Also some suggestions about the best way to "normalize" the Data would be welcome...

[edit]

I just found the HTTP Task in the wishlist ;)

Ash Sharma has described a method for doing this: http://sqljunkies.com/WebLog/ashvinis/archive/2005/05/25/15653.aspx

Parsing the data would best be done with a script task/component or a custom task/component. Donald Farmer has an example of importing a semi-structured file in his book (http://www.amazon.co.uk/exec/obidos/ASIN/1932577211/qid=1139167425/sr=8-1/ref=sr_8_xs_ap_i1_xgl/203-1655415-8887134) which you will find useful.

-Jamie