Hot answers tagged ssis
9
Integration Services is not "Instance aware". The rest (Analysis, Reporting, DB) are.
Note that SSIS doesn't need to be instance aware either. The service itself is pretty pointless and doesn't need to running. You simply need the SQL Server common binaries installed to run packages. The service is for SQL Server Agent and SSMS to browse packages etc
...
9
You aren't bottlenecked by CPU usage.
The majority of the time, SQL Server will be IO bound since physical disk access is many magnitudes slower than CPU or memory.
An analogy to what you are asking is:
I have this huge fire hose connected to my kitchen faucet. How come I can't get full pressure from the fire hose like they get from a hydrant?
The ...
9
I would slipstream Service Pack 2 (and perhaps Cumulative Update 2 as well) into the R2 installer prior to running the upgrade, especially if you have enabled the extended partition support. This guide walks you through slipstreaming (it talks about a different service pack and CU, but the process is the same). Slipstreaming will save you a bunch of time and ...
8
+1 for @JNKs explanation.
Simplest way to verify your bottlenecks would be to reset wait stats before the process starts:
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
Immediately when the process is completed, capture wait stats. If you want to get a more fine grained picture of where the bottlenecks are occurring, capture the wait stats to a table on a ...
7
If you're loading to an empty database, you could/should take steps to avoid requiring any additional maintenance steps post load. Fragmentation is the enemy, that's what you're trying to avoid.
Drop all NC indexes before loading.
Post load, add the NC indexes for each table in sequence i.e. don't add an index to TableA, then TableB, then back to TableA. ...
7
The SSIS service doesn't actually do much and you can run packages without it or with it disabled. It's used by SSMS amd SQL Agent for example.
See the KB article "Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service"
What you need are the base SQL Server binaries which are installed with the ...
7
If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one). Then you can get the new rows by
SELECT * FROM staging_table
EXCEPT
SELECT * FROM real_table
;
Rows missing from the new CSV can be get reversing the two sides around EXCEPT. However, ...
6
I would just backup/restore. This accomplishes the same thing, validates your recovery story, and will easily handle schema/object changes as well - without really putting any undue strain on the existing database (you're already backing it up, right?). If it were on a different server (or if you ever split them up), I might suggest log shipping as opposed ...
6
You would need to save the package to a SQL 2012 instance and run it from there. It will connect to R2 just fine.
This is because a SQL Server 2012 SSIS package cannot be saved to a lower version of SQL Server or run from a lower version of SQL Server. That said, an SSIS package (in any version of SQL Server) can connect to any data source that is ODBC or ...
6
I wouldn't want to have 200 data flows in a single package. The time it'd take just to open up and validate would make you old before your time.
EzAPI is fun but if you're new to .NET and SSIS, oh hell no, you don't want that. I think you'll spend far more time learning about the SSIS object model and possibly dealing with COM than actually getting work ...
6
A much more efficient path than messing with that wizard would be to:
Take a full backup of your 2000 database.
Restore it to a SQL 2005, 2008 or 2008 R2 instance, all of which support 2000 databases. You can still get the evaluation edition of 2008 R2 here, if you don't have any applicable instances in place. Note that Express won't work as it has a 4GB / ...
6
There are only two possible ways that the execution plan for your query might not read from table1 directly. I will use the following indexed view created in the AdventureWorks sample database to illustrate:
CREATE VIEW dbo.IV
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ...
6
As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query.
Usage
In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length.
Instead of
SELECT
E.BusinessEntityID
, ...
5
It depends on how much of a window you have but generally updating statistics and rebuilding/reorganizing indexes is a good step to take. Other than that there shouldn't be anything else you need to do really.
The statistics tell the query optimizer how many rows are likely affected by an operation and that in turn tells SQL which approach to take to run ...
5
You can import the old DTS packages into SQL Server 2008 using the DTS Migration Wizard. You can find it on the SQL 2008 Management Studio at the following folder: Server -> Management -> Legacy -> Data Transformation Services - Migration Wizard. Point to the current source of the packages and this should help you do your job.
You should be able to find ...
5
The packages themselves are stored in the msdb sysdtspackaes90 table.
It’s important to note that the SSIS
server isn’t aware of packages stored
in the File System until those
packages have been imported to the
File System folder in the SSIS
service.
...
While the SQL Server Management Studio
is shipped using the default folder
...
5
I'd go straight SSIS myself. Besides having configuration, logging and error handling out of the box, you can get some really nice performance out of it.
You could probably save quite a bit of time by using something like EzAPI to script out the majority of the packages and then tweak the remaining 10%.
I recently used that approach to script out ...
5
We do this, the environment varaiable is at the user level, so the user for each environment that runs the SQL agent jobs is different. So on the the dev environment our agent user is something like SQLDev and on the QA environment it is something like SQLQA.
If you are not running from jobs (which I highly suggest doing except on dev while doing actual ...
5
I would suggest reading up on SSIS at MSDN:
Initial Installation (Integration Services) -this references having a standalone installat of just SSIS.
Quote from above link:
SQL Server Integration Services is installed through the SQL Server
2008 Setup program. You can install Integration Services alongside
other SQL Server components or you can ...
5
It would be a tough call between a CLR assembly and using SSIS given that your current migration script is just a script.
CLR Assembly:
Must be installed into a database (both the assembly binary and the exposed wrapper method(s)).
Requires setting up an external project in Visual Studio, something you may not be very familiar with.
Very easy to integrate ...
5
Mapped drives, like P:\ in your case are dependent on the user logged in. Just because you can see the drive letter when logged into the server machine does not mean SQL Server can "see" the drive letter. If you specify the UNC (Universal Naming Convention) name in the path instead of using the drive letter, and the account SQL Server is using has access ...
4
BI Development is a big subject to learn and will take a lot of time understand all the concepts, I would suggest this book as place to start your journey of discovery: The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset by Joy Mundy
Permalink: http://www.amazon.co.uk/dp/0470640383
4
I'd have a restore script
And I'd have it restore the latest "real" backup too: this way you test the backup file integrity too (of course it could be corrupt later but it's a nice to have). This new script shouldn't take it's own backup.
We have this in our current shop but restore into non-production environment
I wouldn't use SSIS or replication: you ...
4
I'd say less as an initial starting point but more as an indispensable resource, there are some great blogs out there:
SSIS Junkie/Jamie Thomson
SSIS Team Blog
4
The most significant balancing act to consider is whether your need for real-time reporting outweighs the performance hit that both your OLAP and OLTP systems will take from using your OLTP data source directly as fact and dimension tables, and then bouncing ROLAP/HOLAP queries off of them. If the tables are all quite small, and the server isn't under heavy ...
4
You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of ...
4
Maintenance plans are stored in msdb.dbo.sysssispackages like any other SSIS packages that are stored to SQL Server. I have a handy post on SSIS Package Extract from MSDB that should cure what ails you.
4
If you've truncated the table, then a primary key violation must be coming from duplicate data in the file. Try bulk inserting into a new table, without the PK constraint, and then check the table for duplicates (probably easier than writing some tool or script to parse the file directly). You can create a mimic table that won't have constraints this way:
...
4
You don't have sufficient disk space to process everything you're trying to do concurrently. Data flow operations that spill to disk and temp files that get created are just some of the many reasons you might run out of space.
You should be able to run Perfmon during one of these runs to see how disk space and activity play out.
It's next to impossible ...
4
Time taken would depend on
Row length
Network Bandwidth
Available resources on the source DB server
And finally on data volume, as in number of rows
To put things in perspective - I recently did a similar migration. A table with 40 columns ( a mix on nvarchar and numeric) and 3 million rows of data took 35 minutes, where as another table with 7.5 ...
Only top voted, non community-wiki answers of a minimum length are eligible
