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.
I have an exiting excel workbook say master.xls. Now I need to dynamically create and append a new worksheet to the above master.xls every month end using the Reporting services.
Could you please guide me how dynamically creating the worksheets task can be achieved using the reporting services?
Your any guidance or help in this matter will be highly appreciated.
My SSIS package automatically creates Excel files everyday. i want the excel files to get todays date when they are created . Eg:06152008 for today and 06162008 for tomorrow please help
Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.
Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.
What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:
1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.
2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).
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 .
I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
I have a created a DTS packges which is reading data from sql server table, manipulate this data as required and then create a text file with that data. I created the text file using FileSystemObject. I was writing one field at a time to the text file.
I need to same thing but instead of creating text file, I need to create a excel file with each column from database going to separate column in excel sheet. I tried to do this with FileSystemObject, but it was wrting all the columns from database to one cell in excel sheet. How can I fix this problem?
Hi - hope someone can help. We're creating a ASP.NET C# site that allows a user to connect to any of our 100+ SQL Servers and query a database that sits on every server. I'm trying to work out (and failing) how we can set the 'Data Source' in a connection string dynamically using the server name selected by the user in a drop down box. Any help appreciated, thanks in advance,
I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.
I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.
Any help in pointing me to the right direction is appreciated.
I want to be able to generate a table on the fly. I know I can use dynamic sql to do this but my brains have forzen this afternoon so any help would be much appreciated.
I have the first query that returns one column with three rows. The contents of the these three rows will form the new columns in the dynamically generated table. How can I do this?
it usually works, except when the name of the database (xxxxxx) contains a number in the beginning of the name or if it is all made up completely by numbers. I also converted the database name into a string, but it doesn't work either.
I was given what at first seemed a simple task (and maybe it is, but with everything else on my plate, I can't seem to get my head wrapped around this)...create a grid or table something similar to below: Plan A Plan B PlanC PlanD Item1 yes yes no no Item2 yes yes no no Item3 yes no yes no etc.....
The "plans" are stored in one db table, the "items" in another. What I can't seem to get straight in my mind is how to relate the yess and nos and then generate a tabular layout similar to the above. I don't need anyone to do it for me, but a push in the right direction would sure be nice (using SQL 2005) Thanks!
Folks, Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?! Something like alter table add column+@i The alter table statement is within a Cursor and ideally @i should increment each time and alter the table to put in a new column. If I went 3 times thru the loop, I should have column1 column2 column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
Has anyone found out how to dynamically create report width.
If I have 20 columns on a report all of 2cm, then report will be 40cm width.
If I display the columns based on specific criteria using the iif function and then setting the an expression for the Visibility Hidden property, the report could potentially reduce in width.
I cannot see how to build an expression for the report width or table width, to accommodate the potential change in the width of the report when columns are not shown, thus it pages incorrectly, leaving huge white space on the right of the report when columns are hidden... has anyone come across this requirement, and hopefully a solution for it?
Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy
i have a ssrs report in which i have creating rows with heading of months name from jan to dec i.e. jan|feb|mar|apr|....i have a parameter start date.i want when i enter start date from march supoose i enter date 03/22/2015 then display months from mar to dec.
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.
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100) Declare @File varchar(100) set @FileName='brkrte_121227102828' SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1) --=select @File
[Code] ....
Error massage:- Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.
I have three databases which have the identical data structure. When the user runs a report, he has to select a database. For example, my report query would look like:
Select * from <theDatabase>.dbo.MyTable
How could I assign the value of <theDatabase> at run time depending on what the user selects from the list? In my datasource, I have only specified the server name and no value for initial catalog.
If I run the following command in a Query window it works:
RESTORE DATABASE CIS_Source_Data_Test FROM DISK = 'y:CIS_Source_Data_backup_2015_06_17_085557_7782407.bak' WITH RECOVERY, REPLACE
If I dynamically put together the command and store it in variable @cmd and then execute it using exec sp_executesql @cmd or exec (@cmd) it does not work. I get the following:
Msg 2745, Level 16, State 2, Procedure CIS_Source_Data_Refresh, Line 92 Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process. Msg 50000, Level 20, State 1, Procedure CIS_Source_Data_Refresh, Line 92 RESTORE DATABASE is terminating abnormally. Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Why it won't work when I try to create and run it dynamically?
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 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.
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.
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.
I have an OLEDB source that uses a stored procedure which pivots records and returns me data with columns which are dynamic (Changing every time). How can I export this data with dynamic number of columns to excel destination?
