How To Include Looked Up Value In Excel Output File Name
Aug 3, 2007
I have set up an export package in SSIS and I would like to assign a file name based on the value in a table on my SQLServer. For example, my file name might be Monthly Reporting Package 20070731.xls where 20070731 is a value in my date table. I can retrieve my date by the following select statement: "SELECT Date FROM DecodeTables.dbo.Date". Can I set up a variable to look up this value and then concatenate it into my file name?
Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?
Also an easier question: What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)
I would like to create a SQL job that runs a basic query monthly and saves the results as a .xls file and emails the results to a user. I've been unsuccessful with sp_send_dbmail in that the output file is always a single column. Any suggestions?
I'm a newbie to SQL. I'm using SQL Server 2012 on my local machine and I need to find a way to output my queries as Excel files. I came across these codes for Interactive SQL (what is intercative SQL by the way?) but they don't work in the SQL query window:
SELECT * FROM SalesOrders; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls); DBQ=c: estsales.xls; READONLY=0' INTO "newSalesData";
It seems the "OUTPUT" command is not a valid command. I really liked that piece of code (may be because it is so simple and carries over very few lines)! Do we have something similar for SQL Server 2012 that can do the job?
I need to create a query (SQL 2000) that renders a formatted excel (xml or xls) file for each row that is outputted.
The details, I have a Campaign table that contains information for Auto and Life "Leads" and the data is submitted by telemarketers directly into the database. I need to render a file for each line, and it would be good if It were an Excel XML or XLS file, because that's what we've been using for a while.
I'm writing an SSIS package to run a series of select statements and save the results of each to a sheet in an Excel file. An indirect configuration file with these entries is used:
At the start of the package this setup will happen.
1) copy ExcelTemplateFilePath to ExcelFilePath
2) Set User::ReportFilePath to ExcelFilePath
Creating a script task to do this is not a problem. However the task fails on validation of the tasks that write the select results to the Excel file sheets. I'm thinking the validation fails because the Excel file is not copied before the validation is done.
How can I arrange for the setup script to run and copy the Excel file before validation?
Is there is a better way or am I just doing this wrong?
To overcome the inability to manage the sheet tab names when exporting from Reporting Services to Excel, is there anyway to include a short macro with the exported Excel workbook to Auto_Run macro when the Excel file is opened to rename the tabs?
I want to import date from excel file from the row if this row has a character like 'TableName', and I don't know the rownumber and end column number ,so I can't use openrowset 'select * from Sheet$ A11:N'. And I am thing if I can get the rownumber of the row contain string 'TableName' and the max column ,that May be easy to extract data.
I've already read many topics that was asked about SSIS runtime, but for my purposes I didn't find satisfied answer
Our clients don't use SQL Server for their data. But we anyway used DTS packages for transfering data between client DBes. Only thing that we needed to install was DTS dlls.
What will I need to do now to use old DTS and new SSIS packages on these clients?
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
ok...i give up..... can this be done? (sql server 2000) all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp. here's my current working file output code: EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees.txt" -c -Usa -P' i'd like it to be something like this: EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees" + datetime + ".txt" -c -Usa -P' but nothing seems to work.
We have an application group that wants to pull date from SQL Server and write it to text file on the server. They want the file format to be 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a second set...and so on. The '12100' is fixed, but the rest of the file name will always have to include the system date/time. Is there an easy way to do this within a DTS package (when writing to the output file)? I would really appreciate help on this. Thank you.
I am trying to move a flat file to an archive folder and then rename the file to include the date and time of runtime.
I have set up a File System Task to move the file which works fine and a second File System Task to rename. The IsDestinationPathVariable is set to TRUE and the DestinationVariable is called User::Error_Log_File_Rename. There is an expression as follows:
If I go into the expression and evaluate it it will give me the current date and time. However when I run it as part of the package say 10 minutes later the file is renamed using the same date and time from 10 minutes before when I evaluated the expression. If I wait another 10 minutes and run the package again it still retains the original date and time.
Is there a way to get the expression evaluated with the current date and time during execution of the package?
Is there anyway to send excel file from ssis using send mail task without saving the excel file locally. I need to automate the process which involves loading the excel file from the database and send it to some people.
I am created a SSIS package to export data. I am exporting query data to a flat file to a different server. I tried to use the UNC path and it failed saying could not access the file. How can I create a SSIS package to export data from one server to another?
Trying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
We have 10 sheets in Excel File and 10 sheet contains errror data. How to load 9 sheets data in to 1 destination and error data in to other destination?
i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated
I am using a sql command to produce outputs having 3 columns Before running the sql I am spooling the output in .xls format But when I open the .xls file (i.e. the spooled file) data of all the 3 columns appear in a single column with blank spaces between them Is there a way by which I can ensure that each output column of the SQL appears in a seperate column in the excel Sheet
I need to get the output of a SSIS package in an Excel. I have made a Stored Procedure for getting the output, since the output has variable number of columns hence i have made a table through the stored procedure which is dynamically created when the procedure is executed. Now i have a table in SQL with the output data.
In my report I have used the "jump to url" for the report output but when I export this to excel I get a column with the correct data and also the url.
Is there anyway of exporting the data without the url?
I want my users to be able to view the report and click on the column the want to URL jump to but also be able to export just the raw data without the URL?
SET @RowCnt = 1 SET @date = CONVERT(CHAR(10),GETDATE(),110) SET @ArchPath = '\D$EDATAWorkFoldersSendSendData' SELECT @TotalRows = count(*) FROM table1 --select @ArchPath
WHILE (@RowCnt <= @TotalRows) BEGIN SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt --PRINT @AccountNumber --test SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = ''' + @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c' --PRINT @sql EXEC master..xp_cmdshell @sql SELECT @RowCnt = @RowCnt + 1 END
I am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.
the command I am using is this.
declare @x varchar(300)
set @x = 'bcp "select * from Northwind..orders" queryout c: est.csv -S local -U sa -P passwd -c -C RAW -t "," -r '
I use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
I have this script which outputs a combination of financial data. I have recently joined a table that includes a narrative and when I run the query, it runs perfectly. However, when I copy and paste the output into Excel, not all the data is showing. After the process of elimination, it is because Excel doesn't like the narrative column and not all data is copied across which is very annoying. If I remove the narrative column, then all the data copies over correctly.
I created an Excel Source and used a query to get the data,i.e
SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]
The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?
Deaa group,I am using SQLServer 2000 in an XP Sp2. I would like to do thefollowing:I have a program running on a database server that generates some datawhich are loaded to the database. This program is used in a webapplication, invoked by some java program and JSP scripts. (I amfrontend illiterated.)The question is, is it possible to write a stored procedure to generateoutput in excel spreadsheet? So that user could call this procedureand get spreadsheet output on the client side.Any pointer to a solution would be immensely apprecaited.thanks,charia
INSERT INTO RCSAdvantage VALUES('123','Mike','Bhatt','12/12/2003','123','RCSA')
INSERT INTO RCSAdvantage VALUES('TM123','Mike','Bhatt','12/12/2003','456','TRIMICRO')
INSERT INTO RCSAdvantage VALUES('INR234','Mike','Bhatt','12/12/2003','890','INSIGHT')
INSERT INTO RCSAdvantage VALUES('INR234','John','Bhatt','12/12/2003','890','INSIGHT')
I needed to run following cursor and get the result exported to excel file. But Cursor retrives two resultset and while exporting to excel spreadsheet , it is the only first resultset without second resultset. How can it be exported to excel as a single resultset combined of first and second one.
Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).
I have a relatively simple task - create an excel spreadsheet with 3 columns of data - Id, Description and Sales. ID and Description are text, sales is int.
So my SP aggregates and creates my resultset in my OLE DB Source in the Data Flow. It proceeds to the Excel destination, and that all seems fine. My issue is that the data is being written as text. Looking at the excel destination in Advanced editor: the Excel Destination Input, Input columns are formatted as I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and DT_I4 for the Sales. Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.
The target excel spreadsheet is being created from a template file. That template file has header columns. The target column for the Sales has the entire column formatted to NUMBER (0 decimals). Yet to now avail.
When I check the spreadsheet, the column has retained the cell formatting, and I have a 'I' pop-up to inform me that 'someone' has inserted text data into the number column (even though the data IS number).
Since the SP spits out INT, it isn't a case of receiving a text value, imho. While trying to change the external column data type in the advanced editor, SSIS is quite happy to let me change the value for the Sales output to DT_I4, apply, and ok. Then, when I open it immedaitely aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't handle it, at least tell me when I try and change it. don't let me change it, and then revert back without telling me! Grumble grumble...
