Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
1,059
questions
19
votes
2
answers
151k
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 ...
17
votes
4
answers
72k
views
Not able to create SSISDB catalog
Getting the error below while trying to create a catalog in sql server 2014 integration services.
Any idea what I missed in installation or anywhere else?
The catalog backup file 'C:\Program Files\...
- 323
17
votes
2
answers
55k
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 ...
- 5,907
17
votes
2
answers
23k
views
"Access is denied" when connecting SSMS to Integration Services
I receive the following error when attempting to connect SSMS to Integration Services using a particular SQL Server cluster's network name:
Connecting to Integration Services service on the ...
- 4,238
16
votes
4
answers
78k
views
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
, E....
- 15.6k
15
votes
1
answer
11k
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 ...
- 894
15
votes
1
answer
9k
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, SSIS....
- 23.9k
14
votes
2
answers
13k
views
Relating ExecutionInstanceGUID to the SSISDB
The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions ...
- 15.7k
14
votes
2
answers
549
views
How to implement a Set based algorithm/UDF
I have an algorithm that I need to run against every row in a table with 800K rows and 38 columns.
The algorithm is implemented in VBA and does a bunch of math using values from some columns to ...
- 169
14
votes
1
answer
3k
views
Executing SSIS Package from a stored procedure 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 ...
- 161
14
votes
2
answers
2k
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 ...
- 3,501
13
votes
2
answers
12k
views
ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?
Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
- 2,609
13
votes
2
answers
6k
views
SSIS 2012 Create environment variable fails
I'm working a script to port an Environment from one server to another. I am running into an issue calling catalog.create_environment_variable wherein I get the error "The data type of the input value ...
- 15.7k
13
votes
4
answers
20k
views
Avoiding "Row by row" fetch method when dealing with source LOB columns
I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying:
'Row by Row' fetch method is enforced because the ...
- 311
12
votes
1
answer
28k
views
Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it
I have a job that runs an SSIS package.
Currently the owner is a proxy account. I can run the job manually from a sys-admin account.
Our web service logs in using a limited account. It needs to ...
- 223
12
votes
1
answer
3k
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 question:...
- 1,368
11
votes
1
answer
740
views
SSDT Schema Compare doesn't work while a BULK INSERT is in progress
I'm working at a large ETL and DW project where we use TFS/source control together with both SSIS and SSDT.
Today, I found out that while an SSIS package is performing a BULK INSERT into a database ...
- 397
11
votes
4
answers
9k
views
Is it possible to develop a SSIS with a SQL Server Evaluation Version
We are thinking about buying an SQL Standard Server for implementing an ETL via SSIS. Since it is very expensive for us I would like to test developing SSIS Packages on a free version. Since the ...
- 299
11
votes
2
answers
514
views
To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS
I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run (...
- 213
11
votes
2
answers
4k
views
Tablock hint triggers deadlocks
I was inserting two data sets, using minimal logging, into an empty heap table using via two Execute SQL Tasks running in parallel and with SQL of the following form.
INSERT INTO Table (TABLOCK) ...
- 137
11
votes
3
answers
50k
views
Error: "To run a ssis package outside of sql server data tools you must install [send successful email] of Integration Services or higher."
NOTE that the title of this qusetion is a verbatim error message specific to this user. The error message illustrates a bug in the dtexec application, which substitutes a user variable into the error ...
- 2,679
11
votes
2
answers
39k
views
SSIS Script Component - How to modify the Output0Buffer
I've a script component which accepts records from SQL Azure database table. The script then invokes a web service, which returns the number of failed and success records.
For all the records, I ...
- 371
11
votes
3
answers
16k
views
SSIS keeps force changing excel source string to float
There is a column in Excel that is supposed to be text, but SSIS sees numeric text there and automatically makes it double-precision float [DT_R8].
I can change it manually in the Output branch's ...
- 3,225
11
votes
1
answer
692
views
Can't use Fuzzy Lookup in BIDS
A coworker of mine was having an issue with BIDS. He's doing a simple fuzzy lookup and is only looking against two rows (For Testing Purposes). The weird thing is that when he tries to execute a ...
- 3,501
10
votes
3
answers
14k
views
Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
We are just starting design for a new data warehouse and we're trying to design how our date and time dimensions will work. We need to be able to support multiple timezones (probably at least GMT, IST,...
- 357
10
votes
1
answer
9k
views
SSIS 2008 Package Configurations being ignored
With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time ...
- 6,592
10
votes
7
answers
42k
views
How to change a script task version in SSIS?
I added a script task to an SSIS project in VS2015. When I deployed to SQL Server 2016 I got an error message that the version 15.0 script is not supported.
Where does this version 15 come from? ...
- 2,278
10
votes
1
answer
7k
views
Running an SSIS package owned by a domain user from SQL Server running on a local service account
I want to run an SSIS package containing Transfer SQL Server Objects tasks. The involved servers are on the same domain, but the SQL Server services are running on local service accounts. So the ...
- 547
9
votes
3
answers
68k
views
Converting Non Unicode string to Unicode string SSIS
I am creating a package where I will be exporting data from a database into an empty excel file. When I added only the source and destination components and I ran the package I got a conversion error ...
- 3,225
9
votes
2
answers
23k
views
SSIS Data Flow data access mode - what's the point of 'table or view' vs fast load?
Using the SQL Server Business Intelligence Development Studio, I do a lot of flat file to OLE DB destination data flows to import data to my SQL Server tables. Under "Data access mode" in in the OLE ...
- 333
9
votes
2
answers
5k
views
In SSIS 2012, what is the purpose of flagging a package as an "Entry Point" package
In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package"
Doing a search I found this page on MSDN which states:
The value of 1 signifies ...
- 368
9
votes
2
answers
21k
views
sql server max memory includes SSIS?
I have installed 2 instances of sql server plus SSIS on the following server.
Note the amount of RAM is nearly 384 GB
And this is the max and min memory settings that I have applied to my 2 ...
- 15.6k
9
votes
9
answers
53k
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 (...
- 927
9
votes
1
answer
54k
views
SSIS - This error is thrown by Connections collection when the specific connection element is not found
I am writing a package to upload result of query to FTP as CSV file.
Package is working fine on local machine, but after transferring .dtsConfig and .dtsx on another environment it throws following ...
- 4,011
9
votes
2
answers
62k
views
How can I resolve SSIS Excel Connection Manager Error 0xC0209303?
I have created an SSIS package that imports an excel file into a SQL Server table.
The SSIS package runs without any problem when I run it locally on my machine but when I run it on the server where ...
- 3,225
9
votes
1
answer
24k
views
Assiging stored procedure results to SSIS variable
I am trying to get the value from a stored procedure into an SSIS variable, and then testing to see if two SSIS tasks can work if I added an expression. So for an example, I am trying to use this ...
- 667
9
votes
3
answers
4k
views
SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?
I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows ...
- 313
8
votes
1
answer
2k
views
SSIS organization
I'm familiar with SSMS, but I've never used .net, c# or visual studio (been doing other things: Assembler, c, unix, vi, oracle, etc).
I'm trying to figure out basic 2012 SSIS ETL (no BI or data ...
- 83
8
votes
1
answer
7k
views
SSIS Control Flow: Precedence constraints not working as expected
The control flow of my SSIS package is not behaving as expected.
What I want to achieve:
Test if CLR is enabled on SQL Server using
SELECT value
FROM sys.configurations
WHERE name = 'clr ...
- 737
8
votes
1
answer
4k
views
Why is SSIS slow to enumerate over many files in a directory and import them?
I have a terribly slow SSIS package. It's quite fast with one file, and reasonably fast with 100 files or fewer. (About one second per file)
However, if my directory has thousands of (very small) ...
- 351
8
votes
1
answer
7k
views
Timeout expired error while using copy database wizard
Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get ...
- 441
8
votes
1
answer
2k
views
Determine the PackageFormatVersion for multiple .DTSX packages files in a folder
Kenneth Fisher put a blog post out about how to determine What SQL Version is My SSIS Package? in April, 2015.
It has a table of which SQL versions map to which PackageFormatVersion of the SSIS ...
8
votes
2
answers
4k
views
How to track SSIS memory and CPU performance?
I may be missing some pieces to this puzzle, but this is what I have:
All of this was written for us by an outside firm.
There is a .Net application that runs as a service on Server A.
Inside that ...
- 1,201
8
votes
1
answer
1k
views
OLE DB Sybase Connection Failing
I have a connection to a Sybase database which we stage in a SQL Server 2008R2 database. I can connect the the database and the connection tests out fine. I am using an OLE DB source with a Sybase ...
- 3,501
7
votes
2
answers
1k
views
Data Flow Task Produces No Rows with Declare Statement
I have a SSIS package that is doing a simple SELECT statement and then loading into a table. I have anonymized the sql statement which should be painfully obvious when you look at the table name. I ...
- 3,501
7
votes
4
answers
4k
views
Duplicate database and keep it up to date - Replication or SSIS?
I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally.
We don't need ...
- 215
7
votes
2
answers
3k
views
Star join query optimization - Change partitioning, use columnstore?
I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which ...
- 73
7
votes
2
answers
34k
views
SSIS File System Task Copy File Could not find part of the path error
I have created an SSIS package that uses a File Systen Task (copy) that copies a file from 1 folder to another. The package runs fine when I run it from my local computer but when I import the package ...
- 3,225
7
votes
1
answer
9k
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 ...
- 3,225
7
votes
2
answers
27k
views
Running SSIS from a different server than the primary database instance
We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2. On the current production ...
- 175