DTS-Change Excel File name based on variable

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)

DTS-Change Excel File name based on variable

Author

Topic

jdoering
Starting Member

32 Posts

Posted - 2002-08-14 : 16:04:12

Hello all!

I am creating a DTS package where I need to pass a variable into the package so that the package knows which Excel file to import into the table. My variable is @Supplier_ID. Based on this variables values (102, 325), etc) the Excel file name is LoadTemplate_Lite_V10_102, LoadTemplate_Lite_V10_325, etc. The variable is passed successfully into the DTS package. However the DTS package doesn't know which excel file to import because I receive the following error message:

"Error: -2147217865 (80040E37); Provider Error: -543884569 (DF94FAE7)

Error string: The Microsoft Jet database engine could not find the object 'Products$'. Make sure the object exists and that you spell its name and the path name correctly.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003011"

Products$ is a the name of the first worksheet in the excel file.

My ActiveX Transformation Script between the Excel Connection and the database connection is as follows:

Function Main()

Dim oConn, sFilename

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

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

Main = DTSTaskExecResult_Succes

DTSDestination("Sup_Mat_Num") = DTSSource("Sup_Mat_Num")
DTSDestination("Prod_Desc") = DTSSource("Sample_Lead_Time")
DTSDestination("Prod_Hierarchy") = DTSSource("Proof_Lead_Time")
DTSDestination("Min_Ord_Qty") = DTSSource("Prod_Weight")
DTSDestination("Lead_Time") = DTSSource("Prod_Unit_Cost")
DTSDestination("Sample_Lead_Time") = DTSSource("Prod_Soft_Good")
DTSDestination("Proof_Lead_Time") = DTSSource("Prod_Long_Desc")
DTSDestination("Prod_Weight") = DTSSource("Prod_Hierarchy")
DTSDestination("Prod_Unit_Cost") = DTSSource("Prod_Desc")
DTSDestination("Prod_Soft_Good") = DTSSource("Min_Ord_Qty")
DTSDestination("Prod_Long_Desc") = DTSSource("Lead_Time")
DTSDestination("ProdSupNum") = DTSGlobalVariables("@Supplier_ID").Value
Main = DTSTransformStat_OK

Set oConn = Nothing
End Function

If someone could please direct me direct me I would greatly appreciate it. I don't know if it is the Excel file or the worksheet.

Thanks,

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-14 : 16:11:18

Trust me, I have felt your pain

.

The issue is that DTS does not provide a mechanism for run-time discovery of data sources. The sheet that you reference must already exist with the same name as that used at design time. The filename may be different, which is the good news, but everything else must be the same. Also, if I remember correctly, DTS appends a '$' character to the sheet name you provide it - maddening since this means you can't ever directly specify the sheet name.

To get it to work you need a mechanism for creating the workbook and the sheet, perhaps an ActiveX scripting task, and run it before the data pump into the sheet. You may need to run it twice to shake out the $ issue.

Jonathan Boott, MCDBA
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-14 : 16:11:59

What about the suggestion here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18632

About setting up the transformations on a fixed file first, then using ActiveX to rename (or copy) the source file with the supplier ID to the fixed file name? Once the transformations are set you don't need to specify column names or sheet names afterwards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources