July 16, 2009 at 8:33 am
Hi Phil, the data source is a table from SQL server rather than from a file system. So, can I still use your script to do it? Thanks a lot!
July 16, 2009 at 8:42 am
ryan213 (7/16/2009)
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
I think it should work as-is. Did the task turn green to indicate that it had run successfully? The new file's folder will be the same as the source file.
Phil
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:48 am
jim (7/16/2009)
Hi Phil, the data source is a table from SQL server rather than from a file system. So, can I still use your script to do it? Thanks a lot!
Hi Jim
The process I am envisaging, as I mentioned earlier in this thread, is something like this:
1) Copy Excel template (field headings, but no data) to a standard file location/name (this will be your destination connection, as configured in SSIS)
2) Use data flow task to output data from SQL Server to this 'standard' file
3) Run script to copy from 'standard' file just populated to new file, based on required naming conventions.
As it stands, the script copies c:\export\x.txt (or whatever the standard file is called) to c:\export\Junex.txt (June being 'last' month), but it would be easy to modify to suit almost any other naming requirement.
Phil
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 9:16 am
Yup, everything ran fine. Saw the box turn Green. 🙂 But the filename remains the same "Output_.xls." Do I need to do anything else in the Script Task Editor?
July 16, 2009 at 10:09 am
Um..so yeah...it's working...except the new file is located in the directory above, not the same directory. Hmm...
July 16, 2009 at 10:18 am
Interesting. Can you give me an example of what your source file variable contains?
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 11:54 am
OK, I figured out why it was saving to the parent directory.
FileDestination = FileIO.FileSystem.GetFileInfo(FileSource).DirectoryName & "\" & MonthName((Now().AddMonths(-1).Month)) & "_" & FileIO.FileSystem.GetFileInfo(FileSource).Name
I added the "\" between the DirectoryName and the MonthName. But now my filename is: June_Output_.xls. Output_June.xls would be great! Or better yet (might be asking for too much), Output_June_2009.xls.
My SourceFile variable is: \\000.00.0.000\exports\Ryan_Files\Test\Output_.xls
July 16, 2009 at 7:16 pm
Try 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
Dim file As FileIO.FileSystem
Dim Folder As String, Filename As String, FileExtension As String
Dim LastMonth As String, ThisYear As String
'Set the file source from the input variable
FileSource = Dts.Variables("SourceFile").Value.ToString
'Set the various variables
LastMonth = MonthName((Now().AddMonths(-1).Month))
ThisYear = Year(Now()).ToString
Folder = FileIO.FileSystem.GetFileInfo(FileSource).DirectoryName
Filename = System.IO.Path.GetFileNameWithoutExtension(FileSource)
FileExtension = FileIO.FileSystem.GetFileInfo(FileSource).Extension
'Assemble the spec of the destination file
FileDestination = Folder & "\" & Filename & LastMonth & "_" & ThisYear & FileExtension
'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
As you will see if you examine the code, I have broken out the various components and then assembled the destination file spec at the end - hopefully you will be able to use this to get the destination file name in whatever form you want.
Phil
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 9:58 pm
Freakin' genius! It worked, after I "un-commented" this line:
' CopyFile(FileSource, FileDestination)
hehe
I'm having trouble understanding how the Precedence Constraint Editor works though. I can sort of get it to work, but I don't completely understand it. I want it to create the file if there are records, but not create the file (and send me an email) if there are no records.
I have a Row Count that saves to a variable called RowCountResult, then I have that connected to two things: Data Flow task to create my file and also an Execute SQL Task to send me an email if there are no records.
The precedence constraint between my Row Count and Data Flow Task is set up like this:
Evaluation operation: Expression Or Constraint
Value: Failure
Expression: @RowCountResult ==0
Can you offer an explanation/solution? I know I've been asking a lot, and thanks so much for your help!!
July 16, 2009 at 10:24 pm
Ah yes - that copy line was causing me pain because my file system does not have the path you were using, so I muted it 🙂
Change your Evaluation: Expression and Constraint (you want both to be true, not either - this is likely to be your problem)
Value: Success
Change your Expression 1: @[User::RowCountResult]==0
Same thing for your other branch - except it has
Expression 2: @[User::RowCountResult]>0
See how that goes.
It's been a journey!
Phil
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 17, 2009 at 7:17 am
OK, everything's working perfectly now. I just need to get Database mail to email me the file attachment. But that's for another day. hehe Thanks so much for your help! You'd get tons of rep points if I knew how to do it and if it's available! 😉
July 17, 2009 at 7:29 am
It's always a pleasure helping someone who's enthusiastic and eager to learn.
No rep points here - just hoping that good SQL karma will come back to me when I get back to the UK soon & try to find somewhere/someone that will hire me:-)
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 22, 2009 at 7:54 am
I accidentally deleted a SSIS package from solution explorer. Is there anyway to find it back?
July 22, 2009 at 8:04 am
Eek. Stop all activity on your PC and find an undelete app from somewhere, after first checking that the DTSX file is not magically in your Recycle bin.
You should also check the folders under the solution - maybe it's still in the BIN folder. (BIN = Binary, not 'rubbish bin'!)
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
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply