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 |
|
billsack
Starting Member
35 Posts |
 Posted - 2009-08-03 : 11:19:44
|
Hello experts,We have finally made the transition to SQL 2005. I am re-writing DTS packages in the new environment. I have a simple task that I cannot get done. I am trying to get the ssis package to create a new excel file appended with todays date each time it is run and then export data to that file. With DTS I did this using activex.I have tried to set two variables:1. RIGHT( (DT_STR, 4, 1252) YEAR( GETDATE() ),4) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ),2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ),2)Gives me the date and 2. "C:\\myfile"+ @[User::TodayDate]+".xls"Gives me the connection string. The problem is that I cant get this working. I have spent all day looking at it and my brain is fried. HELP!!!!!!!! |
|
|
billsack
Starting Member
35 Posts |
Posted - 2009-08-03 : 11:44:58
|
| Sorry. I should have said that I then want the SSIS package to export data to the excel file outlined above ie:C:\\myfile20090703.xlsWhat I cannot do is set the connection properties to reference this file. I get a message saying it doesnt exist.Thanks folks. |
 |
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2009-08-04 : 07:11:49
|
| Set package DelayValidation = True |
|
|
|
billsack
Starting Member
35 Posts |
Posted - 2009-08-04 : 10:48:38
|
| Thanks. I have tried that already but its not working.The connection for this task is displaying the path but I get the same message:Error: 0xC00291EB at Create Excel Sheets 1, Execute SQL Task: Connection manager "C:\myfile20090804.xls" does not exist. |
|
|
|
billsack
Starting Member
35 Posts |
Posted - 2009-08-05 : 06:16:38
|
Ok I have it:1. Create variable with excel file destination2. Create "ExcelFilePath" expression within the excel file connection that uses the variable above.3. Hey presto it works.Its actually easier and more straightforward than DTS. You just need to get used to how SSIS is working. |
|
|
|
|
|
|
SSIS Using Variables