March 30, 2010 at 7:51 am
I am trying to create an SSIS job that outputs an Excel file with a file name similar to Report-20100330.xls with the current date of the job run. I think I am close, after searching SQLServerCentral and the Internet I found a blog entry by MVP Rafael Salas that has gotten me most of the way, but it only works once. Will you help me find the piece I am missing?
I created a Data Flow Task as an output from the Execute SQL Task. Inside the Data Flow Task I added an OLE DB Source. I created an OLE DB Connection to my SQL database. Set the Data access mode: to SQL command, and entered the following query:
Select OrderNum, Date, Amount from orders order by Date
Then I created an Excel Destination as an output from the Ole DB Source with a path of C:\Temp\Temp.xls.
I created a new Name of the Excel sheet with the following create table command:
CREATE TABLE `Excel Destination` (
`OrderNum` INTEGER,
`Date` DATETIME,
`Amount` MONEY
)
I went to the Mappings page to verify the mappings are correct and clicked OK.
I ran the package and up to this point everything works perfectly.
My next step was to create Execute SQL Task in the Control Flow tab with an output to the data Flow Task created above. I changed the ConnectionType to EXCEL, the Connection to Excel Connection Manager. On the SQLStatement I entered this command:
CREATE TABLE `Report1` (
`OrderNum` INTEGER,
`Date` DATETIME,
`Amount` Varchar(50)
)
I created a variable called ExcelFileName,
Scope: Package
Data Type: String
Value: c:\Temp\temp.xls
In the properties of the variable I changed
EvaluateAsExpression: True
Expression: "c:\\Temp\\Report-" + (DT_STR, 4, 1252) YEAR( GETDATE()) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE()), 2) + ".xls"
On the properties of the Excel Connection Manager I changed
Expression:
Property: ExcelFilePath
Expression: @[User::ExcelFileName]
At this point the package runs fine and I receive the output excel spreadsheet Report-20100329.xls with the correct data. Everything is perfect.
On the next day, the job fails with Package Validation Error. I find that in the Excel destination inside the Dataflow task, The Name of the Excel sheet has “No tables or views could be loaded.” So I think I am missing something that resets the Excel Destination after each run. But I don’t know what that is.
What am I missing?
March 30, 2010 at 9:45 am
Did you change the connection String Property of your EXcel File which should look something like this based on your EXCEL Version. The Variable "ExcelFilePath" holds the path of the File " C:\Temp\Report1_03292010.xls "
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+@[User::ExcelFilePath]+" ;Extended Properties=\"EXCEL 8.0;HDR=YES\";"
March 30, 2010 at 11:05 am
Showing my ignorance here, but where do I look to verify/change that?
March 30, 2010 at 11:58 am
click on Excel connection Manager and on the rt side Property pane you should see Expressions.. you will also see a Connection string property, try to make a note of it and then using the expression pane for connection string change its properties..
you will have to replace your "file path" with the "file path variable" using expressions as i have mentioned in my earlier post
March 30, 2010 at 12:06 pm
MVP Todd McDermid gave me the answer to my problem:
Have you tried setting DelayValidation to true for the Connection Manager and Data Flow Task?
That resolved my problem.
Thank you for all your assistance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply