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
0answers
767 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
1answer
4k views

sql agent job hangs without any errors or warnings [closed]

Problem: we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without providing any errors or warnings. It just hangs. BUT when i stop the job and run again ...
2
votes
0answers
142 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
166 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
2
votes
0answers
579 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
635 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
2k 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
621 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
12k views

SQL import data when both source and destination has data and identity column

I am using Import and export data option to import data from one database to another database. Both database have similar structure of tables. All the tables have primary key which is identity ...
1
vote
1answer
85 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
3k 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
2answers
863 views

Why does my SSIS package produce different results when executed from a maintence plan

I have a SSIS package that picks up a file in one network location transforms the data and drops off files ready for import in another network location. The package reads a file and writes a new file ...
1
vote
3answers
1k views

SSIS 2008 painfully slow to import CSV

I am using SSIS to import a 25,000 row CSV to an empty table. The table has a primary key that is populated as the data is inserted. I am using an ADO.Net destination. What reasons are there why ...
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
2answers
444 views

SSIS: script task on Visual studio 2013 can not open since Visual Studio 2015 was installed

Until the installation of Visual studio 2015, it was always opening Visual studio 2012. Attached the only error he throws, It seems he was trying to open Visual Studio 2015.
1
vote
1answer
64 views

SSIS Derived Column Conditional Time Modification

I'm using SSIS to transfer data from a MySQL database to a SQL Server one. There's one point that I'm currently stuck on, however. MySQL TIME datatype supports values greater than or equal to ...
1
vote
1answer
196 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
551 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
5k views

'skip rows' in a flat file source does not work as advertised

In SSIS(2012 specifically) there is an option in the Flat File Datasource to skip a number of lines at the beginning of a file. The preview appears correctly. However, when trying to execute the data ...
1
vote
2answers
6k 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
4answers
5k 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
351 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
3k 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
3k 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
3k 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
18 views

Doing lookup transformation

I have a contactTypeDescription and I want to find the corresponding contactTypeId for that description. I am doing the following, however it throws a warning saying that atleast one column must be ...
1
vote
1answer
47 views

SSIS push and pull performance

After reading Which one is more efficient: select from linked server or insert into linked server? I wonder if this is true also for SSIS? For example, I have 2 SQL Servers (A and B) and SSIS ...
1
vote
1answer
383 views

SSIS - Fixed width flat file mapping tool/technique

I am wondering if there is a tool (or a special technique) that anyone has used to map fixed width flat files in SSIS Flat file connection managers? Usually I just use the Flat File Connection ...
1
vote
1answer
3k views

SSIS package hits “login failed for user” when simultaneous operations occur against the same target database

Quick summary We have been working on migrating our library of SSIS packages from SQL Server 2008 hosted on physical Windows Server 2003 to SQL Server 2012 hosted on virtual Windows Server 2012. The ...
1
vote
1answer
3k 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 ...
1
vote
1answer
3k views

SSIS export to CSV Flat File Destination task, error The code page on input column is … and is required to be

I get this error on a File Destination Task, do you know what the cause is and how to fix it ? Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at export ...
1
vote
1answer
131 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
485 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
1answer
47 views

SSIS Buffer/Memory Failure

I get this error when running SSIS package... A buffer failed while allocating 104857600 bytes. The system reports 31 percent memory load. There are 17179328512 bytes of physical memory with ...
1
vote
1answer
21 views

decrypt data in column before load into destination

I have a table named city which contains two columns city_id (int) city_name (varbinary) -- encrypted column I want to extract data from this table, transform (decrypt the city_name) and load ...
1
vote
1answer
77 views

Incremental extract strategy using SSIS and CDC

I'm looking for a strategy for extracting data from a SQL Server database that is configured for change data capture (CDC) using SSIS. Most of the documentation I have seen around using CDC and SSIS ...
1
vote
1answer
45 views

Is this Normal SSIS Foreach Loop Container behavior

I have a Foreach Loop Container that loops through files in a folder. I thought the Loop Container would only look at the files 1 time but apparently it will start back at the top and pick up a file ...
1
vote
1answer
45 views

SSIS line number from file

I have done some tests in flat file source trying to see if it read the file in order of the number of lines and all of them the number of the line read was inserted in SQL Server equally. I was ...
1
vote
1answer
49 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 ...
1
vote
1answer
72 views

T-SQL Check another table rows if match delete them

I am creating a script in SSIS where I want table A to check if rows exist in table B. If they exist, they must be deleted. Table A is an archive table named Customer_archive Table B is a table that ...
1
vote
2answers
51 views

SQL Server 2014 data integration between servers

Given two MS SQL servers, each with a different database, how does one create an SSIS package such that one can run a complex query that fills a temporary table and then transfer that data as a bulk ...
1
vote
2answers
39 views

Should I convert timezones when moving data to a report server/data warehouse

I'm writing an SSIS Package to take data from a production DB to a reports server/data warehouse. The other databases on my reports server are all in Mountain Time but this DB is in UTC should I ...
1
vote
1answer
52 views

SSIS Merge component performing a UNION instead of merging

I have a package to combine data from multiple databases. I'm trying to bring together two data sets so they can be written to a single table. There's a common SSIS_ID (DT_I4) used to match rows and ...
1
vote
1answer
62 views

dtsinstall.exe does not deploy packages

I created SSIS project solution in VS2008. I created 5 packages and deployed them to SSIS Storage succesfully. I executed 4 packages successfully. I leave SQL server few days alone (holiday!). I ...
1
vote
1answer
126 views

When does SSIS SQL Server Configuration write to the configuration table?

I am using SQL Server configuration in an SSIS pacakge. Other than when you create/edit the SQL Server configuration entry in the SSIS Package Configurations, are there other times that SSIS writes to ...
1
vote
1answer
112 views

When installing SQL Server 2014 SSIS, what are the meaning of “authentication level”?

After installing SSIS 2014, it is the time to deal with the permissions. When dealing with the DCOM Config (control Panel->Administrative Tools-> Component Services), you want to set up the ...
1
vote
2answers
809 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
1k views

How to use HTTP Connection Manager in Script Component? (Not Script Task)

I'm trying to perform the same scenario as in the following link; Create a SSIS Script Component as a Data Source that uses a pre-existing HTTP Connection Manager to retreive a page with GET and emit ...