SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.

learn more… | top users | synonyms

5
votes
1answer
1k 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 ...
5
votes
1answer
1k 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
1answer
37 views

SQL Query not throwing error even if the entered scale value is higher than specified

I need to capture numeric values without truncation. But the output value is getting truncated even though I specified the Scale as '2'. Column 'A' datatype - numeric(5,2) Case 1: Entered value: ...
5
votes
1answer
647 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. ...
5
votes
2answers
178 views

Copy data from multiple databases to a central database for reporting and analysis

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting ...
5
votes
3answers
15k views

Error: “To run a ssis package outside of sql server data tools you must install [send successful email] of Integration Services or higher.”

NOTE that the title of this qusetion is a verbatim error message specific to this user. The error message illustrates a bug in the dtexec application, which substitutes a user variable into the error ...
5
votes
1answer
7k views

Importing a large column (8000 chars) from Excel using SSIS

I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor. I'm using SQL 2008 R2 SSIS to import it into a table. The problem connection manager is an OLE DB connection ...
5
votes
1answer
11k 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 ...
5
votes
1answer
143 views

Best Practice for SQL Server data validation

I have CSV files that I would like to import into SQL Server 2014. Each record in the files has around 20 columns, each of them should be validated for data type compliance as well as some other ...
5
votes
1answer
57 views

Designing a dimensional DB off a normalized source that already implements SCD's

I have built an SSIS ETL to integrate various data sources (one from MySQL, two from SQL Server) into a single SQL Server relational and normalized database, which I've called [NDS]. The SSIS ETL ...
5
votes
2answers
494 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 ...
5
votes
1answer
290 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] ( ...
5
votes
1answer
4k 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 ...
5
votes
2answers
2k 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 ...
5
votes
1answer
309 views

SQL Server: Unable to save import export package, but am on Standard Edition

I am confused. Issue: SSIS ("SQL Server Import Export Wizard") should let me build a data import package and save it to a file, for easy re-running (converting to a task, etc.). I want to save a ...
4
votes
3answers
1k 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
2answers
3k views

How can I change the number of @schedule_uid=N'????' to re-create a SQL Server Agent Job?

I have a SQL Server 2005 job in the SQL-Server Agent Jobs that I need to move/transfer to another updated SQL Server 2008 R2 instance. I was able to script it out and then use it as a new query window ...
4
votes
2answers
33k views

Converting Non Unicode string to Unicode string SSIS

I am creating a package where I will be exporting data from a database into an empty excel file. When I added only the source and destination components and I ran the package I got a conversion error ...
4
votes
3answers
340 views

How to correctly discard old backups?

I have an Execute SQL Task in my SSIS control flow that's backing up a number of databases before it proceeds to run the overnight ETL: EXECUTE master.dbo.xp_create_subdir N'G:\Backups\DQ' GO EXECUTE ...
4
votes
1answer
677 views

Deleting Rows is Increasing Index Size

We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table ...
4
votes
1answer
2k views

How can I get more information about which lookup failed in an SSIS lookup?

I'm frequently getting errors along the lines of Error: 0xC020901E at Build Test Data, Lookup ID String[120]: Row yielded no match during lookup. To resolve these, I'm going back to the source and ...
4
votes
3answers
4k views

How can I dynamically back up all SSAS databases on a given instance?

I want to dynamically back up all the databases on a given SSAS instance using a SQL Agent job (which would most likely involve executing an SSIS package). It is imperative that this is a dynamic ...
4
votes
1answer
1k 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 ...
4
votes
1answer
3k views

SSIS how to parse column with erratic data

I have a question on how to parse out a column and hoping a SSIS/Database guru can help out :) Here is the problem. I have a column saved as a string/varchar format in SQL Server, and need to parse ...
4
votes
1answer
5k views

How do I grant the privilege of connecting to SSIS?

Attempting to run SSIS packages using dtexec.exe, I got this error message: Could not load package "D:\Filename.dtsx" because of error 0xC00160AE. Description: Connecting to the Integration Services ...
4
votes
3answers
740 views

Why does my procedure run slowly until I update statistics?

I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the ...
4
votes
1answer
3k views

Licensing standalone instance of SSIS

Basic background: I have an application on an app server (no MSSQL installed) that I want to allow to call an SSIS package. One Solution: One option seems to be that we can install just SSIS on the ...
4
votes
1answer
3k views

Error using an Execute SQL script to load a date variable in SSIS

I'm trying to populate a variable using an Execute SQL task in SSIS. The table structure is as follows: CREATE TABLE BuildControl (Id INT NOT NULL Identity, BuildDate Date, Country char(2), ...
4
votes
1answer
7k 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 ...
4
votes
2answers
35 views

SSIS OLEDB Command not firing triggers

Got a bit of a strange one that I can't seem to work out. We have a vendor database ( so we have no control over the table structure, foreign keys, etc...) that's on SQL Server 2012 SP2. We have a ...
4
votes
2answers
243 views

Loop through tables or copy paste

I have to do a migration where I should retrieve data from 40 Access tables, which should contain several of the same columns (but not all, the schemas are different). To make it simpler, I used SSMA ...
4
votes
2answers
299 views

SSIS Package Error when called from SQL Agent Job

We have SSIS running on its own server under account 'ourserviceaccount'. We then have SQL Agent running on a separate server under the same account. I have set up a job that calls an SSIS package, ...
4
votes
1answer
408 views

SSIS Multicast performance question

I'm wondering how multicast actually works. So here is my setup. I have one source flat file(csv) of about 100k rows, maybe about 250-300 columns, and I'm multicasting that file into 7 one-to-one ...
4
votes
4answers
3k views

best ETL design to transfer transaction tables records into the data-warehouse

I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table. but for ...
4
votes
1answer
11k views

Login failure when running a SSIS package from a SQL Server job

I have a SSIS package that migrates data from a Sybase database to SQL Server. The connection to the source database has been made through an ODBC connection. If I run the package directly on my ...
4
votes
1answer
1k 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. ...
4
votes
1answer
2k views

SSIS Lookup Component Irregularities

I'm seeing odd behaviour in a dataflow with a lookup operation. If I select 'full cache' mode, some portion of the lookups are failing and I have no explanation why. 'No Cache' works correctly, as ...
4
votes
2answers
4k views

Why is this stored procedure for linked server job failing in SQL Server Agent?

I have this stored procedure that runs this code: This piece of code uses the MERGE Statement to update Local Tables form remote tables in an Access Database that is linked by LINKED Server in SQL ...
4
votes
1answer
192 views

Star join query optimization - Change partitioning, use columnstore?

I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which ...
4
votes
2answers
1k views

Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

I am trying to run a number of SQL Server 2008 stored procedures that are wrapped in SSIS packages consisting mainly of Execute SQL Tasks. They run fine in Visual Studio 2008 after deploying to a ...
4
votes
1answer
2k views

SQL column goes from first to last position in output

I have an SQL query inside a SQL Server Integration Services (SSIS) package where the preview shows the column order correctly, however the actual output uses a different column order. This is ...
4
votes
2answers
280 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
4k views

SSIS package blocks itself if uses TRUNCATE

There is an SSIS package with Required transaction on the package level and Supported on the tasks level. The main part of the package is deleting the data and inserting new data: Here the Delete ...
4
votes
2answers
1k views

SQL server agent SSIS error

I get the following error when I execute my package as a SQL server agent job. It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive. I don't ...
4
votes
2answers
623 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 ...
4
votes
2answers
4k views

SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

I have a package that imports excel data into a SQL table. One of the columns in the table is of "smallint" data type and it allows for nulls. The excel file column that has the data that is suppose ...
4
votes
2answers
3k views

Splitting a large SQL Server MDF file

I have a large (1.2 terabyte) SQL Server database that I need to migrate to a new server. Most of the database lives on a single, 1.25 TB data file, and a little bit sits on a much-more-manageable 550 ...
4
votes
1answer
2k 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 ...
4
votes
1answer
78 views

SQL Server 2014 SSIS Package Ignores Script Task when running as AGENT

Alright, so here's what I have going on. SSIS Package that queries data from our production system and drops them into CSVs. Have a VB script task that uploads them to a third party. Needed a ...
4
votes
3answers
1k views

Twice job results in sql server, last one is left in progress

I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1. Fist result ...