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

Error Updating SSIS package to 2014 due to script component

I am trying to update a SSIS package that contains script component. The script component just gets the error description as described here. I am using the update wizard in SSDT 2015 which should be ...
0
votes
1answer
51 views

MsSql Bulk Insert With commit size 1 vs Simple Insert

Using SSIS OLE DB Destination, what is the difference between doing fast load with commit size 1 or inserts without the fast load option. I understand that using fast load is going to invoke the ...
2
votes
3answers
298 views

How to configure SSIS in SQL Server AlwaysOn

I have AlwaysOn Availability Group setup for my SQL Server. Now I would like to configure SSIS. How do I do it? Below link provides some information but since I am not DBA it's difficult to understand ...
1
vote
1answer
45 views

Oracle Wallet Connection setup for SSIS

How to configure SSIS package to connect to oracle using oracle wallet? Specifically for using OLEDB to connect to oracle and load data to oracle tables.
1
vote
1answer
140 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
0answers
110 views

Syncing a SQL Server table with a CSV file

I have recently started a project with sensors that add data to a single CSV file as data the data. I would like to have this data stored in a SQL Server database in a relatively real-time manner. I ...
1
vote
2answers
97 views

Database connectivity issue in SSIS

I have created a Connection manager in my SSIS project for database connectivity.I choose SQLClient Data Provider and select SQL server authentication mode for login.Test connection succeeded. In ...
0
votes
1answer
32 views

Transposing/Wrangling Database Table

Firstly, transposing is too specific a word for the kind of transform I want to do. I have a software that allows a technician to design custom forms for end users. From the form, I can see every ...
0
votes
1answer
55 views

ssis surrogate key in parent child tables

I have two tables Person & Ownership having one to many relationship (each person have many ownerships). Primary Key is person_id of type GUID. I am developing SSIS package to load data from both ...
4
votes
0answers
47 views

Large Variation in Bulk Insert time

So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart. The process is a simple data flow task with default settings for "Rows per batch" and the ...
1
vote
1answer
247 views

SSIS Script Task doesn't appear to run when scheduled

I have a script task inside SSIS which does not appear to run when the job is triggered from SQL Server Agent. I say does not appear to run because I don't know if it's the SQL Script task or some ...
0
votes
0answers
24 views

Execute SSIS in C# failed; “to run a SSIS package outside of SQL Server DT you must install FTP download of IS or higher” [duplicate]

I'm trying to run a SSIS package within a C# application, which downloads .csv file from FTP server and stores it localy. The package runs fine within SQL Data Tools, but fails when called in C#, ...
0
votes
1answer
44 views

What is the best SSIS Tasks for String Parsing database fields

What is/are the best SSIS Tasks to use in order to search for string patterns. This relates to standardizing address information. My developer does not have a confident grasp on using Regular ...
1
vote
1answer
85 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
24 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
0answers
51 views

LogFile Size increases by SSIS Package execution

On production server we have 125 GB free space. The SSIS package is doing some delete in Transaction. e.g. Insert 3 columns which are mainly business keys into a staging table and then deleting the ...
0
votes
0answers
30 views

SSIS buffer size and table with many columns

I have an Oracle view with around 10 million rows, each row has about 130 columns, I wrote a SSIS 2012 package which uploads the data of this view into a SQL Server table. The SSIS package fails each ...
1
vote
1answer
50 views

Returning ssis errors to sql queries

I have created a web site using VS 2013 that uses T-SQL queries to execute SSIS packages (created in VS2012). This seems to work however a couple of issues have come up. I must confess this is the ...
1
vote
1answer
154 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 ...
0
votes
2answers
36 views

Optional variable/parameter in SSIS

I have a SSIS package which has the following: Data Flow Task OLE DB Source (select * from table1 where DateCol <= ISNULL(@DateVar,DateCol )) OLE DB Destination DateVar variable And I need ...
1
vote
0answers
84 views

SQL Server Agent Jobs starting but is idle

We have a data warehouse with a few databases. Every morning we import data from source systems to our data warehouse. The final step used to be to initiate loading of an Essbase cube by a SQL job ...
1
vote
0answers
70 views

What can cause an “SCD is not compatible with this version of the DataFlow” error?

I created an SSIS package which includes several SCD data flows. This works fine in Visual Studio SSDT, but when scheduled (using SQL Agent), it fails, with this error repeated for each SCD: Code: ...
1
vote
1answer
205 views

SSIS ODBC: Mapping result set columns to variables returns error “Value does not fall within expected range”

In SSIS 2012, I'm using an "Execute SQL Task" to call a stored procedure that returns a single row with two columns. I'd like to map that result set to two SSIS user variables. Should be easy, right?...
1
vote
0answers
37 views

SSIS: Trying to configure branching logic in Foreach loop

Full disclosure: I posted this on SQL Server Central yesterday but got no answers. I hope someone here can assist me. I'm working on importing flat files into a database. I have used a Foreach loop ...
3
votes
1answer
128 views

SSMS: Import from flat file (.csv)

In SQL Server Management Studio (SSMS) 2008 R2, I wish to import a not-quite-properly formatted CSV into a database table. I was trying to use the wizard at <database name>->Tasks->Import Data. ...
3
votes
2answers
138 views

SSIS - How to parse extensions from path-filename strings

I'm importing a large set (126M rows) of file data from CSVs. Import works fine, except I'm trying to derive a column as I import rather than trying to script it out afterward (due to the number of ...
4
votes
2answers
118 views

SSIS OLEDB Command not firing triggers

Got a bit of a strange one that I can't seem to work out. We have a vendor database ( so we have no control over the table structure, foreign keys, etc...) that's on SQL Server 2012 SP2. We have a ...
1
vote
1answer
100 views

SQL Server Import Export Wizard Error - Server throw Exception

I am trying to move all database tables from one db to another as a nightly job. The first (export) db is running sql server 2008 - SQL Server Authentication - I connect with SQL Server Native Client ...
2
votes
1answer
106 views

Database in SIMPLE recovery model full using SSIS bulk insert task

I have a database in SIMPLE recovery model with 10GB free. The file I am trying to load through SSISs bulk load is 1GB. The table is empty (truncated prior to each loading), is a heap and has no ...
1
vote
2answers
934 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.
0
votes
0answers
111 views

SSIS: Check file for row delimiter and dynamicly update row delimiter in Flat File Connection Manager at run time

I am importing about 50 or so txt files into a SQL Server table. All the files have the same layout except that for some files the row delimiter used was {CR}{LF} and for others {LF} was used. ...
0
votes
0answers
70 views

Data Conversion error in SSIS after upgrade

We recently did a server upgrade from 2008 R2 to 2014 as well as upgrading our SSIS packages. Since then we receive an error when reading a flat file. The error is on a column with percentage values ...
5
votes
1answer
117 views

AWS RDS databases & SSIS

I have 2 AWS RDS databases (A & B) and need to create a datawarehouse with star scheme and slowly changing dimensions within AWS. From a MS background it makes sense to create a SSIS package to ...
5
votes
1answer
42 views

SQL Query not throwing error even if the entered scale value is higher than specified

I need to capture numeric values without truncation. But the output value is getting truncated even though I specified the Scale as '2'. Column 'A' datatype - numeric(5,2) Case 1: Entered value: ...
0
votes
1answer
27 views

Aggregation of duplicate data

We have an interesting problem around duplicate data. Our source files can contain multiple rows of data for a single employee benefit. As an example, a health care benefit could be split up and ...
11
votes
1answer
160 views

Executing SSIS Package from a sproc with different user privileges

I'm having issues with allowing my users to execute SSIS Packages in a reasonable manner due to varying levels of privilege required. The Scenario We've created a data warehouse, with two different ...
1
vote
1answer
76 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 ...
-4
votes
1answer
50 views

SQL Server and msbi relation and difference [closed]

With SQL Server 2008R2, we have analysis services, reporting services, and data moving and manipulation services with SQL Server Management Studio. So why do we need SSIS, SSRS, and SSAS?
3
votes
1answer
108 views

Starting SSIS Package through SSIS Catalog

I've developed an SSIS package to load files but it is behaving very strangely. I can kick off the package running start_execution on the SSISDB only when I log directly onto the server. Kicking it ...
1
vote
1answer
53 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 ...
3
votes
2answers
127 views

How to change Zeros ( 0 ) to NULL in SSIS?

I would like to change some Zeros into NULL using SSIS 2008. In that context I tried "conditional split" where I have set the columns test in question with the following expression: REPLACE((DT_WSTR,...
5
votes
1answer
748 views

Best Practice for SQL Server data validation

I have CSV files that I would like to import into SQL Server 2014. Each record in the files has around 20 columns, each of them should be validated for data type compliance as well as some other ...
5
votes
1answer
96 views

How can I restrict the version of the target DB instance in a SSIS package?

How can I restrict an SSIS Package to only execute on SQL Server 2012? Is there a built-in way to do this (metadata?), or do I need to do a query? (Possibly belongs in a new question): I can get ...
4
votes
3answers
381 views

How to correctly discard old backups?

I have an Execute SQL Task in my SSIS control flow that's backing up a number of databases before it proceeds to run the overnight ETL: EXECUTE master.dbo.xp_create_subdir N'G:\Backups\DQ' GO EXECUTE ...
1
vote
0answers
32 views

Populate ID Field by Converting Group Name Values

This is a bit tricky to explain, but here goes. We have a System Rights database that loads system users and system rights or access levels into two different tables. The database is already built ...
1
vote
1answer
73 views

How to set SSIS flat file connection row delimiter to blank line?

I have a text file with the following format: DataColumn1 DataColumn2 DataColumn3 DataColumn1 DataColumn2 DataColumn3 DataColumn1 DataColumn2 DataColumn3 Each row with text in the file delimits a ...
0
votes
0answers
64 views

How do you stop an SSIS when an empty text file is processed

I have an SSIS package that pulls a file from an SFTP server, decrypts it, pushes it into a table, then merges into a text file, on to uploading to another SFTP server. This package runs flawlessly ...
1
vote
1answer
61 views

Conditionally move files in SSIS

I have a simple SSIS package with a ForEach Loop Container with 1 Data Flow task that triggers when a file is present. If that task succeeds, I want to move the input file to a "success" folder. If it ...
1
vote
1answer
52 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 ...
5
votes
2answers
353 views

SQL Server 2014 SSIS Package Ignores Script Task when running as AGENT

Alright, so here's what I have going on. SSIS Package that queries data from our production system and drops them into CSVs. Have a VB script task that uploads them to a third party. Needed a multi-...