June 24, 2009 at 3:05 pm
I would like to output to an Excel file that includes the date in the filename, such as: Monthly_Output_20090624.xls I've created two variables:
jetengine: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Monthly_Output_
jetengine2: .xls;Extended Properties="Excel 8.0;HDR=YES";
And my Excel Connection Manager's Connection String expression is this:
@jetengine+RIGHT("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4)+Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + @jetengine2
This works fine after the first run of the package, but when I try to run it the second time, I noticed that the Excel Connection Manager's Excel File Path is now referencing the newly created Excel file (with the datestamp).
My question is will this package execute properly the next time I run it? The connection string won't be able to find the file because the date expression will have changed to the new date.
Just wondering if it's just easier to create the regular file and then create a File System Task to copy/rename that file into the location that I want...but then, I'm not sure how to create the file system task that will copy/rename the file. hehe
Any help is greatly appreciated!
June 30, 2009 at 9:56 am
I'm a little fuzzy on what you are trying to accomplish. Let me see if I understand..
You have an SSIS package that outputs data to an Excel Spreadsheet with the name date/time based and you are using an expression to build the name.
Questions I have:
1. Is the spreadsheet being created elsewhere and the package is guessing the name?
2. What is your question about subsequent runs, the express would be calculated at each run and a new filename potentially generated.
I seem to remember something about the Excel file must exist already to output to it, but I'm not sure about that. It has been a while.. If that is true then using a script command to build the copy command would be the easiest..
June 30, 2009 at 6:06 pm
This is how I would do it.
1) Copy from a blank (except for column names) Excel spreadsheet file to (say) export.xls
2) Your package always exports to this new export.xls
3) A final File System task copies or renames export.xls to whatever file name you want.
You should find that the File System task is pretty intuitive to use - give it a go and get back with any specific questions.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 30, 2009 at 9:40 pm
I have to admit, I like Phil's process..
CEWII
July 10, 2009 at 10:41 am
Sure, it's easy once you put it that way... 😉 Sorry for the late reply, I've been on vacation. hehe
I'll give it a try and let you know! Thanks again!
July 15, 2009 at 11:12 am
OK, so I was able to successfully copy the Excel file from one location to another, but I don't know how to include the date/month in the filename after it's been moved to the new location. Do I need to create variables and if so, how do I do that? I'm just a beginner.
My package creates a file: C:\Ryan\Output_.xls
Then that file needs to be moved to a different location, but with the date added to the filename: C:\NewLocation\Output_(PreviousMonth'sName).xls
Thanks for all the help so far!
July 15, 2009 at 12:48 pm
Phil's suggestions is simplest. And when dealing with Excel I have found the simpler the better. The only step I would add would be to delete any files called export.xls (or whatever you decide to name your temp file) and then continue with the extraction process.
J.D.
July 15, 2009 at 2:14 pm
I want to use an SSIS package that outputs data to an Excel Spreadsheet (from SQL table) with the name date/time such as "output_07152009.xls". Does anyone have a detail-step info or link? Thanks for help!
July 15, 2009 at 3:00 pm
OK, so I was able to successfully copy the Excel file from one location to another, but I don't know how to include the date/month in the filename after it's been moved to the new location. Do I need to create variables and if so, how do I do that? I'm just a beginner.
My package creates a file: C:\Ryan\Output_.xls
Then that file needs to be moved to a different location, but with the date added to the filename: C:\NewLocation\Output_(PreviousMonth'sName).xls
Thanks for all the help so far!
Ryan , little confused here!!!!!. At the very first post u say u want datetime part in filename and now here above you say , you want (PreviousMonth'sName).xls here are the steps that will rename (move and copy file) or copy file to different location using FIle System Task.
1. Create two variables of type string and assign value accordingly.
"FullSourcePath" which will have value "C:\Ryan\Output_.xls"
"FullDestinationPath" ( this variable will be in expression)
Press F4 to get variable expreesion builder and set evaluate as expreesion to TRUE
then build your expression to get the desired results for file path and name.
2. get the file syatem task and configured it accordingly.
Is source a variable to true and then FullSourcePath
and similar for destination.
if u do copy file in operation it will have a copy on both location, one with original name and one with the name that is in ur variable expression
if u do rename file it will actually move file to new location with new name.
Let me know if u need help with building expression for Destination variable
it should be simple
"C:" + "\\" + "NewLocation" + "\\ + "Output_" + "*****put here you code to get date and time portion******" + ".xls"
July 15, 2009 at 3:17 pm
SSIS (7/15/2009)
OK, so I was able to successfully copy the Excel file from one location to another, but I don't know how to include the date/month in the filename after it's been moved to the new location. Do I need to create variables and if so, how do I do that? I'm just a beginner.My package creates a file: C:\Ryan\Output_.xls
Then that file needs to be moved to a different location, but with the date added to the filename: C:\NewLocation\Output_(PreviousMonth'sName).xls
Thanks for all the help so far!
Ryan , little confused here!!!!!. At the very first post u say u want datetime part in filename and now here above you say , you want (PreviousMonth'sName).xls here are the steps that will rename (move and copy file) or copy file to different location using FIle System Task.
1. Create two variables of type string and assign value accordingly.
"FullSourcePath" which will have value "C:\Ryan\Output_.xls"
"FullDestinationPath" ( this variable will be in expression)
Press F4 to get variable expreesion builder and set evaluate as expreesion to TRUE
then build your expression to get the desired results for file path and name.
2. get the file syatem task and configured it accordingly.
Is source a variable to true and then FullSourcePath
and similar for destination.
if u do copy file in operation it will have a copy on both location, one with original name and one with the name that is in ur variable expression
if u do rename file it will actually move file to new location with new name.
Let me know if u need help with building expression for Destination variable
it should be simple
"C:" + "\\" + "NewLocation" + "\\ + "Output_" + "*****put here you code to get date and time portion******" + ".xls"
I'm actually following Phil's idea of creating an Output.xls file and then using File System Task to copy/rename it to another location and adding a date to the filename. I was able to copy it to another location, but I'm having trouble adding the date to the filename. Ideally, I'd like to have the name of the previous month included in the filename.
July 15, 2009 at 3:31 pm
Then create one more variable called "Month" as datatype string
bring Sql Execute task and
map this variable to this T-SQL
SELECT DATENAME(mm, GETDATE()) As [Month]
in the SQL task general page set resultset value as single row and on result set page map this variable as Month to to above variable and sql code to get the month in sqlstatement source is above
then on destinationpath variable get this expression
"C:" + "\\" + "NewLocation" + "\\ + "Output_" + @User::Month + ".xls"
July 15, 2009 at 9:03 pm
Here is how I would do it, I think.
1. Ensure that there is a string variable defined (eg SourceFile) which contains the filepath of the source data (eg C:\Ryan\Output_.xls)
2. Create a Script Task to do the file copy. Ensure that User:SourceFile is added to the ReadOnlyVariables property of the Script Task.
3. Replace the entire sample code in your script task with this
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim FileSource As String, FileDestination As String
'Set the file source from the input variable
FileSource = Dts.Variables("SourceFile").Value.ToString
'Set the file destination as last month + the input variable
FileDestination = FileIO.FileSystem.GetFileInfo(FileSource).DirectoryName & MonthName((Now().AddMonths(-1).Month)) & FileIO.FileSystem.GetFileInfo(FileSource).Name
'Now perform the copy
CopyFile(FileSource, FileDestination)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub CopyFile(ByVal Source As String, ByVal Destination As String)
Dim file As FileIO.FileSystem
file.CopyFile(Source, Destination, True)
End Sub
End Class
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 16, 2009 at 8:02 am
Phil,
Do you mean that I don't need any other such as SQL task or data flow task anymore? If so, where should I copy your VB script on script task? I didn't find anywhere that allows me to paste it.
Thanks a million,
Jim
July 16, 2009 at 8:10 am
No problem. Just a script task is needed to take the variable containing the file name and do the file copy for you. This would be on the Control Flow, not the Data Flow.
Where to paste the code? Double click on the Script Task to open the Script Task editor, select Script and then click on the Design Script button ... You'll find the script in there.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 16, 2009 at 8:33 am
Hey Phil,
Do I need to change anything in that script? I'm pretty sure I followed everything. I created a string variable called SourceFile that contains the location/filename of the original file. Everything runs properly, but I don't see the new file that contains the month in the filename.
Thanks again!
Ryan
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply