imports table structures in SQL 2000 into Excel

Hi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?

Or I need to do it manually?
Thank you in advanced.
Sincerely

AgustinaRun this in Query Analyzer: (common data types, add the the case statement for more)

select name,
case xtype
 when 56 then 'Int'
 when 127 then 'BigInt'
 when 167 then 'VarChar'
 when 175 then 'Char'
 when 60 then 'Money'
 when 58 then 'SmallDateTime'
 when 104 then 'Bit'
 when 173 then 'TimeStamp'
 when 61 then 'DateTime'
 when 48 then 'TinyInt'
 else 'Other' end,
length
from syscolumns
where id = (
select id
from sysobjects
where name = 'TheTableName')
order by colid

|||You could look up the Schema. Run this in Query Analyzer and adjust accordingly:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = '<DATABASE NAME>' AND TABLE_SCHEMA = '<DB OWNER>' AND TABLE_NAME = '<YOUR TABLES NAME>'

sql

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 File: How to dynamically change the row delimiter

Hi,

I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?

i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.

Any help would be appreciated

I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched

Regards JustinThe solution is to identify and change the EOL character that is used in the file before the file is passed through the dts package. The scripting object doesn't appear to contain anything useful that will identify the EOL character so I have come up with this routine in VBA rather then VBScript. It will accept a user defined number of characters in a text file for and returns the EOL character (only a carriage return or line feed).

Public Function gIdentifyEOLCharacter(strFileName As String, _
lngNumOfCharactersToCheck As Long) As String
' identifies the end of line character
Dim fsoSysObj As Scripting.FileSystemObject
Dim tStream As Scripting.TextStream
Dim strText As String, strEOLCharacter As String
On Error GoTo ErrorHere
Set fsoSysObj = New Scripting.FileSystemObject
Set tStream = fsoSysObj.OpenTextFile(strFileName, ForReading)
strText = tStream.Read(lngNumOfCharactersToCheck)
If InStr(strText, Chr$(13)) Then _
strEOLCharacter = strEOLCharacter & "{CR}"
If InStr(strText, Chr$(10)) Then _
strEOLCharacter = strEOLCharacter & "{LF}"
gIdentifyEOLCharacter = strEOLCharacter
ExitHere:
Set fsoSysObj = Nothing
Set tStream = Nothing
Exit Function
ErrorHere:
MsgBox "Error In: Module 'basSupportFunctions'" & vbCrLf _
& "Procedure: gIdentifyEOLCharacter" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
gIdentifyEOLCharacter = vbNullString
Resume ExitHere
End Function

I can't believe that there is nothing simpler in the scripting object that can return the EOL character - i have looked through the object model and can't see anything that is useful|||I think you can try FINDSTR with /G:file parameter where you'd have nothing but CRLF in the file. If FINDSTR returns a file name then it means that the specified file contains normal EOL combination. Else, - it's not. You can also have 2 files, one with CRLF and the other with just LF. So that if the first one produced 0 results you can do FINDSTR against the second and be certain that LF is the actual delimiter.|||This is just a wild guess but you might run a cmdshell and do:

type inputfile.txt>newfile.txt

before importing. Type may convert LF to CRLF.

I also wonder if you could create a CR placeholder column with a default value of null and always specify LF as the line terminator.

If these files are coming from an FTP transfer, set the type to Ascii instead of bin in FTP to get CRLF terminators.

Importing non-hidden rows from Excel?

I have a need to import only non-hidden rows from an excel spreadsheet. When I create the package in SSIS, it imports everything. Due to the use of the data on the spreadsheet, we cannot simply delete the data.

Is there a special setting in the Excel Source or Connection manager that can be set to "only import non-hidden rows"?

Also, how do I go about setting the sheet with an index instead of the actual Sheet name? The user changes the sheet name at random, but I know I only need the first two sheets on the file.

Thanks!

Matt Michuta

No, there is no setting like this. You at the mercy of the Excel OLE DB Provider here and it doesn't possess functionality like you are requesting. You will have to filter those rows out in the pipeline.

Not sure about the sheet index problem - I don't ever use the Excel provider. I suspect you can't do that either. Perhaps try the data access forum if no-one here knows: https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

-Jamie

|||You should be able to do this with a little bit of creative vba coding in excel. You can write a macro that will copy all the data to a sheet that has a static name and link the action to an event or button. Since you can use sheet indexes and row properties in vba you can get exactly what you need, and since the sheet you're copying to has a static name you can use the excel source in SSIS to import it.

Importing data with leading 0

I am trying to import some data from excel into SQL Server table - some of
the data has a leading 0 ie 0897 - when I import it, it imports these record
s
as NULLS. How do I import the record so it is 897.
Thanks in advanceHow are you importing the data?
AMB
"ChrisB" wrote:

> I am trying to import some data from excel into SQL Server table - some of
> the data has a leading 0 ie 0897 - when I import it, it imports these reco
rds
> as NULLS. How do I import the record so it is 897.
> Thanks in advance|||Using DTS
"Alejandro Mesa" wrote:
> How are you importing the data?
>
> AMB
> "ChrisB" wrote:
>|||Have a look at;
http://support.microsoft.com/defaul...-US;Q194124#kb3

Importing CSV file with URL using DTS?

I need to be able to create a DTS package that imports a CSV file which is loated at URL. I.E. HTTP://www.url.com/csv/thefile.xls I tried copying the URL an pasting it in the file location when in SQL wazird but I got an error message.

Please help point me to anywhere that I can find some help in doing this? If there is a better way please let me know

ThanksI would post this in the SQL Server Integration Services forum instead of this one. Also, to get a quicker response, please detail exact steps you took to hit the problem, as well as display the exact error message you got.

Importing a tab delimited file into SQL Server

I am trying to impport a tab demlied txt file, into SQL SERVER, using OPENROWSET. Howver, the file gets imports, into SQL, BUT ONLY in 1 column... The source file really has 3 columns, but when it gets isnerted into SQL, it only oputs it all in 1 column.

select * into #tmp from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\\c:\SQLRSReports;','select * from vwConstrLendingmapLoanDefiCdDesc.txt')

How do I insert the data into 3 seperate colms, instead of 1?

Try

BULK INSERT
http://msdn2.microsoft.com/en-us/library/ms188365.aspx

or
BCP
http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx|||I would like to use the OPENROWSET Only. Is that possible with Openrowset?|||

Code Snippet

CREATE TABLE #newIn
(
Ident varchar(max),
subIdent int identity(1,1)
subItem varchar(max)
)

DECLARE @.posA int,
@.posB int,
@.srch char(1),
@.in varchar(max)

SET @.srch = ',' --or char(9) for tab

DECLARE csr CURSOR FOR
SELECT item from #tmp

OPEN csr

FETCH NEXT FROM csr
INTO @.in

WHILE @.@.FETCH_STATUS = 0
BEGIN

SET @.in = LTRIM(RTRIM(@.in))+@.srch
IF len(@.in)>1
BEGIN
SET @.posB = CHARINDEX(@.srch, @.in)
SET @.posA = 0
WHILE @.posB > 0
BEGIN
INSERT INTO #newIn (Ident, subItem) VALUES (@.in, substring(@.in, @.posA, @.posB-@.posA))
SET @.posA = @.posB + 1
SET @.posB = CHARINDEX(@.srch, @.in, @.posA)
END
END
END

CLOSE csr
DEALLOCATE csr

select *
from #newIn

DROP TABLE #newIn

This converts the single column into multiple rows that you can then extract back as you want.

If not exactly what you need, then it's a starting point for other variations.|||

I created a schema.ini file, with all the format specifiactions for my source file. Here is the code for that. However, when I import, it most of the data is imported fine, except for when the value has "" in the data. eg if the data has value such as 'pending "store" ', it imports the data as only "pending", and omits the store..

Here is my specificaiton of the schema file

[vwConstrLendingmapLoanDefiCdDesc.txt]
ColNameHeader=False
Format=TabDelimited
MaxScanRows=0
CharacterSet=ANSI
Col1=Doccd Char Width 50
Col2=Docdesc LongChar
Col3=Docshcd Char Width 100

- And this is the openrowset:

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\\c:\SQLRSReports;','select * from vwConstrLendingmapLoanDefiCdDesc.txt')

Pl advise