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
1answer
758 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
292 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
173 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
355 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
1answer
79 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
1k 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
185 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
268 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
107 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
522 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
641 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
978 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
289 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
552 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
432 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
495 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
578 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 ...
2
votes
1answer
1k 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
922 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
6k 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
524 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
733 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
214 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
264 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
249 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
111 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
148 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
108 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
413 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
385 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
100 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
260 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
663 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
294 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" ...
2
votes
1answer
747 views

Export data into word documents

My boss has a kindle and likes to save a lot of stuff to his clippings. Currently I'm going through and cutting out each clipping and pasting it into a seperate word document with the title of the ...
1
vote
2answers
171 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 ...
1
vote
2answers
6k views

SSIS SQL Server 2008 errors

I just want to know how to import data from source to SQL Server 2008 database using SSIS. I have a simple txt file (actually it's from this blog : ...
1
vote
1answer
62 views

Return value formatted similar to if casted as money

My client's system is very outdated, and there is nothing I can do about it. What I'm trying to achieve is to extract values always with at least 2 decimal places. E.g. From 1826.5695600000 ...
1
vote
1answer
961 views

SQL Server 2005: Integration Services Stored Packages is empty. Why?

I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored ...
1
vote
1answer
167 views

How to pass a table of values to a stored procedure from SSIS?

I am really new to SSIS, so I apologize for the "dumb" question. I know open ended "Best Practices" questions are seriously frowned upon, so I will try to be as specific as I can. I have a basic SSIS ...
1
vote
1answer
109 views

In Job Step > Properties, only see two tabs: Package and Configuration

I am trying to set a job step that executes a package to use the 32-bit runtime, as it uses the SQL 10 driver against a SQL 2000 database. The job is running on SQL Server 2012. I see all kinds of ...
1
vote
2answers
1k views

SSIS Expressions Date Format get just YY for year

I found many articles on date formatting expressions in SSIS but I couldn't find one that specified how to get just the Year such as 13 for 2013 or 12 for 2012... I tried DATAPART("yy",GETDATE()) ...
1
vote
2answers
341 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 ...
1
vote
2answers
224 views

Optimizing SSIS Packages

I have a SSIS package that loads data in an Access DB to tables on a SQL Server DB. Currently I double click the package and excute it. There are times when there is a lot of data to load. My question ...
1
vote
2answers
956 views

Moving data from Oracle to SQL Server : Table size optimization

I am loading data using SSIS from Oracle to an SQL Server database. One table I moved had a size of 4 MB under Oracle but when loaded to SQL Server, the size grows to 7.96 MB! I analyzed the size ...
1
vote
1answer
1k views

SSIS: “The buffer manager cannot write 8 bytes to file”

We have about 60 or more SSAS cubes on one server. All the cubes have the same structure. Each cube has a different size because each cube has a different underlying data warehouse. However the schema ...