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
806 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
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
7k 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
3k 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
5k 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

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
471 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
3answers
9k views

Error Authenticating Proxy Account when Executing SSIS job

I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages. These packages have been running for years and the associated steps run via a Proxy Account ...
2
votes
1answer
987 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
2k 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
569 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
89 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
24 views

SSIS ASCII requirement: “Code page is 1252 and is required to be 20127”

I have a requirement to export a database to a tab-delimited file in the ASCII format. I am using derived columns to convert any Unicode strings to non-Unicode strings. For example, a former Unicode ...
2
votes
0answers
38 views

SQL Agent AD account gets permission denied for ODBC connections in SSIS packages

We have an AD account specifically created for the agent service and when running SSIS packages that connect to databases we get permission denied even though we have granted that account access to ...
2
votes
0answers
51 views

SSIS: FTP task - concurrent connections

I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job ...
2
votes
0answers
103 views

SQLNCLI10.x vs 11.x when connecting SSIS 2008R2 to a SQL Server 2012 database

Starting from the question and working backwards. My question: is it OK to use SQLNCLI10.1 as a provider for SSIS 2008 R2 to connect to SQL Server 2012 database(s)? I have a SSIS box running SQL ...
2
votes
1answer
218 views

How can I invoke an SSIS package from SQL Agent, via T-SQL, using a proxy

Building an ETL framework with many packages and a "controlfile" table that orchestrates running them in the right order and managing errors. In order to control the overall operation of the data load ...
2
votes
1answer
4k views

SSIS OLE DB Error on package execution

Good day, I have an SSIS package that runs on scheduled job for a while now. all was okay, until very recently, i started receiving some failed job executions. When I investigated it, I found the ...
2
votes
0answers
204 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
272 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
342 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
253 views

Limit Output Flat File Size In SSIS [closed]

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
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 ...
2
votes
0answers
596 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
124 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
436 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
1k 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
536 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
1k 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 ...
2
votes
1answer
104 views

SSIS Packages get stuck in SQL Agent jobs [closed]

We run about a dozen scheduled SQL Agent jobs which run SSIS packages. Intermittently, every scheduled job will get stuck in a running state. i.e. we'll arrive in the morning and notice that there's ...
1
vote
1answer
83 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
1k 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
103 views

Upgrade to SQL Server from 2008 to 2012 - Developer perspective [closed]

We are in the process of migration from SQL Server 2008 to SQL Server 2012. The entire process of migration will be taken care by dedicated DBA team. From a developer prespective, I would like to ...
1
vote
1answer
2k 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
3k 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 ...
1
vote
1answer
391 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
4k 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
911 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
3k views

Advice for ETL from Oracle to SQL Server?

I'm building a data mart in SQL Server which needs to extract each month from an Oracle database. Currently this is done by exporting to flat files, then running all the inserts/updates in SQL ...
1
vote
2answers
330 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 execute it. There are times when there is a lot of data to load. My ...
1
vote
2answers
2k 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
2k 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 ...
1
vote
2answers
2k views

Using SQL Profiler on SSIS package

Is it possible to use SQL Server Profiler to trace a running SSIS package? I tried to do that, but all I can see in the trace is executing dp_sqlagent_... and the other internal commands! None of the ...
1
vote
2answers
1k views

SSIS error Property BackupDirectory is not available for Settings

I am trying to edit the SSIS Back Up Database Task And it gives the folowing error Property BackupDirectory is not available for Settings 'Microsoft.SqlServer.Management.Smo.Settings'. This ...
1
vote
1answer
112 views

SSIS Performance tuning

Suppose my system has 8GB RAM and I have installed SQL Server 2012 on it along with the DataTools. Sql server engine uses 6GB memory. Will the SSIS uses remaning memory apart from 6GB or it uses 6GB? ...
1
vote
1answer
264 views

SSIS v/s Replication v/s logshipping

I have 2 SQL server instances, on different servers. One is Production instance and gets lots of reads and writes daily. Another one is for external applications to use a copy of few of the tables ...
1
vote
3answers
3k views

Encrypt/Decrypt output & input for SSIS task

I use SSIS packages to output the data from one system as Comma Separated Values, and then import that same data into a second system. I have a new requirement to encrypt the CSV text files that are ...
1
vote
2answers
33 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 ...
1
vote
1answer
319 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 ...
1
vote
1answer
761 views

How do I set up a Derived Column Transformation to get row number in SSIS 2014?

I am trying to import a ragged right flat file. I am wanting to import the file as one column and used Derive Column Transform to add a row number to each row. I would like to end up with a column ...