DTS from excel file (excel filename is different everyday)

[LimaCharlie]

Good Day to all,

Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.

[spykhov]

Interesting question!
I have the similar problem - how to run SQL Server package (I'm in 2005 version) with the source file as a variable to be set up at runtime.
I've spent 3 days to find the solution, but there is no any progress.

[LimaCharlie]

Hi,

I also joined another forum and asked the same question.
Here's the answer of one of the members there...

"You will need to create a variable in your DTS package for the file name, and then construct the filename dynamically."

But I can't seem to figure out on how to do this. I asked for more specifics and waiting now for the reply.

[spykhov]

In my SSIS package I created a variable with the name "PathFile" of string type with path and file name and in the ConnectionStrin g of the SourceConnectio nFlatFile properties I overighted the full path and name by @[PathFile] link to the variable.
It doesn't work - packeg do not accept ConnectionStrin g as variable.

[spykhov]

Good news!

I resolved my problem! I've found out the solution in the next article
http://www.databasejou rnal.com/features/mssql/article.php/3509601

The code I used for:

Declare @Filename varchar(1000)
Declare @cmd varchar(1000)
Select @Filename = 'O:\Plan2005\Da taWarehouse\Exc el Queries\'
Select @Filename = @Filename + 'report2.txt'
select @cmd = 'dtexec /Reporting E /SQL Package1 /SET \Package.Connec tions[SourceConnectio nFlatFile].Properties[ConnectionStrin g];"' + @Filename + '"'
exec master..xp_cmds hell @cmd

Changing @FileName variable in run time will change the source file.

Good luck everybody!

[LimaCharlie]

Hi spykhov,
Is this solution applicable to MS SQL Server 2000 as well? I'm still using the 2000 version.

[LimaCharlie]

Hi,
I can't seem to get it yet. I am presented w/ so many information from all the websites and help files that I am reading, and I end up more confused. :-(
I'm a newbie in SQL and I need instructions for dummies. =)

I already created a global variable. How can I referenced the value of that global variable to my task?

Here's what I did.
1.) I drag a "Execute SQL Task" to the DTS Designer.
2.) I created a global variable that would hold the FIleName of the excel file I will call upon DTS Package Execution.
3.) I drag "MS Excel 2000" to the DTS Designer and selected a sample excel file.
4.) I drag the "SQL Server" connection to the DTS Designer and selected the database I'm working with.
5.) I connected the 2 connection by using the "Transform Data Task".

Question: How can I reference the global variable so that it would be the excel ource file upon DTS.

Please help.

[LimaCharlie]

Hi,

I got it already!
Here's what I did:
1.) I created a global variable
2.) I add an ActiveX Script Task wherein I assign a value to the global variable
3.) I add a Dynamic Property Task wherein I assign the datasource to the global variable
4.) Then I placed a workflow.

Now, I have another question:
How can I import data from 24 excel files everyday. Excel filenames are like these:

100206_AAA
100206_BBB
100206_CCC
up to
100206_XXX

wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...1 00206_XXX).
How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.

Here's how my ActiveX Script looks like:

'************** *************** *************** *************** ***********
' Visual Basic ActiveX Script
'************** *************** *************** *************** ***********

Option Explicit

Function Main()
Dim vDay, vMonth, vYear, vDate

vDay=RIGHT(RTRI M("0" & DAY(DATE()-2)),2)
vMonth=RIGHT(RT RIM("0" & MONTH(DATE()-2)),2)
vYear=RIGHT(YEA R(DATE()-2),2)
vDate=vMonth & vDay & vYear

DTSGlobalVariab les("gVarPath") .Value=vDate & "_AAA.xls"

Main = DTSTaskExecResu lt_Success
End Function

Thank you so much... :)
God Bless.

[modjoe23]

Hi,

If you found a solution to your problem...pleas e post!

Thanks.

Hi,

I got it already!
Here's what I did:
1.) I created a global variable
2.) I add an ActiveX Script Task wherein I assign a value to the global variable
3.) I add a Dynamic Property Task wherein I assign the datasource to the global variable
4.) Then I placed a workflow.

Now, I have another question:
How can I import data from 24 excel files everyday. Excel filenames are like these:

100206_AAA
100206_BBB
100206_CCC
up to
100206_XXX

wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...1 00206_XXX).
How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.

Here's how my ActiveX Script looks like:


Thank you so much... :)
God Bless.

[Bartek Juszczak]

And why not create BAT file with where you can copy/rename input files and then execute it by Execute Process Task?

By that you can get always the same name of input...

Bartek