SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
11
votes
2answers
322 views
To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS
I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
6
votes
3answers
193 views
Find material for improvement in Microsoft BI
Which websites, books etc. can you recommend me to improve my basic knowledge in Microsoft Business Intelligence 2008, SSAS, SSIS and SSRS?
Please remember that I'm a newbie in technical BI tool.
If ...
6
votes
5answers
242 views
SSIS ETL Question/Option
In our current prod environment, when the ETL launches, it first checks an environmental variable at the OS level which tells it where the "config" database and table is for it to get the necessary ...
6
votes
1answer
468 views
Timeout expired error while using copy database wizard
Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get ...
6
votes
1answer
1k views
Duplicating some rows of a data flow in SSIS
I have a data flow to migrate data from an old database to a new one. The old design had all the data and historical information (changes) stored in a single table with a "version" (incrementing ...
6
votes
2answers
154 views
SSIS Validation slow. OLTP OLAP running on same server
I have four identical servers 2 are used in production and the other two are demo machines/emergency fail over boxes. The more powerful machine a 16 core 127 gigs of Ram hosts both our Datawarehouse ...
6
votes
1answer
1k views
Running an SSIS package owned by a domain user from SQL Server running on a local service account
I want to run an SSIS package containing Transfer SQL Server Objects tasks. The involved servers are on the same domain, but the SQL Server services are running on local service accounts. So the ...
5
votes
4answers
938 views
Duplicate database and keep it up to date - Replication or SSIS?
I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally.
We don't need ...
5
votes
2answers
1k views
SSIS flat file manager - skips the last record
I've a a flat file which is pipe delimited as shown below:
header|header2
abc|abcdef
abc|abcdef
When I create a package on a 64 bit machine it always drops the last record. But on preview it shows ...
5
votes
2answers
64 views
Turning on TF610 in SSIS
I have a simple SSIS package that loads a dummy file that contains 100000 rows. Each row is around 4k long, one int column and one long text column. I'm trying to test TF610 when loading this data ...
5
votes
1answer
306 views
SSIS File System Task Copy File Could not find part of the path error
I have created an SSIS package that uses a File Systen Task (copy) that copies a file from 1 folder to another. The package runs fine when I run it from my local computer but when I import the package ...
5
votes
2answers
750 views
Running SSIS from a different server than the primary database instance
We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2. On the current production ...
5
votes
1answer
663 views
Migrate DTS Jobs to 2008 Server
We have been running SQL Server 2000 for years.
Finally, at long last, we have an SQL 2008 Server being shipped in.
We have many DTS jobs on our SQL 2000 Server that need to be migrated to the SQL ...
5
votes
2answers
421 views
Passing values to variables defined in SSIS package at run time
I need to pass values to the variables in a SSIS package at run time. Obviously it is not practical to open BIDS and set a variable every time the package needs to be run. We run it on SQL Server 2008 ...
5
votes
2answers
207 views
SSIS keeps force changing excel source string to float
There is a column in Excel that is supposed to be text, but SSIS sees numeric text there and automatically makes it double-precision float [DT_R8].
I can change it manually in the Output branch's ...
5
votes
1answer
154 views
Non-English characters are not imported using SSIS or Visual Studio Deploy
I'm having problems with importing data into a SQL Server 2008 database.
If I execute the following statement in SSMS, it works fine.
INSERT INTO
[dbo].[ObjectiveRatingTranslation]
(
...
4
votes
3answers
663 views
How can I make my SSIS process consume more resources and run faster?
I have a daily ETL process in SSIS that builds my warehouse so we can provide day-over-day reports.
I have two servers - one for SSIS and the other for the SQL Server Database. The SSIS server ...
4
votes
1answer
1k views
ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?
Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
4
votes
1answer
152 views
Calling a .NET method in a SQL Server script
In the data migration process, I need to convert some columns values form the source system before writing to the target table.
I've been provided with a Csharp code which reads a string and do the ...
4
votes
1answer
290 views
Silent truncation of last character when using SSIS with Oracle
I am using SSIS to export data from an Oracle database. Sometimes when I export a VARCHAR2 column to NVARCHAR the last character gets truncated (silently). It's completely random as far as I can tell. ...
4
votes
2answers
175 views
SSRS: how to handle unavailability of service when datawarehouse is being rebuilt
We use SSIS to build datawarehouse (full Microsoft environment). This is legacy SSIS package that starts by truncating all tables in datawarehouse, run nightly.
During this time, we restrict access ...
4
votes
1answer
3k views
Migrating from one SQL Server 2008 R2 instance to another
I just started a new dba job and have been given a new server and told to move everything from the old server to the new one, brand new windows and SQL Server installations. I know I have to transfer ...
4
votes
1answer
75 views
In SSIS 2012, what is the purpose of flagging a package as an “Entry Point” package
In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package"
Doing a search I found this page on MSDN which states:
The value of 1 signifies ...
4
votes
2answers
793 views
SSIS Data Flow Error Output runs all the time
I've a SSIS Data Flow Task. Within which i've souce flat file and OLEDB destination. I've an error output sent to redirect row on OLEDB Destination.
The problem is that the redirect row always run ...
3
votes
1answer
100 views
Why SQL Server installs multiple services for AS and RS, but only one for IS?
When I install multiple SQL Server instances on a same computer, every instance gets its own SQL Server Service for Analysis Services and Reporting Services. But I get only one service for Integration ...
3
votes
1answer
380 views
Can I connect to SQL Server 2008 R2 database from SSIS 2012?
I need to redevelop an ETL process and I'm evaluating options.
I'm running SQL Server 2008 R2 database, and SSIS 2008 will take too long to develop and test the required packages. I understand that ...
3
votes
3answers
103 views
Fixed length text file insert into SQL table
I am not a DBA on this one and I will not have access to BCP or anything like that. So I'm wondering if there is still a way to do it in SQL keeping it very basic.
I have 100's of text files that ...
3
votes
1answer
49 views
What is the safest way to run SSIS on Multiple Named SQL Server Instances?
I have an Active/Active SQL Server 2008 R2 cluster. There are two named instances on each node and no default instance.
I need to deploy SSIS packages to the different instances. To do this, I ...
3
votes
2answers
151 views
Relating ExecutionInstanceGUID to the SSISDB
The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions ...
3
votes
1answer
311 views
What is a “Reporting Task for Subplan” task in SSIS, and how can I create one?
I'm trying to script a daily full backup to a job using an SSIS package. I've inherited several databases from a previous DBA, and I'm trying to emulate the ones he made in order to be consistent. ...
3
votes
2answers
1k views
Can I export a maintenance plan without using Integration Services?
I'm trying to export a simple maintenance plan from an SQL Server instance.
I want to check the export into source control, and apply the exported maintenance plan to functionally identical ...
3
votes
1answer
44 views
Convert SQL Server 2012 SSIS package to 2008
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.
Is there a way to convert the 2012 packages to ...
3
votes
2answers
58 views
Executing Child packages without using master package
I have many Fact and Dimension packages which are executed using vb script . Today one of my seniors proposed that these packages should be executed individually instead of creating a master package ...
3
votes
1answer
58 views
Integration Services Catalogs - Version Description
In Integration Services Catalogs I can see what versions of a package/project are available and if necessary revert to an older version. On this dialog I see a field "description" however I found no ...
3
votes
1answer
416 views
SSIS package with BULK data load
I analyzed a C# program over the weekend for a customer that basically does the following:
Truncate Table
Fetch Data (source on these is either remote database or local database where job runs)
...
3
votes
1answer
443 views
Export multiple tables among servers
We currently have an SSIS package that exports about 70 large tables nightly, with truncate and load at the destination. Our tables keep changing, we sometimes add some tables and remove some and ...
3
votes
1answer
120 views
SQL Calculation based on 'similar' rows within table
I'm using SQL Server 2008 R2, building a fact table for SSAS.
I can therefore accept solutions to this via either SQL query to SSIS task.
Let's say I have a fact table that looks a little like this, ...
3
votes
1answer
1k views
Having trouble with Merge Join with SSIS
I am currently using Merge Join to perform an inner join on two sorted sets of data in SSIS. The problem I am having is that no records are being returned from the inner join. To troubleshoot I have ...
3
votes
1answer
151 views
SSIS Design consideration with object variables
I have an SSIS package for SQL Server 2008 R2. In the package I am required to work with an Access database (one to many) and iterate through it running validation checks with the data. The validation ...
3
votes
1answer
880 views
On-Demand Fuzzy Lookup
We've got a customers table (Who doesn't?), containing many records that are, from a business perspective, duplicates. I've been able to create an SSIS package to perform fuzzy grouping, and report on ...
3
votes
1answer
113 views
Permissions Dropped after Package Failed
I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live ...
3
votes
0answers
153 views
SSIS Recordset Destination error
I have an SSIS package targeted at SQL Server 2012.
I have it deployed into the Stored Packages in Integration Services and then have a SQL Server Agent job which executes it.
The first part of the ...
3
votes
0answers
420 views
SSIS Script to split string into columns
I have a dataset (log file) with a number of columns; one of them is "Other-Data" below (unordered string) and need to parse the string to create the derived columns according the u value (U1, U2, U3, ...
3
votes
0answers
583 views
SSIS Row Count: Getting a null variable error where there is clearly a selected variable
Validation error. Build Files Count VIE [245]: The variable "(null)" specified by VariableName property is not a valid variable. Need a valid variable name to write to.
From what I can tell, this ...
2
votes
1answer
113 views
SQL Server 2008 SP1 to SQL Server 2008 R2 upgrade
One of our project is planning to upgrade from SQL Server 2008 SP1 to SQL Server 2008 R2. I would like to know from this community the suggestions on:
Is there any configuration or any change ...
2
votes
2answers
189 views
SQL Server imports data in wrong charset with SSIS
I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001.
However, when I run a
SELECT * FROM tableName
the data appears mangled. For ...
2
votes
3answers
604 views
SQL Analysis Services - SSAS
Do you know what the best practices would be regarding SSAS and having real-time reports.
I am not sure if we should be pulling data off our live database or using SSIS to move data periodically to a ...
2
votes
1answer
2k views
Running an SSIS package without the SSIS service installed
On a SQL Server 2005 instance in our DMZ, we have BIDS installed but not the SSIS service.
If I have an SSIS package on that server, can I still schedule to run it without SSIS service?
I tried ...
2
votes
1answer
118 views
How can I make sure the SQL Server query optimizer uses the exact tables in the query
I have a query running in SQL Server 2008:
select count(*)
from table1 join
table2 on table1.col1 = table2.col2
In the data warehouse where this script runs, a table called thin_table1 is ...
2
votes
2answers
377 views
Using SSIS to get record counts for tables that may not exist
I have an SSIS package that executes simple queries against a list of servers. I am having an issue with one particular simple query: If a table exists, get the rowcount. Otherwise report "not ...
