Importing an Excel File with DTS

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)

Importing an Excel File with DTS

Author

Topic

jdoering
Starting Member

32 Posts

Posted - 2002-08-08 : 16:30:46

I am importing an Excel file into some tables using a DTS package (SQL Server 2000). I have successfully passed my global variable (@Supplier_ID) into the DTS. (DTS package works great)

Here's my dilema
I am trying to import an Excel file however, however the name of the Excel file Log_Template.xls changes every time based on who originally created it. If Supplier 120 (120 is value for the variable @Supplier_ID), then the file is named Log_Template_120.xls, if 110 then the file is named Log_Template_110.xls. I am successfully passing the Supplier_ID as a global variable into the DTS package.
My question is: How do I setup the Connection Properties for Excel with the DTS package to be dynamic? I want to change the connection so that directory reads E:\Log_Template_110 + @Supplier_ID.

Does this make any sense?

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-08 : 16:59:16

Create an ActiveX script task which is the first thing to run in the package and add the following code


Function Main()
	Dim oConn, sFilename

	sFilename = "E:\Log_Template_" & DTSGlobalVariables("Supplier_ID").Value & ".xls"

	Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000")
	oConn.DataSource = sFilename

	Set oConn = Nothing

	Main = DTSTaskExecResult_Success

End Function

HTH
Jasper Smith

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-08 : 17:06:22

How about setting up the package with a fixed file name for the Excel file (e.g. E:\Log_Template_Data.xls) and then using the Supplier_ID to rename the file using an ActiveX task:

Function Main
Dim fso, fil, filename
filename="E:\Log_Template_" & DTSGlobalVariables("Supplier_ID").Value & ".xls"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fil = fso.GetFile(filename)
fil.Name = "E:\Log_Template_Data.xls"
Set fil=Nothing
Set fso=Nothing
Main=DTSTaskExecResult_Success
End Function

You may need to include a line of code that deletes any existing file with the fixed file name. Anyway, include this ActiveX task and make it the first step, then add an OnSuccess arrow from it to the rest of the DTS package.

Edited by - robvolk on 08/08/2002 17:07:58

jdoering
Starting Member

32 Posts

Posted - 2002-08-09 : 09:05:22

Thanks, this is a great lead. Some more question, do I keep the Excel Icon data connection in the DTS package pointed to imported the fixed text file name? And how do I transform the columns in Excel to the destination columns now?

Function Main()
Dim oConn, sFilename

sFilename = "E:\Log_Template_" & DTSGlobalVariables("Supplier_ID").Value & ".xls"

Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000")
oConn.DataSource = sFilename

Set oConn = Nothing

Main = DTSTaskExecResult_Success

DTSDestination("Sup_Mat_Num") = DTSSource("Supplier Mat Num")
DTSDestination("Prod_Desc") = DTSSource("Product Descriptions")
DTSDestination("Prod_Hierarchy") = DTSSource("Product Hiearchy")
DTSDestination("Min_Ord_Qty") = DTSSource("Minimum Ord Qty")
DTSDestination("Lead_Time") = DTSSource("Lead Time")
DTSDestination("Sample_Lead_Time") = DTSSource("Sample Lead Time")
DTSDestination("Proof_Lead_Time") = DTSSource("Proof Lead Time")
DTSDestination("Prod_Weight") = DTSSource("Prod Weight")
DTSDestination("Prod_Unit_Cost") = DTSSource("Prod Unit Cost")
DTSDestination("Prod_Soft_Good") = DTSSource("Prod Soft Good")
DTSDestination("Prod_Long_Desc") = DTSSource("Product Long Description")
DTSDestination("ProdSupNum") = DTSGlobalVariables("@Supplier_ID").Value
Main = DTSTransformStat_OK

DTSDestination("Sup_Mat_Num") = DTSSource("Supplier Mat Num")
DTSDestination("Color_Name") = DTSSource("Color Name")
DTSDestination("Upcharge") = DTSSource("Upcharge")
DTSDestination("Attribute") = DTSSource("Attribute")
DTSDestination("ProdSupNum") =DTSGlobalVariables("@Supplier_ID").Value
Main = DTSTransformStat_OK

DTSDestination("Sup_Mat_Num") = DTSSource("Supplier Mat Num")
DTSDestination("Quantity_Scale") = DTSSource("Quantity Scale")
DTSDestination("TieredCost") = DTSSource("Tiered Cost")
DTSDestination("ProdSupNum") = DTSGlobalVariables("@Supplier_ID").Value
Main = DTSTransformStat_OK
End Function

Thanks,
Julie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-09 : 09:09:14

The code you have now to set up the transformations looks good. Have you tested it? Is it giving you problems?

If you use the file-rename method (my method, the 2nd one) you should not have to re-establish the transformations after you initally set them up, as long as the file layout is the same.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-09 : 09:11:31

As long as the file format is the same it doesn't matter.
The easiest way is to set it all up with everything hard coded and then , after checking that all works, add the Active X script task to modify the file name based on your global variable. Transforms etc should be fine as long as the source file format is the same regardless of its name.

HTH
Jasper Smith

jdoering
Starting Member

32 Posts

Posted - 2002-08-09 : 09:32:44

I am receiving the following error message when running the DTS package:

Error Code 0
Error Source=Microsoft VBScript runtime error
Error Description:=Type mismatch 'DTSSource'
Error on Line 19

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-09 : 09:34:37

Which line is line 19?

jdoering
Starting Member

32 Posts

Posted - 2002-08-09 : 11:08:29

Sorry, its where the DTSTransformations begin:
DTSDestination("Sup_Mat_Num") = DTSSource("Supplier Mat Num")

Would it be because the DTSSource doesn't know to find the Excel file and the name of the worksheet in the Excel which to use? If so, I don't know how to do this. I am not a programmer really.

Thanks,

ljeremie
Starting Member

2 Posts

Posted - 2005-07-27 : 14:12:56

I had the same problem : "Feuil$ does not exists" while using a Dynamic Property Task which was supposed to rename the filename at runtime.

The good solution was to make an activeX script which changes the datasource of the Excel connection.

Thanks for your posts !

J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources