Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
66
questions
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
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
3
votes
1
answer
2k
views
VLDB with large quantity of data file imports
I am a little out of my league with this scenario and hope this community can help get me on the right direction.
I have recently been tasked with re-designing and implementing a database application ...
6
votes
2
answers
10k
views
Searching SSIS packages for the use of specific tables
I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.
- 267
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
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
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
5
votes
2
answers
2k
views
Copy data from multiple databases to a central database for reporting and analysis
I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting ...
- 51
4
votes
1
answer
1k
views
How can I make sure the SQL Server query optimizer uses the exact tables in the query
I have a query running in SQL Server 2008:
select count(*)
from table1 join
table2 on table1.col1 = table2.col2
In the data warehouse where this script runs, a table called thin_table1 is ...
- 149
4
votes
3
answers
1k
views
Why does my procedure run slowly until I update statistics?
I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the ...
- 203
3
votes
1
answer
1k
views
SSIS - SSAS: Missing data handling
I have a solution in SSDT with a SSIS and a SSAS project, with missing values in both numeric and text columns.
Is it a good practice converting all the missing values of these columns to NULL during ...
- 99
3
votes
2
answers
6k
views
Table data transfer using SSIS and triggers on destination table
I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using.
I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do ...
- 185
2
votes
1
answer
1k
views
Permissions for SSIS/SQL Server agent jobs for multiple user groups
What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only? For example, I have 3 folders in the Integration Services catalog. Dept_A, ...
- 1,973
1
vote
2
answers
1k
views
Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?
I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero.
From the DBA I ...
- 463
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
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
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
7
votes
1
answer
3k
views
SQL Server: Unable to save import export package, but am on Standard Edition
I am confused.
Issue: SSIS ("SQL Server Import Export Wizard") should let me build a data import package and save it to a file, for easy re-running (converting to a task, etc.).
I want to save a ...
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
2k
views
SSIS OLE DB Source Editor Data Access Mode: "SQL command" vs "Table or view"
In BIDS, I'm running a Data Flow Task & I'm wondering what the difference is behind the scenes between using "Table or view" vs "SQL Command" for the Data Access Mode for an OLE DB Source.
For ...
- 173
6
votes
1
answer
8k
views
Stopping SQL Server Agent: Effect on runnings jobs
On a server with MS SQL Server 2008 installed, we use the SQL Server Agent to schedule a large amount of SSIS-packages, all with unique schedules throughout the day.
Now and then, the server needs to ...
- 161
5
votes
2
answers
19k
views
SSISDB master key
I recently had to recover my ssisdb databases, along with all other user databases to a new server because of a hardware failure.
I am now seeing the following errors when trying to run ssis jobs ...
- 676
5
votes
4
answers
13k
views
"SSIS Evaluation Period Has Expired" on Dev Instance?
I recently started receiving the following error on a dev server. At one point SQL Server 2016 CTP was installed here but we've upgraded since then. I've tried uninstalling / reinstalling SSIS as well ...
- 811
5
votes
1
answer
14k
views
Migrating from one SQL Server 2008 R2 instance to another
I just started a new dba job and have been given a new server and told to move everything from the old server to the new one, brand new windows and SQL Server installations. I know I have to transfer ...
- 209
5
votes
2
answers
5k
views
How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?
I am having a (rather standard) daily maintenance plan created with SQL Server Management Studio Maintenance Plan Wizard.
AFAIU, the penultimate task "Back Up Database Task" will be executed only ...
- 1,382
4
votes
1
answer
1k
views
SSIS with MultiSubnet Availability Groups
I am planning for a "multi-subnet" AG group . 2 at the primary data center in DC and one at San Jose. We are planning to test a vendor application which requires SSIS. I am planning for the SSIS to ...
- 625
4
votes
3
answers
3k
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 ...
- 41
4
votes
0
answers
656
views
Why is my SSIS package not getting updated?
I have a Integration Services project within SSDT that has multiple packages. When I right click a single package and choose deploy, SSDT says the operation completed successfully.
However, when I ...
- 13k
4
votes
3
answers
9k
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 ...
- 721
4
votes
1
answer
3k
views
Bringing web service data into SQL server
I need to retrieve a data set daily from a hosted Web service. I can retrieve the XML "column names" in an SSIS package. But I need to retrieve all the data in the data set in csv format or bring ...
4
votes
2
answers
50k
views
Access denied when trying to run an agent job
Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server.
12/03/2012 17:32:15,...
- 3,225
4
votes
1
answer
5k
views
Licensing standalone instance of SSIS
Basic background: I have an application on an app server (no MSSQL installed) that I want to allow to call an SSIS package.
One Solution: One option seems to be that we can install just SSIS on the ...
- 153
4
votes
1
answer
3k
views
How do I access the full text of an error in SSIS GUI?
I have an Excel task which is providing error text through the GUI, but I don't know how to access the error task without the GUI and the error disappears if I move the cursor from the X
I have the ...
- 1,368
3
votes
2
answers
6k
views
how to use database mail from SSIS
I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks
- 223
3
votes
2
answers
5k
views
Cannot give NT SERVICE\MSSQLSERVER permissions on network drive
I have an SSIS package that I would like to run as a job. The flat file source sits on Server A while the package and job sit on Server B.
When I run the job that executes the SSIS package I get an ...
- 33
3
votes
2
answers
9k
views
How do I fix the Code Page in SSIS Lookup Transformation to be 65001?
I have an SQL server 2019 with databases and tables all set with the collation Latin1_General_100_CI_AS_SC_UTF8.
The relevant table has code and desc columns which are both of data type varchar.
In ...
3
votes
1
answer
17k
views
Running an SSIS package without the SSIS service installed
On a SQL Server 2005 instance in our DMZ, we have BIDS installed but not the SSIS service.
If I have an SSIS package on that server, can I still schedule to run it without SSIS service?
I tried ...
- 1,173
3
votes
1
answer
1k
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 ...
- 309
3
votes
2
answers
508
views
"Upsert" trigger writing mass duplicates from SSIS
I have a problem with an "upsert" trigger, being triggered by a SSIS package with fastload. (FIRE_TRIGGERS is enabled)
CREATE TRIGGER [dbo].[MP_VOL_UPSERT]
ON [dbo].[vwMP_VOL]
INSTEAD OF ...
- 133
2
votes
1
answer
417
views
Documenting SSRS and SSIS jobs that are the Job Activity Monitor
We are running a SQL Server 2014 database using SSRS as a reporting tool. We have data coming in via SSIS jobs and linked servers. We have developers creating various SSRS jobs and scheduling them. ...
- 45
2
votes
1
answer
5k
views
Update existing SQL Server 2008 table via Flat File using SSIS
I am trying to update existing records or add any new ones to a SQL Server table using a flat file. Also I need it work as an SSIS package. I don't get an option to do a source query like I do for ...
- 121
2
votes
1
answer
7k
views
Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)? [duplicate]
I am having dozens of MSSQL Servers 2012 R2 on Windows Server 2008 R2 with similar setup, configuration (in virtual machines) in production. Neither has SSIS installed.
I have to similarly maintain ...
- 1,382
2
votes
4
answers
19k
views
How to avoid empty rows in SSIS Excel Destination?
Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task:
OLE DB Source:
Data Conversion (to handle the annoying UNICODE / NON-...
- 1,236
2
votes
0
answers
873
views
SSIS Script Component errors sporadically with the same code
I have a SSIS Data Flow Task that takes CSV output (from a flat file connection) and passes that to a Script Component. When I run the task I get the following error:
Script Component has encountered ...
- 2,278
2
votes
1
answer
11k
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,394
2
votes
0
answers
131
views
Which is quicker in SSIS: uploading to a database via SP with TVP param, or using an OLE DB Destination?
I'm importing a (relatively) malformed CSV into SQL Server via SSIS. So far my workflow is:
Import CSV as one column
Use script component to clean up into 113 properly formed columns
Now I have a ...
- 2,278
2
votes
1
answer
2k
views
How do SSIS connection strings send passwords?
I have a SQL server that pulls data from another SQL server via SSIS. We recently converted the server targeted by SSIS to VM. Since the VM conversion I have been getting "login failed for user '(...
- 75
2
votes
1
answer
333
views
Backup on an empty SSAS database hangs
I created an SSIS package to iterate through all databases on an SSAS instance. It grabs the DatabaseId using AMO in a vbscript, then generates the XMLA to backup each database.
Most databases ...
- 721
2
votes
0
answers
94
views
SQL Server - Holistic approach to managing permissions from a domain [closed]
We are small IT department and we are changing how our infrastructure works. We are trying to make it more secure, manageable and transparent for auditing.
We are implementing a domain for our servers ...
- 141
1
vote
1
answer
7k
views
How to change "deployment target version" on SSIS 2019?
I am experiencing the following errors when using the "Transfer SQL Server Objects" task in SSIS on Visual Studio 2019:
An exception occurred in SMO
Value cannot be null. Parameter name: server
...
- 177