Retrieving Column Counts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Retrieving Column Counts

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2003-07-24 : 10:09:49

Hi -- I've got a DTS package that simply imports data from Excel to a SQL Server table. In the same package I've got an ActiveX Script task which needs to know the total number of columns in the Excel file. Is this possible to retrieve from the package?

Bill

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-24 : 11:33:26

Tune it for your case:

Dim e, r, i
Set e = CreateObject("Excel.Application")
e.Visible = False
e.DisplayAlerts = False
e.Workbooks.Open "D:\my.xls"
Set r = e.Worksheets(1).Range("A1:IV1")
While r.Cells(1, i + 1).Value <> ""
i = i + 1
Wend
MsgBox i
e.Quit
Set r = Nothing
Set e = Nothing

- Vit

billsox
Yak Posting Veteran

74 Posts

Posted - 2003-07-24 : 12:06:59

Thanks! I was hoping to be able to get it from the DTS objects rather than Excel but if this is the only way, then I'll implement it.

Bill

quote:

Tune it for your case:

Dim e, r, i
Set e = CreateObject("Excel.Application")
e.Visible = False
e.DisplayAlerts = False
e.Workbooks.Open "D:\my.xls"
Set r = e.Worksheets(1).Range("A1:IV1")
While r.Cells(1, i + 1).Value <> ""
i = i + 1
Wend
MsgBox i
e.Quit
Set r = Nothing
Set e = Nothing

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-24 : 13:52:11

For sure this script is for to be pasted into
an ActiveX Script Task... in your DTS package. :)

- Vit

billsox
Yak Posting Veteran

74 Posts

Posted - 2003-07-24 : 15:28:37

I realize that. My hope was to be able to do something like:

Function Main()

    Dim oPkg, oDataPump, oTransform

    Set oPkg = DTSGlobalVariables.Parent
    Set oDataPump = oPkg.Tasks("Transpose Task").CustomTask
    Set oTransform = oDataPump.Tranformations("DTSTransformation__1")

    MsgBox oTransform.SourceColumns.Count
    
    ' Clean Up
    Set oTransform = Nothing
    Set oDataPump = Nothing
    Set oPkg = Nothing

    Main = DTSTaskExecResult_Success
End Function

However, executing this code results in this error: Object doesn't support this property or method: 'oDataPump.Transformations'.

Bill

quote:

For sure this script is for to be pasted into
an ActiveX Script Task... in your DTS package. :)

- Vit

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-24 : 18:01:46

For this sort of thing it's always worth doing it in VB to see what the available objects and properties are.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources