DTS Package (Excel Transformation)

Question

DTS Package (Excel Transformation)

AER

SSCrazy

Points: 2763
More actions
March 6, 2006 at 2:03 pm

#167261

Hi everybody,
I recently posted a topic about missing values when running a Transform Data Task from Excel into SQL Server.
I finally found the reason why it's hapenning. My column in Excel is a number field, suppose to be a text field.
I'm running an automated proccess. The file is being send monthly from the vendor and I do not have control over it.
How can I programmatically change a datatype of the field in the Excel spreadsheet that is a Source in the DTS package Transform Data Task?
Any help will be greatly appreciated. Thank you so much.
Alex

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 1

Hi,

This topic has been on the agenda before in this forum. I had the same problem. If numbers and text in a column alters between the rows the format will be set to number if I think the seven first rows are numbers to a number field. This is a MS specification. I save the Excelsheet as a textfile and use DTS textfile import which works fine.

OR you can try VB like:

Dim cnx As ADODB.Connection

Set cnx = New ADODB.Connection

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

cnx.Provider = "sqloledb"

cnx.Properties("Network Library").Value = "DBMSSOCN" 'Remote server

cnx.Properties("Password").Value = "XXX"

cnx.Open "Data source=YYY;User ID=ZZZ;Initial Catalog=TEST"

Set cmd.ActiveConnection = cnx

cmd.CommandText = "Insert into test (value1,value2,text,etc) " & _

"select * " & _

"from OpenRowSet ( " & _

"'Microsoft.Jet.OLEDB.4.0' " & _

" , 'Excel 8.0; DATABASE=C:\path\book1.xls;HDR=YES' " & _

" , 'select value1,value2,text,etc from [Data$]' )"

cmd.Execute

OBS headers in the Excel spredsheet Data

Gosta

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 2

Alex,

This is a bug in DTS. There is a workaround that my fellow DBA found out on the web. It has something do changing one of the properties in your connection. When he gets in, I will ask him for a link to the page describing this issue.

John

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 3

You may want to check this MS KB article out. It *might* help. By default, the Jet 4.0LEDB Source engine for Excel only scans the first 8 rows of a spreadsheet to determine the type/length of a column. If you edit the registry you can change that.

"The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large."

http://support.microsoft.com/kb/281517

AER SSCrazy Points: 2763 More actions · Answer 4

Guys,

If I'm saving my excel spreadsheet in a comma delimited format and then processing it as a text file in the package it works fine.

But when I'm trying to save the file in the Active Script task I cannot assign the comma delimited format to it.

This is a line of the code:

oXLSWorkbook.SaveAS "C:\blabla.csv"

How I can do that programmatically? Are there any parameters for the comma delimited format?

I could not find the answer Online.

Any help would be greatly appreciated.

Thanks.

Alex