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
353 views

How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS

I was just wondering, in MERGE in T-SQL, you can output the OUTPUT $action, Inserted.* and Deleted.* records. I want to ask how can I get OUTPUT $action, Inserted.*, Deleted.* records in SSIS?
2
votes
2answers
2k 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
857 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
5k 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
1k 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
2k views

Table data transfer using SSIS and triggers on destination table

I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using. I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do ...
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
2k 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
1answer
109 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
4answers
191 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
2k 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
296 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
214 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
324 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
371 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
807 views

Not able to create SSISDB catalog

getting below error while trying to create catlog in slq server 2014 integration services. any idea what i missed in installation or anywhere else? The catalog backup file 'C:\Program ...
2
votes
1answer
104 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
92 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
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
3answers
312 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
168 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
282 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
5k 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 ...
2
votes
1answer
1k views

error in Execute SQL Task with parameter

I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that ...
2
votes
1answer
489 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
192 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
594 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
2answers
2k 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
400 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 ...
2
votes
2answers
284 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
4k 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
4k views

How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure

i am executing query like this on sql server 2008 R2 DECLARE @returncode int EXEC @returncode = xp_cmdshell 'dtexec /f "C:\certs\SSIS Disposition\SSIS Disposition\SSIS Disposition\Package1.dtsx"' ...
2
votes
1answer
3k views

SQL server agent job built on SSIS package not working

I transferred a job from the old SQL Server 2005 to an upgraded SQL Server 2008 R2 instance, and I scripted out everything, changed the data source and other details... "This job should copy some ...
2
votes
1answer
109 views

Time Boxing SSIS Task

I've an SSIS task which download a couple of files,update various Database tables and archive the files(i have it split in various packages). The average time to completion is about 2.30 min. ...
2
votes
3answers
7k views

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

Version SSIS 2012. Package was originally 2005 ver. Package on file system. DB Engine / Agent on Host 1, SSIS on Host 2. Execution from Host 2 runs successfully. Execution from Host 1 gives the ...
2
votes
1answer
246 views

Deploying to SSIS catalog and get permissions error

When I attempt to deploy to SSIS 2012 Catalog on new server I get an error. I have researched it on the web for several hours and all of the information available online does not fix my issue.
2
votes
1answer
431 views

SSIS - How do I execute a single OLE DB Command for mutiple paths?

I have an SSIS package (2008 R2) that I'm working on and I find myself duplicating the same OLE DB commands repeatedly and whenever there is a change its a nightmare to go back through and update all ...
2
votes
1answer
161 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
1k views

Does surrogate key assignment for a fact table require that the source data has natural keys?

Let's say I have a simple OLTP database with orders, products, and customers: And from it, I am building a data mart with an orders fact table, product dimension, customer dimension, and date ...
2
votes
3answers
2k 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
1answer
365 views

Jet OLEDB driver refuses to open a linked table where the original has a lock - any way to force this?

I have a requirement to read reference data from several access databases for an ETL job. The data owners have produced a file with a series of linked tables that consolidates them all into one ...
2
votes
2answers
9k views

SSIS Script Component - How to modify the Output0Buffer

I've a script component which accepts records from SQL Azure database table. The script then invokes a web service, which returns the number of failed and success records. For all the records, I ...
2
votes
1answer
719 views

Update Schema in SSIS Export Package When Source Schema Changes

I have servers with lots of client databases, all with very similar, but not necessarily identical schemae. Some customers want their data in MSAccess on a monthly basis. This was getting tedious to ...
2
votes
1answer
2k views

SSIS Event Handler at Package Level

I have a fairly simple SSIS package (handful of Execute Task and a few Data Flow task). The end result I am looking to have: I would like to get notified by email when an error occurs anywhere in ...
2
votes
2answers
4k views

How to avoid empty rows in SSIS Excel Destination?

Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task: OLE DB Source: Data Conversion (to handle the annoying UNICODE / ...
2
votes
1answer
5k views

SSIS How can I write to a Specific Cell in an Excel Sheet

I am trying to complete what I thought would be a very simple task but after hours of looking thru various articles and attempting different methods, I still have not been able to Write to a specific ...
2
votes
1answer
2k views

SSIS Package Data Flow Task RollBack on Error

I tried running a package and it failed but I noticed in the output box (in visual studio) that it managed to write 2000+ rows to a table. I was wondering if an SSIS package would roll back the ...
2
votes
3answers
4k views

How to import table's data in MySql from Sql Server?

I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error. Here this error may be ...
2
votes
1answer
1k 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 ...
2
votes
1answer
962 views

SSMS 32bit can't connect SSIS 64bit

I can't find the solution and searching for info doesn't give desired results Environment: Server: W2K8 EE 64 bit + MSSQL SE 2K8 R2 64 bit Client: Windows XP Prof 32 bit Using SSMS on client ...