Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drivensql
Starting Member
9 Posts |
 Posted - 2011-02-03 : 10:37:38
|
| Thank you so much in advance - I need help solving the issues below.1. First, I'd like to fix the script below which opens the files in excel - currently the script is in an endless loop - without stopping once a file is picked up (I have to CTRL+Break once the first file or two is added and delete any extraneous files that come through manually)2. Then I'd like to automate this process using SQL Server 2008 / BIDS. I've set up the connection managers so far... I'm having problems with Execute SQL Task? Compute File Path to do what the script below does? Enclosing this in a loop container?Should I skip the first step and work on this directly in BIDS Integration Services?[CODE]Sub OpenFile()' OpenFile Macro'Opens a sheet based on date searchDim TheString As String, TheDate As Date, ThePath As StringThePath = "c:\Data\"TheDate = Date - 1TheString = "filename" & WorksheetFunction.Text(TheDate, "MMDD") & ".CSV" Do On Error Resume Next Workbooks.Open ThePath & TheString If Err <> 0 Then TheDate = TheDate - 1 TheString = "filename" & WorksheetFunction.Text(TheDate, "MMDD") & ".CSV" End If Loop Until Err = 0 Sheets("filename" & WorksheetFunction.Text(TheDate, "MMDD")).Select Sheets("filename" & WorksheetFunction.Text(TheDate, "MMDD")).Move Before:=Workbooks("DATA.xlsm").Sheets(1)End Sub[/CODE] |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-03 : 14:37:24
|
in Bids you would make a variable for the path and set that variable as the source connection for the flat file in expressions (set up the flat file first the normal way). For example, the variable would be [@USER::FilePath]You would only have to format the string and the set the variable in SSIS Script task. You can then have a for each loop point to a folder.Good description here:http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
|
|
|
Open previous business day file w/ VB