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

2
votes
2answers
4k views

Will SSIS 2012 work with SQL Server 2008R2 (not integrated)

Our company has SQL Server 2008R2 and plans to upgrade to 2012 in late 2013 or early 2014. I would like to use SSIS 2012 now as we do have the software. We have a company policy where we cannot run ...
2
votes
2answers
1k 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
1answer
595 views

SSDT on VS2013 for SQL Server 2012

I have Visual Studio 2013, and SQL Server 2012. From what I'm reading, it seems that the SSDT add-in for VS2013 only works for SQL Server 2014. Is that correct? I've installed it, and I built a SSIS ...
2
votes
1answer
2k views

ssis convert ddmonyyyy dd/mm/yyyy

Hi guys trying to convert 16JAN2014 --> 16/01/2014. Any ideas??? i'm trying to avoid the horrendous expression of ? JAN : 1 ? FEB : 2 etc. etc. SSMS will quite happily do SELECT MONTH('16JAN2014') ...
2
votes
2answers
1k views

SSIS Visual Studio 2008 reference variable scope in expression builder

When creating an SSIS package in Visual Studio 2008, in the expression builder, I know I can reference a variable by using @[User::FilePath] for example but this only works if you are evaluating the ...
2
votes
2answers
1k 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 ...
2
votes
2answers
4k views

Can I downgrade SSIS packages to run on older version of SQL

We have some new SQL 2008 SSIS jobs that were recently developed, mostly to do some simple flat-file loading and targeting a set of tables on a 2008 server. As it turns out, they may have to run on a ...
2
votes
2answers
80 views

SSIS compatibility

I'm trying to use SSIS for preparing data warehouse and for making business objects for report generator. The problem is that i have to support multiple sql server versions - sql server 2008r2 and ...
2
votes
1answer
58 views

Introduce or not introduce SSIS to an ETL project [closed]

Given is an ETL process that works fine using traditional SQL. The process is well optimized making use of ALTER TABLE permissions and performing partition switching, index creation, partition ...
2
votes
4answers
482 views

Getting new and updated data with an archive column

I am trying to optimize an SSIS package that retrieves data from a very large transaction table that gets millions of new records daily. The data is being collected from several hundred field servers ...
2
votes
1answer
5k views

Moving SSIS Packages to New Server

I have a number of SSIS packages in a number of folders on MSDB SSIS on SQL Server 2008R2. We are migrating to a new server host but remaining on SQL Server 2008R2. How can I copy/migrate these SSIS ...
2
votes
3answers
400 views

SSIS Rollback Package

I have several data flow tasks in my package that depend on other data flow tasks. For testing reasons I want to run the entire package and then roll it back after it completes. How do I do it?
2
votes
2answers
264 views

Pull Access schema into a SQL Server table

We had an assembly that used to pull a list of tables and their columns from an Access database and place the listing into a SQL Server table for later use. For various reasons the assembly is no ...
2
votes
1answer
436 views

Data migration between databases in the same instance

I have two almost identical (structurally) databases: Documents DocumentsOld The original database existed before table partitioning was available, so the solution they found was to create the ...
2
votes
4answers
445 views

What “tool” to use for monitoring instances and where to store this data?

I have three SQL Server instances in my DW project, development, test and production. I also have two SSIS servers (dev/test, production). My intention is to query data from these three SQL Server ...
2
votes
1answer
42 views

Database in SIMPLE recovery model full using SSIS bulk insert task

I have a database in SIMPLE recovery model with 10GB free. The file I am trying to load through SSISs bulk load is 1GB. The table is empty (truncated prior to each loading), is a heap and has no ...
2
votes
2answers
340 views

Why would a job call a batch file with dtexec.exe instead of just calling the SSIS package directly?

I've inherited a SQL 2012 server with a number of jobs that call SSIS packages. Instead of the more obvious solution (calling them directly from a "SSIS Package" job step), these jobs use a CmdExec ...
2
votes
1answer
370 views

Searching SSIS packages for the use of specific tables

I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.
2
votes
2answers
587 views

How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package?

I want to pass a parameter (@date1) in my SSIS OLE DB Source so I created a variable and I tried to pass the parameter using '?' but it's showing 'Syntax error, permission violation or other ...
2
votes
1answer
200 views

Prevent Subscription to report from running on a holiday

I have a SQL Server that I am using for building and distributing reports. I have built all the packages for importing data as needed as SSIS packages. These reports only need to be run after the ...
2
votes
1answer
435 views

convert string allow null col to int?

I want to convert string to int my string type [DT_WSTR] and my expression ISNULL(col) || TRIM(col) == "" ? (DT_I4)0 : (DT_I4)col I get this error [Derived Column [518]] Error: The conditional ...
2
votes
1answer
153 views

SSIS Transaction Level for Single Task

I have 3 tasks in a SSIS package. If the final task fails I only want that task to rollback. Do I just set that task to REQUIRED? Or leave it as SUPPORTED? Thanks!
2
votes
1answer
4k views

Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)? [duplicate]

I am having dozens of MSSQL Servers 2012 R2 on Windows Server 2008 R2 with similar setup, configuration (in virtual machines) in production. Neither has SSIS installed. I have to similarly maintain ...
2
votes
2answers
2k views

Average Time to Load 1.2 million records from Oracle INTO SQL SERVER 2008R2

I have an ssis package which loads 1.2 million records of data from oracle into SQL SERVER 2008R2(with 32 GB RAM) dataware house table once every week. (truncating and loading) This process is taking ...
2
votes
1answer
29 views

Are there security vunerabilites/implications with SQL Mail, currently using SQL Server 2008 R2

I am an accidental DBA and responsible for series of data load jobs. I would like to use SQL Mail for altering me to job failures. SQL Mail is not currently turned on are there security concerns in ...
2
votes
3answers
781 views

Looking for a utility to move data between databases (prod to dev) SQL Server 2012 [closed]

I am looking for an easy way to copy table data from a production SQL Server 2012 machine to a development machine (same version, level). We currently just do an export from within SSMS, but we have a ...
2
votes
1answer
8k views

Creating Excel files in SSIS based with a predefined format

I need to create a statistical report in Excel and wondering if I can use SSIS to read data from the database (calculate the aggregated numbers) and load them into an Excel file with a specific ...
2
votes
1answer
413 views

How do I access the full text of an error in SSIS GUI?

I have an Excel task which is providing error text through the GUI, but I don't know how to access the error task without the GUI and the error disappears if I move the cursor from the X I have the ...
2
votes
1answer
212 views

Backup on an empty SSAS database hangs

I created an SSIS package to iterate through all databases on an SSAS instance. It grabs the DatabaseId using AMO in a vbscript, then generates the XMLA to backup each database. Most databases ...
2
votes
1answer
421 views

SQL Data Collector - SSIS required on source server?

For SQL Data Collector: Do you need SSIS (SQL Server Integration Services) installed on the server that you are collecting data from. Or can the packages be run from another server -- like the one ...
2
votes
1answer
637 views

Debugging a SSIS OleDbCommand

I have an OleDb Command component in a dataflow that does an update(yes, I'm aware of the performance implications). Running the pack in BIDS shows me that the component is being reached and runs ...
2
votes
2answers
210 views

Advice on how to approach data cleaning & importing

I am importing and cleaning data from a datasource that have records like this: The data needs sorting and rearranging based on the Event Type. The destination schema would be something like ...
2
votes
1answer
780 views

Export SQL Server varbinary field to string using Visual Basic script in SSIS?

I'm using SSIS to export data from a set of tables to flat files. The export is running great except for one table with a varbinary field. The export is running a Visual Basic script task to load the ...
2
votes
3answers
3k views

Is there a way to do data export from Amazon Web Services RDS for SQL Server to On-Premises SQL Server?

We have a large Amazon Web Services RDS for SQL Server instance and we would like to do incremental data transfers from RDS to On-Premesis SQL Server on a regular basis. The On-prem server will be ...
2
votes
2answers
3k views

how to use database mail from SSIS

I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks
2
votes
1answer
44 views

SSIS export to flat file - how to configure all DataTypes for columns w/o manual entry?

In my OLE DB Source to Flat File Destination Data Flow, I have quite a few columns and it's excruciating to manually set each one to decimal with a scale of 4. I tried adjusting my SQL code in the ...
2
votes
1answer
79 views

Running scheduled SSIS pkg with SQL Server 2012 Web Edition

I have a client with SQL Server 2012 Web Edition. I believe it has SQL Server Agent, but I see on the following Microsoft page that many integration services are not supported: ...
2
votes
2answers
60 views

Get rows inserted and updated dynamically in SSIS

I need to get the rows inserted and updated in each table through my SSIS package. Is there any way to do it except using the row count transformation component before each insert or update ...
2
votes
1answer
56 views

Proper permissions for ETL user

I'm working on changing the permissions on our ETL to incorporate best practices. This is our ETL in a nutshell: Load 100+ tables from 5 different SQL Servers into stage in DW Call procedures on ...
2
votes
1answer
854 views

How can I resolve SSIS Excel Connection Manager Error 0xC0209303?

I have created an SSIS package that imports an excel file into a SQL Server table. The SSIS package runs without any problem when I run it locally on my machine but when I run it on the server where ...
2
votes
1answer
374 views

Script component source won't load when click “Edit Script…”

I'm working on an SSIS package in Visual Studio 2013. For any script component throughout the package, if I double-click on it and then click the "Edit Script..." button, Visual Studio 2012 opens but ...
2
votes
1answer
28 views

Demographics Dimension Table - Finding the Key!

I have a dimension table which captures demographics on Employees. The schema is similar to below: CREATE TABLE [dbo].[DimDemographics] ( [DemographicsKey] [int] NOT NULL, [AgeBand] ...
2
votes
1answer
38 views

Non clustered index for non-evenly distributed data in large SQL table

I have the following table containing 2.000.000.000 rows: CREATE TABLE [VeryLargeTable] ( [dataCol1] [NVARCHAR(MAX)] , [dataCol2] [NVARCHAR(MAX)] , [Manufacturer] [NVARCHAR(10)] , ...
2
votes
1answer
518 views

Most efficient way to insert rows into a temp table in a stored procedure

I am working in SQL Server 2008. I have a stored procedure with 5 queries inside of it that select rows from very large fact tables (on the order of millions of rows) into another table (call it ...
2
votes
1answer
312 views

Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)

I try to deploy new version of existing SSIS project into Intergration Service Catalog and getting error: "Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)" ...
2
votes
2answers
543 views

SSIS Package - Error Exporting Data to Flat File - Invalid Character

I have an SSIS package where I pull data from a 3rd party source into a CSV file. I get this error: "Data conversion failed. The data conversion for column "Description" returned status value 4 and ...
2
votes
1answer
1k views

Error message when importing data into SQL (Import & Export Data Wizard)

I am trying to use the Import & Export Data Wizard to import the contents of a CSV file into a table in MSSQL. I have no issues if I import all columns as strings however, I need one column to be ...
2
votes
2answers
590 views

TSQL vs SSIS Lookup

I was trying to find some well performing Tsql equivalents for the SSIS Lookup component. Especially for Fact table loading. After few different query patterns the most obv performed the best: ...
2
votes
2answers
652 views

Importing different file types to SQL Server

I'm looking for direction here. I need to automate a process that will import several different file types into SQL Server. They are all text based files (no excel) but the internal format of the ...
2
votes
1answer
615 views

Why Does SSIS Oracle Source by Attunity return DT_WSTR for Oracle CHAR(1) Column?

Using SSIS 2012 and Microsoft SSIS Connector for Oracle by Attunity v. 2.0. When I add an Oracle Source for a particular table, it is setting the data type for an Oracle CHAR(1) field to DT_WSTR. I ...