How To Dynamically Generate Excel Files For The SQL Query Input?
May 22, 2008
Hi,
There is one manual process done by my friend. The process is " In query analyser, she runs a sql query and the resultset of Sql query, she copies and pastes into a excel workbook".
I wanted to write a SSIS package to automate it and providing a UI, in which she can give connection details and paste the SQL query and the package will in turn generate excel workbook in the target path". I thought of trying ExecuteSQL task, but the output resultset of ExecuteSQL task cannot be mapped dynamically to excel source(correct me if I am wrong). If I use Dataflow task, then also whether dynamic mapping of SQL output to Excel destination input is possible?
Can you please tell me the best approach for achieving the above functionality?
I have created a package which import data from excel file and do some technical & business validation on the data. My package has about 20 control flow items. Now I'm asked to handle a second (and probably more in the future) excel file format (columns name are different, some fields are murged in one single column...).
I definitely don't want to create a different package for each excel file format. But I can't find a way in the control flow to execute a particular DataFlow in one case and another DataFlow in other cases. Typically I would like to evaluate an expression an depending on the result execute a DataFlow or another one. Even in a given DataFlow I cant find a way to have a condition and process different Excel Source depending on an expression result. Or it would be good if I could say to my Excel Source to discover the columns name and types at runtime and let me manage the columns manually in the data flow. Is that possible ? I know SSIS manage metadata on the columns based on the data source is there any way to manage the metadata manually ? I coulnd't find anything about that in BOL.
I guess an easy workaround is to have a different package just to import the different excel files in a common staging table and each package calls a single package which contains all technical & business validation.
I'm new to SSIS and have a question about the best way to generate multiple Excel files with my current package design. I have a stored procedure that I run from an Execute SQL Task in a foreach loop container, and it generates results as appropriate for each of the parameters it loops through.
What's the best way to take the result set from each of those executions and generate an Excel file from each set? How do I map that result set variable to be the input for creating a new Excel file? Is this best done as a script task somehow?
Should I not be using SSIS for this task? I thought I would just create a package and schedule it to run daily with SQL Agent, and it would autogenerate the Excel files as needed.
Hi, how I do create SqlDataSource in the code-behind,setting Select SQL along with its parameters and, finally automically generating Del, Update, Insert SQL. Is there a way to achieve this goal as it would in VS designer? Thanks, Ricky.
I have a handful of reports that are currently used by sales reps, and I'm trying to make them available to their regional VP's, and coporate users (executives and administrative staff that support Sales nationwide).
Currently, the reports take the UserID and resolve it to show the information that is only appropriate for that specific rep.
What I would like to do is have the parameter section at the top of the report be displayed for higher level users, so they could select an individual sales rep from a drop-down. (Ideally, the RVP's would only be able to select from reps in their region, but the corporate users would be able to select any rep.) The problem is, I don't want any of the sales reps to be able to select a rep other than themselves, for obvious reasons.
Is there a way to have the parameter section hidden/displayed dynamically, based on the UserID, so that users other than reps would have the ability to enter the desired rep name, but reps would not?
I have to display three months name as column name in ssrs reports. These month column will be dynamically. So i have to display the column dynamically.
I need to generate a csv file from another csv file, seems to be simple but let's go the trick thing:
Needs to have maximum 1000 lines, if I reach to this, I need to create another csv and fill that new one.
Exemplifying:
I have a csv file called fileA and this has 2000 lines and another csv called fileB with 1500 lines.
I need to loop a folder and get the fileA, create an output called FileAOutput and start to fill that, if I reach to 1000 lines, I need to create a FileAOutput_2 and fill the other 1000 lines...so I'll go to fileB and do the same thing, but in the second case, I'll have 500 lines in the second output.
hi , I am trying to copy files dynamically from a remot folder by using File System task , the files are going to be generated daily with f1yyyy-mm-dd.txt I created 3 file connections : - Existing remote folder (RemotFolder) and set that to var v_remotefolder - Existing destination folder (DestFolder) - existing file (filename) and set the expression to var (string ) =v_fname in the File System task I set the the Destination connection = DestFolder Operation =Copy file Source = filename expression >>Property (source) = @[User::v_remotefolder] + @[User::v_fname] I know I need the date too ,but I 'm getting error on this step. Can you please tell me if you know how to resolve this without using For each loop since I don't have control on the remote folder and I don't want to copy unneeded files (the files are 10 GB daily) and get delete it once every month. -I know this not uncommon task but I can't find any example. Thanks
Hello All,Looking for an opinion on the following scenario.Lets say you need to create a report for a 1000 stores. The report format is the same but the data changes for each store.Option 1:Write an asp.net page where the user selects the store, or a store parameter is pased, hit SQL Server with a SP and display the data. - This option seem to have the advantage of flexibility and simplicity.Option 2:Write out a static XML/HTML report for each store and have the asp.net page load the static file. - This options seems to have the advantage of speed on the client side but is less flexible and more complex on the backend.Any thoughts?For option 2 is it possible to write out the XML/HTML files using SQL Server or does something custom need to be created?Thanks, Gary
I need to get a list of customer ids and then use them as a parameter to select from a transaction table and then create a file for each customer. I have used an execute SQL task to get the list of customers and have put the result set into a variable.
How do go through the recordset to create a file for each customer?
This script generates a format file for every table in a database.
It's set up for fixed width files, not too common in this world...but they can easily be modified.
Also the do not at this time handle text or image columns.
Any hints/advice here would be great.
I'll post it when I figure it out.
happy bcping..
SELECT FORMAT_CARD FROM ( SELECT '7.0' AS FORMAT_CARD , TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' UNION ALL SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD , c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND TABLE_TYPE = 'BASE TABLE' GROUP BY c.TABLE_NAME UNION ALL SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9) + CONVERT(varchar(5), CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH WHEN DATA_TYPE = 'int' THEN 14 WHEN DATA_TYPE = 'smallint' THEN 7 WHEN DATA_TYPE = 'tinyint' THEN 3 WHEN DATA_TYPE = 'bit' THEN 1 ELSE 26 END) + CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD , c.TABLE_NAME, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME AND c.table_schema = t.table_schema AND TABLE_TYPE = 'BASE TABLE' WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.Columns i WHERE i.TABLE_NAME = c.TABLE_NAME) UNION ALL SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5), CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH WHEN DATA_TYPE = 'int' THEN 14 WHEN DATA_TYPE = 'smallint' THEN 7 WHEN DATA_TYPE = 'tinyint' THEN 3 WHEN DATA_TYPE = 'bit' THEN 1 ELSE 26 END) + char(9)+'" "'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD , c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND TABLE_TYPE = 'BASE TABLE' WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.Columns i WHERE i.TABLE_NAME = c.TABLE_NAME) )AS XXX ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
I've one Dafta flow task where I'm getting data from OleDb source and then doing some scripting using script component and then generating a file. Now I would need to get the same data and apply some different things and generate another file. Can I used this same task for doing the secondry work? If yes how woulld I put the thing in place, I would need to get the same data but I would need to use a seperate scripting and generate a seperate file?
I've just started using SQL Server 2005 Integration Services and I've come up against a situation where I need to name output files dynamically (i.e. based on a timestmap). Looking through this newsgroup, and other web resources it looks like my only option is to use a Script Task/ActiveX Script Task to rename the file after it has been created with a generic file name. I was wondering if there was a different approach or if there was a way to pass in a variable to the file connection manager that I could append to the file name at run-time.
I am pretty new to SQL Server 2005 and SSIS. I am trying to develop a package that will dynamically load files into SS2005 based on the contents of a configuration table. The configuration table (see below for example) contains the path to the file, a flag indicating whether or not to process the flag, the type of file (specifies the nature of data -- financial, order, etc.) and some parameters specific to each file.
FileName ProcessFlag Type ExcelTab Param1
C:File1.xls TRUE 1 Sheet1$
C:File2.xls TRUE 1 Sheet1$
C:File3.xls TRUE 1 Sheet1$
C:File4.xls TRUE 2 Sheet1$
C:File5.xls FALSE 2 Sheet1$
C:File6.csv TRUE 3
C:File7.txt TRUE 4
Right now I basically have a seperate sequence for each of the file types. The task in each sequence are virtually identical with the exception of the the data flow source in the data flow task (since the source file could be .xls, .txt, .csv). The first sequence ran fine in isolation, but when I linked a second sequence I started getting a Package Validation Error: DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER I use the same variables (FileName, ExcelTab) for each of the sequences, so I am not sure if that causes te error. I also tried changing the ValidateExternalMetadata setting to false, since the connection variable wont be applicable until that sequence is being processed. I am not sure where to go here ... should I rearchitect how the package all together?
Is there a better/most efficient way to to architect this package to handle the different file types (develop a package for each type which is called by a master package, create one package with a different sequence container for each type, etc).
I am a very beginner in SQL and know a little bit VB.
I created a database table with two columns, one for key and the other for data. I'd like to update the data column using excel sheet which contains the same columns, key and data.
I am pretty new to SQL Server 2005 and SSIS. I am trying to dynamically load files into SQL Server 2005 using files/paths contained in tables. I have a key table in my SSIS package that defines which files should be processed, some default values that will be associated with each file (ie CompanyKey and PeriodKey) and the file path (see below for example):
CompanyKey
PeriodKey
ProcessFlag
FileName
2
61
FALSE
2005TB200501.xls
2
62
FALSE
2005TB200502.xls
2
63
FALSE
2005TB200503.xls
2
64
TRUE
2005TB200504.xls
2
65
TRUE
2005TB200505.xls
2
66
TRUE
2005TB200506.xls
2
67
TRUE
2005TB200507.xls
2
68
TRUE
2005TB200508.xls
2
69
TRUE
2005TB200509.xls
2
70
TRUE
2005TB200510.xls
2
71
TRUE
2005TB200511.xls
2
72
TRUE
2005TB200512.xls
The package I am developing is supposed to loop through this table checking for files with ProcessFlag = TRUE. For those files, it will load all of the records/columns (prefaced with the company and period keys) into a common table.
Do I have to manually create data sources for each file or is there a way to dynamically define the connection and process the connection?
Any assitance you could provide would be greatly appreciated!
I am trying to create a DTS package that uses a sql stored procedure to generate a set of results and export those results to an excel spreadsheet on a server.
The trick is that the stored procedure accepts a parameter for Bank_Number (there are 10 of them). Therefore i was wondering if there was a way to somehow create the package to run the stored proc 10 times, each with a different bank number as the parameter and generate 10 different excel spreadsheets, one for each bank with it's results.
Can this be done using DTS or do i have to try another method?
Curently I am using a DTS package which is used to import data from Excel sheet into sql dollar table.
Now, the no. of Excel sheets is more than one and everytime the DTS package and VB Code has to be updated and sql dollar tables has to be increased to the no. of Excel sheets available.
The DTS package being executed by VB Code(.EXE).
How can I modify the DTS package and VB Code so that the import can be done dynamically irrespective of no. of Excel Sheets.
I have one share folder ,every month end-user will copy & paste excel file into particular share folder. Ok . Now i have to create new SSIS package as schedule should run every month to find the file and then load automatically into Sql server tables and then move those excel file to another share folder if file successfully loaded only. The excel file name will be changing every month. but the format wont change. If any body knows this process or steps. Please share with me .
Hi everybody, i'm a newbie to SSIS and I'm having a problem dynamically creating a new excel spreadsheet in SSIS. What I need to do is be able to dynamically create a brand new Excel spreadsheet after a data flow task completes.
In my SSIS package I have a loop container that I am running the same code against 4 servers. I have the package export the SQL data to an Excel spreadsheet that has multiple tabs.
Is there a way I can change the tab on the fly or do I need to create a Connection for the same spreadsheet 4 times Each Connection pointing to a different tab?
I tried to set up a expression for the Excel Connection Manager to use the InitialCatalog for the tab and change it based on the script in the loop however this causes the following error:
An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Cannot create an OLE DB accessor. Verify that the column metadata is valid.
What object do I reference to use SSIS from Excel. I want to generate a flat file definition based on Excel. I have a lot of fields to import and I don't feel like creating them as flat file columns. I have a few tables and I get the source file format from the vendor in an Excel format. What I would like to do is generate a flat file connection in an empty package using VBA.
Every day an application creates new tables and dumps static info into them.
I would like to create a package to dynamically export those database tables to raw files for long term archive, one file per table. Here is what I have so far and the issue I am having.
1) Get a list of un-archived tables. 2) Foreach table do the following.
a. Export the table into raw file. b. Zip the raw file. c. Update archive tracking table.
As long as the metadata for each table is the same this package seems to work fine. However, I have many tables with different metadata. How can I dynamically get the package update the metadata column collection when it hits a new table? When it hits a table with different metadata I am getting warnings like this:
The column "some_column" needs to be added to the external metadata column collection.
The "external metadata column "someother_column" (103)" needs to be removed from the external metadata column collection.
Then I get this error: Error: 0xC004706B at dump the table into a raw file, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA"
I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?
I have an issue where I'm trying to export data from Sql Server tables (or from a result set in a SP or view) into Excel Spreadsheets. Normally I would use a simple data flow to do this. However, I need to do this on-the-fly because the schema of the Sql data is not static. The table could be a different one or the result set would have column schema that is not always the same.
The constant in all of this is that the spreadsheet columns and the table (or result set) column schema is identical. It's just that the column count and column names are not defined at design time, but would need to be defined at runtime.
Going from Excel to Sql Server is simple as I used a Script Task and the SQLBulkCopy class to dynamically transfer the data. However, BOL says that it's only one way (Data to Sql Server). Basically I need the to go the opposite direction now.
I have all of the information (SQL Table server, database, schema, and name and the Excel file path and name) already set up in variables and running through a ForEach container and I can dynamically change the variable information. I just need to figure out how to dynamically map the columns, create the spreadsheet file, and load the data into the spreadsheet. I'm sure this has been tossed around before. If someone could point me in the right direction I would most grateful.
