SSIS export to new excel file

Let me know which the error you have.
I can attach here a sample SSIS Project.
You are importing from SQL to excel, and want the connectionstring with the datetime in the filename, and the file created dinamically, is this correct?!

Be sure to put two back-slashes for every back-slash because they are considered an escape character in Expressions. C:\Temp would look like C:\\Temp.

Here is an example of an Expression to create just a filename - not the path, adding the curent date:

"dw_batch_"
+   (DT_WSTR, 2) MONTH( GETDATE() )
+   (DT_WSTR, 2) DAY( GETDATE()  )
+   (DT_WSTR, 4)  YEAR( GETDATE()  )

I have tried the connection string dynamic build, as the SSIS package has other functional expressions for other abilities, I have been reading about dropping tables and recreating tables in the excel connection mananger, but every time I dynamically assign variables for that component I get:SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

put delay=true on conenction properties

@PedroCGD:

correct, I want to generate a new excel document suffixed with datetime that gets kicked off from a job to populate the newly generated excel document, I put @[User:FileName] which is generated dynamically, depending on what the SSIS was asked to do, it has 7 other evaluation dataflows, depending on what Extract variable was passed to it, now one of them has to make excel instead of a flat file, and I cant seem to assign dynamic variables to the excel connection manager like I can to the database connector, and the flat file destination handlers.

This will add Date and Time in the form of a String to an FileName. In This case I am writting to C:\Temp\Excel\myfile_YYYYMMDD_HHMISS.xls

"C:\\Temp\\Excel\\myfile_"
+ (DT_WSTR, 4) YEAR( GETDATE()  )
+ RIGHT("0" + (DT_WSTR, 2) MONTH( GETDATE()  ) , 2 )
+ RIGHT("0" + (DT_WSTR, 2) DAY( GETDATE()  ) , 2 )
+ "_"
+ RIGHT("0" + (DT_WSTR, 2)DATEPART( "HH" , GETDATE()  ) , 2 )
+ RIGHT("0" + (DT_WSTR, 2)DATEPART( "MI" , GETDATE()  ) , 2 )
+ RIGHT("0" + (DT_WSTR, 2)DATEPART( "SS" , GETDATE()  ) , 2 )
+ ".xls"

Delay validation = true for excel connection mananger already

ok... I'm doing an example for you!
just a moment

Debug: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

What is the value you are trying to assign at the point of the error?

How are you trying to assign the ConnectionString dynamically? Excel Connection Manager expressions?

You have not even got here yet, but you will also need to run a create table statement on your new Excel File to hold the data.

Also, I have found the best choice is to create a template Excel document. This already has the "table" created. You use a File System Task to copy the template to your new FileName, then when your Excel Connection Manager looks for that file it is already there -ready for data.

OK...
To create the excel file dinamically, you should have the file already created in the destiantion. SSIS doenst create the excel file for you.

1. You can should have a excel file to copy for a destination directory that will be renamed using the date format with a script task. Could be a template file as Hogg suggested.

Give feedback

ConnectionString=@[User::FileName]

I have read that you need a template file to map to, thats fine, but all I can get it to do is append the template and/or make the dynamically named file that has no data. I dont mind the template idea if it will only make itself a fresh file every time the job is called, create the header column names in row 1, and then append all the relevant data into the excel document, ExcelDocument_yyyymmdd.xls.

@HoggZilla: I have the script already designed to create the dynamically named file:

Dts.VariableDispenser.LockOneForWrite("User::FileName", vars)
            vars(0).Value = strEnv + strFilePath + strFileName_Prefix + MyDate.ToString("yyyyMMdd_HHmmss") + ".xls"

however, when I assign [User::FileName] to the excel connection manager connection string, this is when I get the error.SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Ok, first thing I need you is put a breakpoint at the time you populate the FileName variable, look in the Locals window and get me the value of that variable.

Next, before going to the Data Flow Task to write to the Excle File, you will need to put a File System Task in to make a copy of your template Excel File - the one you can write to without a "dynamic" connectionstring, and copy it to the new FileName from your variable.

Then, let's validate that your ConnectionString expression is setup correctly. You have the connectionstring defined by an expression in the Excle Connection Manager properties?

Let's validate these first and then go from there. This is a very common procedure what you are trying to do, but there are many "gotchas" in getting it done.

OK, had not thought of the file system task to create a duplicate. My destination is biztalk consumption, so placing a template there will instantly get devoured by biztalk.

"the one you can write to without a "dynamic" connectionstring, and copy it to the new FileName from your variable."

are you stating that I read the template .xls into a variable inside the SSIS? wont the file system task allow me to copy the template from a 'safe zone' on the biztalk to the 'landing zone' area, and will it stay long enough for me to append data to it? Also, wouldnt I have to generate the filenaming convention prior to the file system task so that the excel connection will know what file to update?

The connection string expression is configured in excel connection manager as an expression.

filename break:

c:\PROVIDERUPDATE\OXFORD\ProvUpd_PL_datetimestamp.xls

and dynamically built from package variables set in the job:

@[User::Env]+@[User::FilePath]+@[User::FileName_Prefix]

"the one you can write to without a "dynamic" connectionstring, and copy it to the new FileName from your variable."

are you stating that I read the template .xls into a variable inside the SSIS? wont the file system task allow me to copy the template from a 'safe zone' on the biztalk to the 'landing zone' area, and will it stay long enough for me to append data to it? Also, wouldnt I have to generate the filenaming convention prior to the file system task so that the excel connection will know what file to update?

The connection string expression is configured in excel connection manager as an expression.

When you first build the package, before you try writing to a "dynamic" file - the one that we are trying to create, you first wirte to a specific xls file. Maybe on your c:\temp or wherever. But that file is your beginning of the template. So, once you have written to that file, validated it works, then save it as your template. Something like MyTemplate.xls. Highlight all of the rows below the Heading and right click - delete. Make sure it deletes the rows, not just the data. And that is your template - ready to start writing at Row2.

I'm almost done you SSIS example... just one more moment

ok, I have been able to export data to the .xls file, it appends to the template, so I can investigate that avenue. I look forward to the example PedroCGD offers.

Thanks guys, the SSIS package is for 7 clients, all get flat files, except this new requirement that one of our vendors wants .xls and not just .csv saved as .xls. This will be a weekly job that needs to generate a new .xls each week with the datetimestamp.

I appreciate all the help here, its nice for once to get intelligent information :)

we have multiple environments with different folder structures, therefore I have to make the biztalk server, filepath, and filename_prefix dynamic for each job in each environment.

wahoo, yes, this explains how I can do this, thanks both for help.

I split the points, thanks again.

confirmed confirmation @PedroCGD: example works as designed. Took some 'tweaking', thanks.