SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Importing an Excel File with DTS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jdoering
Starting Member

32 Posts

Posted - 08/08/2002 :  16:30:46  Show Profile  Reply with Quote
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

United Kingdom
846 Posts

Posted - 08/08/2002 :  16:59:16  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 08/08/2002 :  17:06:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

jdoering
Starting Member

32 Posts

Posted - 08/09/2002 :  09:05:22  Show Profile  Reply with Quote
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















Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 08/09/2002 :  09:09:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 08/09/2002 :  09:11:31  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
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
Go to Top of Page

jdoering
Starting Member

32 Posts

Posted - 08/09/2002 :  09:32:44  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 08/09/2002 :  09:34:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
Which line is line 19?

Go to Top of Page

jdoering
Starting Member

32 Posts

Posted - 08/09/2002 :  11:08:29  Show Profile  Reply with Quote
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,

Go to Top of Page

ljeremie
Starting Member

France
2 Posts

Posted - 07/27/2005 :  14:12:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000