0

I have an SSIS package which pulls in a CSV file for processing which pulls one file for the source \\server\dash\LABORDERS.CSV and is working fine.

enter image description here

We wanted to keep older files for historic purposes so everyday there will be new files instead of just overwriting the old one and it looks like this:

enter image description here

I know I am suppose to add a script task but I am not sure where to add it and how to invoke it so that the source file is always looking in the folder for the latest file and using that file to transfer data to it's sql destination.

How can I achieve it?

Si8
  • 9,141
  • 22
  • 109
  • 221

1 Answers1

1

What have you tried? You can could create a script task at the start of your control flow that uses the .NET framework filesystem objects to search a directory and get the file with the most recent timestamp. You could then assign that file name to a SSIS Variable, then use that variable in your file connection manager.

N West
  • 6,768
  • 25
  • 40
  • I was looking through many tutorials but none of them are very clear on the explanation. I did create a variable called `CurrentFile` and then I am stuck. I dropped the Script Task in my SSIS package and I am not sure how to go forward with it. I would request some help with this process: `uses the .NET framework filesystem objects to search a directory and get the file with the most recent timestamp. You could then assign that file name to a SSIS Variable, then use that variable in your file connection manager`. The file names already have the `mm-dd-yy.CSV` format so it should be easier. – Si8 Apr 09 '14 at 19:58
  • If you don't know the basics of C#, then you should start with some basic C# tutorials available on the Web. Some example code for finding a file in a directory: http://stackoverflow.com/questions/1179970/c-sharp-find-most-recent-file-in-dir – N West Apr 09 '14 at 20:00
  • Read through the MSDN documentation on script tasks for more information on how to use them. This is a Q&A site, not a "do my job for me" site. – N West Apr 09 '14 at 20:01
  • I understand the C# part. The only thing I have a question about is using the variable in the connection manager. – Si8 Apr 09 '14 at 20:02
  • You are absolutely right and I don't mean to come that way but It is a question :) – Si8 Apr 09 '14 at 20:02
  • 1
    For setting the variable - go to the Properties of the File Connection, expand the "Expressions" and set the "ConnectionString" to an expression using your CurrentFile variable. Then on execution it should use whatever is in the variable name as the source file. – N West Apr 09 '14 at 20:03
  • Ahhhhhh... So set a variable and use Script Task to set the variable to the most recent filename and in the expression set the variable to the source and file and it will work? – Si8 Apr 09 '14 at 20:06
  • I keep getting an error with `using Microsoft.SqlServer.Dts.Runtime` stating `The type of namespace name 'Runtime' does not exist in namespace`. Any idea how to resolve that issue? I set the target to .Net 3.5 instead of .Net 2 but my code does not run because of that error. – Si8 Apr 09 '14 at 20:09
  • Not sure. You should be building the task inside of BIDS - that runtime should be automatically included. Try saving off your code and recreating the script task from scratch. It's fussy at times. – N West Apr 09 '14 at 20:14
  • I am currently building it in BIDS and I deleted and recreated the same script multiple times but to no avail. I think that is the reason I am unable to move forward :/ – Si8 Apr 09 '14 at 20:16
  • You might not have all the necessary components installed. The Client Tools Connectivity and Client Tools SDK might not be up to date. – N West Apr 09 '14 at 20:20
  • One thing you can try - Try adding a reference to the DTS assemblies in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies - if they aren't there, try installing the client tools from the SQL server installation media – N West Apr 09 '14 at 20:21
  • I added the `Microsoft.SqlServer.ManagedDTS` but there is a exclamation mark next to it... Do you know what that means? – Si8 Apr 09 '14 at 20:23
  • I am running VS 2008 if that helps any – Si8 Apr 09 '14 at 20:28
  • I ended up using the EXPRESSION in the connection manager to enter the date dynamically without a script task. Thanks for the help. Accepted for trying to help me. – Si8 Apr 10 '14 at 13:49
  • Sounds good! As long as your file is named correctly, that should be fine. – N West Apr 10 '14 at 15:33