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

1
vote
2answers
1k views

Moving SSIS Packages to New Server

I have a number of SSIS packages in a number of folders on MSDB SSIS on SQL Server 2008R2. We are migrating to a new server host but remaining on SQL Server 2008R2. How can I copy/migrate these SSIS ...
0
votes
1answer
332 views

execute [SSISDB].[catalog].[create_execution] with 'execute as"

We are testing a 3rd party tool to schedule/execute jobs in our environment so trying to test launching SSIS packages within the integration service catalog. I have found examples of the code to run ...
0
votes
2answers
23 views

Create a read only excel file from SSIS?

Is there a way to create a read only excel file from SSIS?
0
votes
0answers
23 views

How to sync two tables which are on different databases and servers using SSIS?

I am relatively new to SQL Server's tools. I am on the following situation: On several stored procedures on a database D1 on server S1, I need to use data that is stored on a database D2 on server ...
0
votes
1answer
17 views

SSIS append trailer with Script

I am trying to add a footer to a flat file output. I have 2 data flows in my control flow. One creates the file from the DB. It goes from OLE DB Source to Flat File Destination. The second control ...
0
votes
1answer
35 views

SSIS permissions for specific databases

I'm quite new to SSIS. Is it a straightforward to grant SSIS permissions in the AD environment so that that a user can only create/manage/run ETL tasks on specific databases/Analysis Services ...
1
vote
2answers
109 views

TSQL vs SSIS Lookup

I was trying to find some well performing Tsql equivalents for the SSIS Lookup component. Especially for Fact table loading. After few different query patterns the most obv performed the best: ...
0
votes
1answer
48 views

Loading XML data from SQL Server directly into MarkLogic?

Has anyone loaded XML data from SQL Server to a MarkLogic document store? This would be preferable to dumping XML to file system and then using mlcp. Maybe with SSIS?
3
votes
1answer
45 views

Metadata Headache

A process that I don't have control over is dropping and re-creating tables in a MySQL database every night. This wouldn't be a problem (I think), if it re-created the tables identically every time. ...
0
votes
1answer
584 views

Invalid date format in SSIS

I am using BIDS to export data from Oracle to SQL server. I am using OLEDB source and destination. I have around 6 datetime fields in ORACLE out of which two fields give no problem while 4 fields give ...
0
votes
1answer
82 views

Namespacing database names on my server in order to test multiple different SSIS packages

An SSIS package that I'm building has two OLE DB connections, to databases named "core" and "staging". I created test versions of these two databases on my local SQL Server installation. I named them ...
1
vote
3answers
4k views

How to move a SQL Agent Job to another Server?

I'm moving from SQL Server 2005 to SQL Server 2008R2, and I see there is one job in the SQL Server Agent Jobs that I need to move but I see it also in the SSIS---> Stored Packages\MSDB\File ...
1
vote
1answer
834 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 ...
1
vote
1answer
2k views

sql agent job hangs without any errors or warnings

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 ...
0
votes
0answers
16 views

Validating Excel Counts against Database Inserts

I have to design a SSIS package which uses Excel Source files and inserts the data into SQL Server Database. The problem is that I've created the packages top transfer data, but now I need to ...
1
vote
2answers
57 views

SQL ssis delete imported rows from source table

I have setup a simple SSIS package that copies data from a source table to a destination table which works. I would like to get the package to also delete the data in the source table that has been ...
4
votes
1answer
58 views

SSIS Multicast performance question

I'm wondering how multicast actually works. So here is my setup. I have one source flat file(csv) of about 100k rows, maybe about 250-300 columns, and I'm multicasting that file into 7 one-to-one ...
-1
votes
0answers
26 views

SQL table up to date from TM1 cube

Can anyone recommand a good approach to keep an SQL table updated with the data from IBM COGNOS TM1 cube ? is there any way to create a process that runs daily and extract data from TM1 in xls or csv ...
1
vote
1answer
121 views

Why is Excel File Opened Exclusively By Another?

I'm creating an SSIS package to loop through a series of Excel files (.xls). I create my while loop container and then create my data flow, which includes an Excel source. When I edit my Excel ...
0
votes
1answer
1k views

SSIS Package Validation Error when Executed From Windows Task Scheduler

I have deployed my SSIS package using the project deployment model to the SSIS catalog and experience a strange validation error when I try to execute the package. I believe this is due to a ...
0
votes
1answer
92 views

SSIS 2012 metadata not refreshing on re-deploy

I have three systems: DEV, UAT, PROD, all running SQL 2012 Enterprise. I have existing packages that hit objects on these systems. Every time I have to make a change that affects metadata, it's not ...
0
votes
1answer
87 views

Apply settings to multiple output columns in SSIS flat file source

Is there an easier way to apply property settings to multiple output columns in a SSIS flat file source, rather than manually applying to each output column? (SQL Server 2014.) I am attempting to ...
1
vote
1answer
279 views

SSIS - insert system date variable as a execute sql task parameter

I am trying to insert the system:starttime variable into a table by passing a parameter into an execute sql task. I dont have much knowledge of SSIS data types, and I am currently having trouble ...
1
vote
1answer
35 views

Difference in number format (comman and dot) on SSMS and SSIS

I have a numeric(5,2) field on my SQL Server database. When I run the query, the number on that field appears with . (dot) separator. However, when I preview the query on SSIS, it appears with ...
2
votes
1answer
213 views

How do I grant the privilege of connecting to SSIS?

Attempting to run SSIS packages using dtexec.exe, I got this error message: Could not load package "D:\Filename.dtsx" because of error 0xC00160AE. Description: Connecting to the Integration Services ...
0
votes
0answers
43 views

Split and Concatenate data

I have a group of fixed width text files that come in daily. These files contain thousands of records each.Each record has an (01) as Header (02) as Detail 1 (03) as Detail 2 I would like to ...
1
vote
1answer
62 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
186 views

Error when trying to deploy SSIS package

I am trying to deploy my 2012 SSIS project to the Integration Services catalog on the SSIS server (also 2012). I am getting the error below. I checked that there is plenty of space in SSISDB and ...
0
votes
1answer
142 views

How to divide data per client from a single database?

I am looking for best practices to separate data from a single database into multiple client specific databases. Currently my application dumps data for all the customers into single database and I ...
0
votes
1answer
214 views

Downloading files based on most recent date and timestamp

I have a requirement to download zipped files from an SFTP site using C# and Script Task within SSIS. We have multiple files on the server with the same name, but with different date and timestamps. ...
0
votes
1answer
324 views

SSIS Package Execution From Visual Studio Unit Test

Hello I am attempting to run a test which contains the following code: string pkgLocation; Package pkg; Application app; DTSExecResult pkgResults; ...
0
votes
0answers
43 views

SSIS Dataflow Task issue, moving data from SQL Server 2008 to MySQL via ADO.Net

Can you please help me in getting this problem solved.. I'm trying to build a SSIS package where I'm moving the data from SQL Server 2008 to MySQL. However I'm getting a weird problem in dataflow ...
2
votes
3answers
828 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 ...
0
votes
1answer
77 views

SSIS import with header record

I have fixed length text files that I have to import into SQL Server daily. These files contain header records (01) and Detail records (02). I am trying to build an SSIS package to do this. I have a ...
0
votes
0answers
1k views

SSIS package starts and stops immediately with a DTS_W_MAXIMUMERRORCOUNTREACHED, but there's no error to read

I have a package that, when I run it in Development mode, it outputs the following into "Execution Results": Import Validation has started Validation has ended Start, 10:36:27 PM ...
0
votes
1answer
114 views

Problem with SSIS Variable Expression containing getDate()

Brief Architecture My SSIS package is generating dynamic excel files through Data flow. The file name format is "XYZ_Year_Month_Day_Hour_Minutes.xlsx". So I am using variable with expression which ...
2
votes
2answers
11k views

The value could not be converted because of a potential loss of data [closed]

Let me start out by clarifying that this is not a duplicate question, nor a potential duplicate for that matter. I have tried implementing every answer to every single variant that already exists of ...
1
vote
1answer
79 views

SSIS Packages with SQL Server Express 2008 R2

I understand from various questions [1] [2] that SQL Server Express 2008 R2 doesn't support SSIS packages and that there's only primitive support for ad-hoc data imports / exports. I have already got ...
8
votes
1answer
1k views

How do I determine if a SSIS transform is non-blocking, partially blocking, or fully blocking?

There are several blogs which claim to categorize SSIS Transforms into blocking (asynchronous), non-blocking(synchronous) and partially blocking(asynchronous). When looking into the specific ...
0
votes
1answer
31 views

SSIS Condition that include two connections/data bases

In a SSIS package , I need some sort of condition that include two connections. The simplified problem is: A simple CSV import to SQL Server, DB1, table 1( col1, col2 ). I need to populate table 1 > ...
0
votes
0answers
31 views

SSIS - ODBC Parameter Size

I have created a SSIS package which runs every hour from my Machine. First part of my Package runs a Execute SQL Task and result set (single row, single column) is a comma separated list of Unique ...
1
vote
1answer
50 views

Granting permissions to SSIS for developer

I installed SSIS a couple of months after installing SQL Server 2014. I granted permissions to users per MSDN and rebooted SSMS and SSIS. I can login remotely from my client without issue, but have ...
0
votes
1answer
148 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
69 views

SSIS Package Validation Error email task when attaching file created by previous task

While running in debug mode an SSIS package I get the folowing error. "file ... does not exist ...", but the file is created by a task earlier in the process, so most probably it will exist at ...
2
votes
1answer
96 views

Prevent Subscription to report from running on a holiday

I have a SQL Server that I am using for building and distributing reports. I have built all the packages for importing data as needed as SSIS packages. These reports only need to be run after the ...
0
votes
0answers
443 views

How can I resolve timeout errors in SSIS around midnight every night?

I find a pair of these in my Application Event Log a little after midnight every night: The SSIS Execution Process could not write to the IS catalog: MyServerName:SSISDB Error details: Unable to ...
2
votes
4answers
164 views

Getting new and updated data with an archive column

I am trying to optimize an SSIS package that retrieves data from a very large transaction table that gets millions of new records daily. The data is being collected from several hundred field servers ...
1
vote
2answers
232 views

Unable to connect to FTP server using “FTP Connection Manager”

I'm trying to setup an SQL agent job to use my SSIS package. The package contains an FTP task that is failing. I'm getting the following error in both the job history and logging that I've setup (Log ...
4
votes
3answers
2k views

How can I dynamically back up all SSAS databases on a given instance?

I want to dynamically back up all the databases on a given SSAS instance using a SQL Agent job (which would most likely involve executing an SSIS package). It is imperative that this is a dynamic ...
0
votes
0answers
48 views

SSIS cannot access the file

I have a SSIS package which performs a few tasks using SQL Server Agent. First it executes a script task, then accesses Excel files, executes a SQL query and moves files to another directory. The ...