SQL 2012 :: How To Avoid SSIS To UN-protect Excel Template
Nov 1, 2015
I am exporting the data from database to an excel template that will have 100+ columns and approx 4000 rows of data. Then the business user will make changes to some columns without modifying primary key columns and will send back to us where we will update the same to database.
In order to this am using an excel template by protecting the primary key columns with a password protection.
At template level am fine and whenever am trying to modify any primary key column it's not allowing and am totally good there. But when I use that excel template as a destination to load data from SSIS, all the protected columns are no longer protected and i could able to make changes.
I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database
I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..
In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically
I'm stuck on this one. I've got this package working for dynamic output based on an XML statement (yea!!!). It's actually a really simple package that has a few execute sql tasks and an xml task. I pass in a sql statement and get out raw xml that I use in the xml task and I use an XSL file to combine the xml into an excel document. My next obstacle is how to handle multiple worksheets in the same spreadsheet. Given the following code, xsl and xml, what changes would I need to make excel recognize that there is more than one worksheet?
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D: esting.xls;', 'SELECT * FROM [SheetName$]') select * from pubs.dbo.authors
I am using similiar query to above to create a excel file, however for this to work, I need to create a template file which has the same columns as the authors table. Is there a way to NOT to define template columns , as I some times will not know which columns will be available... as teh query is dynamic....
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?
Exporting a report to Excel has a lot of issues with appearance. As a result, I created an Excel template that I would like to use when the user selects to export the report to Excel, using the reportViewer toolbar. I haven't found any documentation thus far on how to accomplish this. Does anyone know whether this is possible with SSRS and if so can you please provide information as to how it is accomplished. I have created a similar post where my application code can be reviewed: http://forums.asp.net/t/1183977.aspx
I have an excel file which i am reading through SSIS through MICROSOFT Office 12.0 Access Database Engine OLE DB Provider. My problem is that if the Excel file has data in a column as 123.45 which is displayed as 123. SSIS is able to pick 123 only and not 123.45. however if I change the data type to Numeric in the excel, SSIS is able to read data as 123.45.Any solution other than changing data type in Excel?
I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file. My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel drive or excel itself on the machine in order to be able to import the data?
I am using vs 2010 and I have an .xls file that I am trying to import into SQL Server 2012, and I have most of it figured out, but I have a date field that is giving me problems, and what I would like to do is put that date in a variable so I can add it to every record in my SQL Table.
I am using a SQL Task Editor with an excel connection and I have no problem getting other data from the excel document and putting into my variable, its just the date that I have problems.
I'm trying to use Excel in SSIS to import the data from spreadsheet to a staging table. The package runs well from the web server using SSMS. But when I deploy and try to execute the package, I'm getting the below error. I've a question, whether I've to install the AccessDatabaseEngine driver in SQL database server or the web server where I'm executing the SSIS?
Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode.
I need to process a 2012 Excel file. In SSIS Connection Manager, I am only given an option until Excel 2007 version. When I use this in my connection for Excel Source, I am prompted with this error when I attempt to select the name of the sheet:
"Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
Failed to connect to the source using the connection manager 'Excel Connection Manager'"
is there some way to avoid single quote for all fields (including text) in excel when we export data from sql server? i dont want to have any single quote in excel when i export data.
I have a report with 3 matrix only one matrix is displayed at a time depending on a parameter value, I use an expression in the visibility Property, since then, when I export to Excel the Toggle buttons have disappeared.
I am sure this questions have been asked but i was not able to find a useful information.
1. I am looking for a SSIS Standards document or source. A document developer can use for developing ssis package. This should include how to name each container, task , and how to organize things. Basics of SSIS. Is there some kind of source where i can find this information . We are starting out to migrate from informatica to SSIS but before we do that we would like to put standards in place so all SSIS development is consistent.
2. SSIS project documentation template that we can use to document each project. Is there anything out there that we can follow to document each of our project.
I have a 400MB Excel file that I consume from another automated process (don't ask). I copy this file down locally to my server, and I am attempting to create an SSIS package that points to this file via a connection manager. My computer starts gobbling up massive amounts of memory (devenv.exe gets up to about 800MB or so, then drops back down to 100MB) even when I attempt to rename the connection in the connection managers tab.
I have set all BypassPrepare to TRUE and ValidateExternalMetadata properties to FALSE, and still it can take up to 3 to 6 minutes for BI Dev Studio to respond. My specs:
Intel Centrino Duo 2.00 GHz 2GB RAM XP Pro SP2
There MUST be a way for me to work effectively on a file of this size. Please help! Thanks much for any assistance.
Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:
;with rCTE(iterations,computed) as ( select 0 [iterations], 1e+0 [computed] union all select iterations+1,dbo.f(computed) from where rCTE where iterations < 30 ) select * from rCTE
Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]
However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.
I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.
I need to use WHILE to avoid Cursor under certains conditions.
My SELECT statement is:
SELECT ref, ano, numberofyears ,nreint, naoreint,degress, tabela, tax, taxamaxima,[evactual], [evaldepact],[ereintact],nrregbt,[taxAmtAno] FROM deprec ORDER BY [ref] ASC
numberofyears= 100 /tax for exemple for a good where lifecycle is 4 years ,ex: Tax = 25% Then 100/25 = 4 years
I see this WHILE script, but i need to run :
1. for each REF + Until years < 4 in this exemple, because i have goods years depend on Percent.
the WHILE script i see is:
DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int ) INSERT into @table1 (col1) SELECT col1 FROM table2 SET @num_rows=@@ROWCOUNT
SET @cnt=0 WHILE @cnt<@num_rows
[Code] .....
My doubt is how to make the LOOP for each REF until Year < 4 (like my example)
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows FOR XML RAW ('node'), root('nodes'), ELEMENTS
I have a flat file which have some record data ex.
id name team 1 "A"my" "Bl"ue"s" 2 "Bob" "Reds" 3 "Chuck" "Blues" 4 "Dick" "Blues"
in above example first record contain invalid data so complete flat file will not import due to one invalid row or record in flat file. so is there any way to check invalid row from flat file and ignore it(write log about invalid record) and process importing flat file.
I would like to standardize SSIS development so that developers all start with the same basic template. I have set it up so it is an available template ( http://support.microsoft.com/kb/908018 ) but I would like it to be the default when a new project or package is created. Is this an option?
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.
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?
Hi. I need to import excel file in database. i first need to do an unpivot task. the column names are dates and SSIS seems to be unable to pick up the column name as it is replaced by F2 F3 F4etc Can you advise of a solution. thanks ken
My client maintains its HR data in an application that uses Oracle as its backend. This highly-sensitive data is basically off limits to all but a select few. Presently, I use a program in Access 97 that allows one high level HR person to pass their login to linked Oracle tables and copy a large chunk of this data to Access tables. From there I can morph it as needed for the Personnel, Safety, EEOC and other areas. The client sees this PW-protected, encrypted Access DB as safe because, being "only an Access DB", it falls below the radar of IS. This basically means IS can't get to the data. However, accessibility and scalability are non-existent. I'd like to reduce the Access DB to a shell that simply links to Oracle and SQL Server 7 tables and performs a straight pipe of the raw data between DBs. However, now IS will be very interested (since it's SQL 7) and have Admininstrator rights, therefore causing the HR people to squash the deal. How can I lock SQL 7 up so tight that IS can't get to the data and yet be able to maintain the DB? If this is not feasible, are there any other options that might provide a solution?
Hi all, We have sql server 2000 on Windows server 2003.Is there anyway in sql server 2000 to protect some crucial data, even from the DBA. Thanks in advance...
Hello. I have a report with parameter called "parm1", that gets a value of "true" or "false" depanding on another parameter.
When the report is runnig the parm1 value is "false". How can I protect this parameter from a change by the user? I mean - the user can run the report and then add to the url "¶m1 = true".
Can I do anything against that? I tried marking it as "internal" and I thought that now it can get his value only from inside the report but it didn't worked.
Just read about SQL injection, and tested it out with sample database, and it does hack my database, the article show to prevent SQL injection by using application code to remove those keywords and change single quote to double quote, is there any method to prevent SQL injection directly using the database system itself, maybe stored procedure or anything?
Hello. I have a report with parameter called "parm1", that gets a value of "true" or "false" depanding on another parameter.
When the report is runnig the parm1 value is "false". How can I protect this parameter from a change by the user? I mean - the user can run the report and then add to the url "¶m1 = true".
Can I do anything against that? I tried marking it as "internal" and I thought that now it can get his value only from inside the report but it didn't worked.
