SQLTeam.com | Weblogs | Forums

Variables in SSIS Package


SQL Server 2014 was just installed on a new server, along with the SSIS tool for modifying packages. I created a packaged with the Import/Export wizard in SSMS to import an Excel spreadsheet. I then load it in to Visual Studio because I need a dynamic file name for the Excel File.

I (try) create two the two variables below, but it seems to be too much for Visual Studio. When I click on Evaluate Expression I get 'Not Responding' for 3 to 5 minutes. If I am able to save it I get errors when reopening the package saying that it can't evaluate the expressions. Twice VS has crashed during saving and I've had to start over from scratch. I've been at it for an hour and a half.

Does there seem to be a problem the expressions below? Occasionally I can get all the way through the process and they do evaluate, but then VS crashes and I loose the work before I can save. Very frustrating.


PS I deleted the package, restarted the server, recreated it and the exact same thing happened. Clicking Evaluate or Save causes the software go in to Not Responding mode. I timed the last one and it was 9 minutes long.

@[User::FileName] = "S:\BILLING\VENDOR\" + (DT_WSTR,4)YEAR(GETDATE()) + " Member Eligibility\" + @[User::MonthName] + " " + (DT_WSTR,4)YEAR(GETDATE()) + "\Monthly Report.xlsx"

@[User::MonthName = MONTH( GETDATE()) == 1 ? "January" :
MONTH( GETDATE()) == 2 ? "Febuary" :
MONTH( GETDATE()) == 3 ? "March" :
MONTH( GETDATE()) == 4 ? "April" :
MONTH( GETDATE()) == 5 ? "May" :
MONTH( GETDATE()) == 6 ? "June" :
MONTH( GETDATE()) == 7 ? "July" :
MONTH( GETDATE()) == 8 ? "August" :
MONTH( GETDATE()) == 9 ? "September" :
MONTH( GETDATE()) == 10 ? "October" :
MONTH( GETDATE()) == 11 ? "November" : "December"


Visual Studio 2013 or 2015? Do you have all the applicable maintenance applied to VS and SSDT?

see here and here


Thanks for the reply. It is 2013. We did the install from the second link you posted. At 4:45 last night I clicked Save and got Not Responding. At 5:00 it was still not responding so I left. This morning it seemed to have saved and the package reopened without error. It seems responsive now, but I went to do a second one and the same thing happened..



Longshot: Database set to AUTO CLOSE ?


Auto close is false, but thanks.