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

-4
votes
1answer
44 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
85 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
46 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
1answer
71 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: ...
5
votes
1answer
323 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
95 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
359 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
28 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
60 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
48 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
33 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
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 ...
5
votes
2answers
216 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 ...
0
votes
1answer
51 views

SSIS Package - “Subquery returned more than 1 value” - Inserting one record into a table

SSIS on SQL Server 2008 R2 I have inherited an SSIS package that is failing when importing a spreadsheet. The part of the failure is in a data flow that consists of :- * an Excel Source Data Flow ...
0
votes
1answer
93 views

Communication Link Failure in SQL

I have an SSIS package that runs daily in SQL agent, there are days that the job is failing due to "Communication Link Failure" but sometimes successful. I search the net for some solutions and there ...
2
votes
1answer
244 views

SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0

I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job. Now it fails intermittently with the ...
3
votes
2answers
132 views

What could cause “lagging” in SSIS execute SQL task?

Recently I noticed that the part of my ETL process loading the data into the staging area sometimes takes longer and sometimes shorter. With the following query (executed in the integration services ...
0
votes
1answer
43 views

Change database used for SQL Server Integration Services logging

The SQL Server Best Practices Analyzer noted that my Integration Services' logging table is in a system database. It recommends that I move this to a non-system database. (See here) How do I make ...
0
votes
1answer
31 views

SQL Server Merge mandate

Does the target table need to be identical to source table schematically? Specifically: 1) do they need to have the same number of columns and do the column types must be the same?, 2) do the column ...
9
votes
1answer
327 views

Problem creating a transaction in an SSIS package

I'm working on a package that needs to use a transaction but I'm currently getting the following error: SSIS package "CATS-Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, ...
0
votes
0answers
20 views

SQL Server 2012 process (Stored procedure in SSIS Package via SQL Agent Job) stays running where it normally ends within 10 minutes

[] SPID 70 : SSISDB.catalog.start_execution;1 SPID 113 : EXECUTE ETL.uspLoadDataStoreData'dbo', 'Project', 0
0
votes
0answers
33 views

commonly used diagram for ETL

A the title indicates. Am i wondering what is the most commonly used diagram to explain an ETL process? In my case the process is rather linear, would a flowchart suffice? or is a Use-case also ...
1
vote
0answers
29 views

SSIS package version GUID does not stay stable between runs

We have a large quantity of SSIS packages created in visual studio 2008. Currently running on hundreds of servers. We are logging the details of each package before it runs. One of the things we are ...
1
vote
1answer
73 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 ...
0
votes
1answer
63 views

Do you need Table Lock when you have multiple OLE DB connections inserting to the same table?

SSIS is not really my forte. I noticed you have a table lock option on OLE DB destinations where the entire table becomes locked in what I assume is during the transaction of inserting data. What ...
1
vote
0answers
116 views

SQL Server Job Cannot Run SSIS Package Error 0xC00160AE Cluster Environment

I have a 2 machine SQL Cluster and a separate SSIS/SSRS Server. I'm running SQL Server 2014 and Integration Services 12. I'm getting Error 0xC00160AE when trying to run a job from my SQL cluster that ...
1
vote
0answers
26 views

SSIS DTSPackageType behaviour

I created a new SSIS package within Visual Studio 2013 with SSDT BI Tools (12.04.2430.0) installed bu when looking into this package I can see that the DTSPackageType is set to DTSDesigner100. Based ...
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 ...
0
votes
1answer
97 views

Extract data from Oracle DBF files

I'm completely new to Oracle and I was provided with what appears to be an Oracle database backup (170 DBF files and 3 CTL files). I'd like to extract the information using SSIS or Pentaho's Kettle ...
0
votes
0answers
93 views

How to import data from Sqlite into SQL Server 2012

I need to import data from sqlite into SQL Server so we can include it in our data warehouse. Based on previous system volume. Id expect there to be 50-100,000 rows after a couple of years. For ...
2
votes
0answers
108 views

SQL Agent - PSexec remote commands causing SSIS packages to hang

First post here, so please be kind! I have a bit of a problem with a set of SQL Agent jobs (SQL Server 2014, running on Windows server 2012 R2) that I am trying to setup. Each job is a multiple step ...
1
vote
0answers
45 views

SSIS package with changes to Excel file

I have an SSIS package that loads up an Excel file on daily bases; from time to time the file changes. It can have additional columns in the Excel worksheets. How can I know if the Excel file has ...
0
votes
1answer
21 views

Whole numbers from sql column using SSIS

Below is the sample SQL. From Source1 tb, I need only whole numbers of Value column into ValueNEW of Destination1 tb as shown in the output pic. --Source1 IF EXISTS (SELECT * FROM sys.objects ...
13
votes
1answer
867 views

Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job ...
1
vote
0answers
33 views

SSIS Guarantee that all data will be moved to target when running data flows in parallel

My package has the following two tasks which run in parallel. DF - Move source data to target loads a copy of all source data to target via OLEDB Dest - Target and also loads a copy of source PK ...
0
votes
0answers
35 views

Primairy Key constraint error in SSIS with delete

So I have an SSIS package that deletes a bunch of data then inserts the new data. Even though the delete completes without error I am still getting a primary key constraint violation. When I run the ...
0
votes
1answer
54 views

How to Publish Checked-In changes only in Team Foundation Server 2013 to SQL Server?

I would like to ask you how to publish checked-in changes only in Team Foundation Server 2013 to SQL Server? It's a new installation of TFS 2013 - installed by me. Currently when I create new object ...
0
votes
1answer
37 views

Better parallelization than a sequence container in SSIS 2008?

We currently have a process that has to process hundreds of thousands of rows an hour. For valid reasons, it has to call a stored procedure for each row. We're using a SSIS package (SQL Server 2008) ...
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 ...
7
votes
4answers
4k views

SQL Server 2014 - how to query SSISDB to find out the errors in the packages?

I have seen this question SSIS 2012 - How to Query Currently Running Packages in T-SQL? It gives me the following script: SELECT E.execution_id , E.folder_name , E.project_name , ...
2
votes
1answer
140 views

Running scheduled SSIS pkg with SQL Server 2012 Web Edition

I have a client with SQL Server 2012 Web Edition. I believe it has SQL Server Agent, but I see on the following Microsoft page that many integration services are not supported: ...
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
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
176 views

SSIS Execute Sql Task not executed on server

I have a package that has an Execute Sql Task. When I run it in SSDT it works fine, the query is executed. When I deploy it on the server and run the package, the query is not executed and there is ...
0
votes
1answer
31 views

Change SSIS package location (REPOST from SO) [closed]

My SSIS Package are located in shared drive with a long path name. To make the packages easier and not to remember long path name, I use "Map my network drive" option and use X: drive as an alias to ...
0
votes
0answers
31 views

Change SSIS package location

My SSIS Package are located in shared drive with a long path name. To make the packages easier and not to remember long path name, I use "Map my network drive" option and use X: drive as an alias to ...
0
votes
2answers
170 views

problems when importing csv files into SQL using SSIS when the columns are not consistent from quarter to quarter

I am using SSIS to upload a bunch of csv tables into SQLexpress. The csv files are quarterly data files. They are largely consistent but every couple of years a column may be added or an existing ...
0
votes
0answers
68 views

Selected Parameter values is not passing to another tab click in SSRS view report

I built an SSRS Report in which the final report is populated on a tabular formatted report with the combination of many sub reports. The report takes 3 input parameters values as date,fund ...
0
votes
1answer
24 views

SSIS - Update Specific Cell in CSV

I have a pre-defined csv template. The header row begins on row 5. Prior to this I have a date value in cell B2. How do I update this cell value in SSIS?