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
3answers
206 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
158 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
285 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
331 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
49 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
1k 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
4k views

moving data from one db to another using SSIS

I am very new to SSIS and need to work out how to setup a transfer from table A database A to table B database B. table A contains many more fields than I need in table B, so the process needs SQL ...
2
votes
1answer
2k views

Duplicate primary key in bulk insert after truncate in SQL Server 2008?

I'm using SSIS to dump data from my SQL Server 2008 prod database to flat files and then using bulk insert to load those flat files into the SQL Server 2008 dev database. The SSIS package is running a ...
2
votes
3answers
95 views

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

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
102 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
2k 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
402 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
188 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
505 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
1k 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
2answers
140 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
94 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
4k 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
230 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
334 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
134 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
882 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
792 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
324 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
1answer
624 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

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
1k 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
1answer
2k 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
4k 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
2k 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
10k views

Access denied when trying to run an agent job

Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server. 12/03/2012 ...
2
votes
1answer
826 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
856 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 ...
2
votes
2answers
281 views

SQL server job deployment

I have a SQL server job which has got different steps, including running a SSIS package. Currently it is on my pc (my local instance). I should deploy it to UAT and assume some steps need to be ...
2
votes
1answer
428 views

Is there a way to use a SQL Server 2000 database in SSIS 2012?

I just got my first chance to install SQL Server 2012 on a test machine. I also installed all of the management and Visual Studios components. I created a copy of one of my SSIS 2008 packages and ...
2
votes
1answer
1k views

SSIS PL/SQL task hangs with message “Multiple-step OLE DB operation generated errors.”

I've configured a Microsoft SQL Server 2008 R2 SSIS package with an Execute SQL Task to run a PL/SQL query on an Oracle 10.2 database. The Execute SQL Task (named "SQL Update MY_STATUS flag") is ...
2
votes
1answer
349 views

use variable table in OLE DB source

I try to export data to a flat file from OLE DB source. Here is what I do under OLE DB source editor. In SQL command Text: declare @t table (customerid varchar(100),[enrollment_Date] ...
2
votes
1answer
85 views

Data Extract and submit across the network

I've been given a somewhat vague requirement regarding a data extract process which I need some help with. I'm fairly new to database warehousing etc. but I will try to explain everything relevant. ...
2
votes
0answers
110 views

SSIS Bulk Insert Task Operating system error code 1311

I have been running SSIS 2008 R2 Packages for a long time and when ever my Packages trough SQL Server Job accesses FTP Server which is on the same domain it uses correct credentials ,e.g. ...
2
votes
0answers
136 views

Same data type but getting error “Cannot convert between unicode and non-unicode string data types” [closed]

We have two SQL servers (source and destination). From what little information I've got, it seems to literally copy content from one server/database to another using a SSIS package (which I ...
2
votes
1answer
179 views

Primary Key Constraint Violation in SSIS with Quotation Mark [closed]

Using SSIS Data Flow Task to transfer records from table in 1 SQL Server database to an IDENTICAL table in another SQL Server (2012) and getting "Violation of PRIMARY KEY constraint 'FLG_APVN_KEY_0'. ...
2
votes
0answers
163 views

Limit Output Flat File Size In SSIS

I am using Flat File Destination to export my data's to a Text file. I need to limit the output File size to 1 MB. Is it possible?
2
votes
2answers
604 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 ...
2
votes
0answers
427 views

Importing Excel files with SSIS

I'm trying to import an Excel spreadsheet into SQL Server 2012 using SSIS. There is only one sheet in the file. The file is a SalesForce export. I believe the Sheet name changes depending on when the ...
2
votes
0answers
108 views

How to use a foreach loop with project deployment model

I have a SSIS project that is configured to project deployment model, within this I have two connections one for a flat file and one for a sql db. Both connections are in project mode. Now I want to ...
2
votes
0answers
304 views

Microsoft Visual Studio is Busy

I am working in Visual Studio 2008 on a SSIS package. It is really frustrating that I get "Microsoft Visual Studio is Busy" message quite often. It almost happens every time I try to open a data ...
2
votes
0answers
976 views

Create SSIS package to copy data between SQL Server 2008 installs on different machines?

I'm new to the whole SSIS world. I'm trying to create a process to copy data between SQL Server 2008 environments. I want to be able to run a job to export data from my prod database and then use that ...
2
votes
0answers
375 views

SSIS - Derive column values based on join/subselect from a non-table source

I have a flat file source that contains a lot of duplicate records, which have the same key fields but differ by their "changed date". I need to split this up into two outputs, one for the "latest" ...