Questions tagged [ssis-2014]
SSIS 2014 is Microsoft SQL Server Integration Services 2014, and is part of SQL Server 2014. SSIS allows SQL Server to interact with other technologies, including import and export of data to and from other formats, or ETL, and data warehousing. Please add the SSIS tag.
55
questions
0
votes
0
answers
5
views
Download files from multiple FTP folders inside particular files in SSIS
I have an FTP server with a folder named CIV.2-130211067212254633.
Inside this folder, there are files with names like 066075254035232083135067074114053142221015071213.Upload.
...
2
votes
2
answers
455
views
Security Considerations/Risks/Best Practices for Enabling CLR Integration (Assemblies) for SSIS Catalog
The major pre-requisite in utilizing SSIS Catalog is enabling CLR Integration. The instance in question is running SQL Server 2014. I'm looking for feedback and resources I can mine which specifically ...
- 275
0
votes
0
answers
80
views
SERVER_OPERATION_ENCRYPTION_LEVEL missing from SSISDB catalog_properties
I'm using MSSQL 2014 (build 12.0.6439.10, named instance) with SSIS 12.0 and trying to execute a package that I've successfully imported into the SSISDB catalog, but when I attempt to run it via a SQL ...
- 171
0
votes
1
answer
249
views
SSISDB - Certificates expired
I was doing an audit of certificates in our environments to find those that have either expired or are expiring in 30 days. I used the following query:
SELECT *
FROM sys.certificates
WHERE ...
- 1,898
0
votes
1
answer
1k
views
How to make variable update for each instance of for-each loop?
I have a for-each loop that goes through a file directory. These files are transaction information from different clients. When there is an error with a file (i.e. can't be processed), the Event ...
- 101
1
vote
1
answer
2k
views
Unable to start SQL Server Integration Services 12.0 service (SQL Server 2014) after upgrade to SQL Server 2019 and patched to CU4
I upgraded a SQL Server 2014 Standard instance to SQL Server 2019 Standard (also required upgrading the OS from Windows Server 2012 R2 to Windows Server 2019), and then patched to CU4.
During the SQL ...
1
vote
1
answer
614
views
Directory Verification with For Each Loop in SSIS
How can we check if a directory is valid or not inside for each loop . I am having multiple files at some location and i want to load that file but before this i
want to check if the directory is ...
2
votes
1
answer
3k
views
How Do I Execute SSIS Catalog Packages As A Different User With TSQL?
We've been moving application packages from the older methods of storage and execution into the SSIS Catalog. We're required to use an enterprise scheduling system instead of the SQL Server Agent to ...
- 103
2
votes
1
answer
6k
views
The server principal "loginname" is not able to access the database "SSISDB" under the current security context
We have a stored procedure which calls an SSIS package from the SSISdb database.
It works when run as myself using my domain account, however the VB6 application which will call this connects to the ...
- 193
1
vote
1
answer
9k
views
SSIS packages fail with Pending Execution Status
My packages have been running on our production environment for a couple of weeks until they started failing with very little information apart from packages having a pending execution status.
All ...
- 174
1
vote
1
answer
939
views
SSIS convert flat file cell to HH:MM:SS
I see similar questions, but most have to do with datetime or fairly different requirements.
I have a CSV report that has a time column stored as HH:MM[:SS] (if over 1 day, it adds :00 for seconds... ...
- 1,126
-1
votes
1
answer
353
views
SQL Server kicking off multiple SSIS processes for same job
I've seen this bug before and I don't know what made it go away in the past. I have a test server that has three jobs that each kick off their own SSIS package. In production this works fine and ...
- 339
2
votes
1
answer
923
views
Adding Image to excel Export in SSIS
I am exporting excel sheets through SSIS export, and creating excel sheet at run time using execute sql task.
Now the client needs a logo(image) to be attached to the excel sheet on top of data part. ...
- 75
2
votes
0
answers
271
views
SQL Edition Downgrade Impact on Jobs
we’ve made a downgrade from Enterprise Evaluation Edition to Standard and we saw the over night jobs all running much longer these are ETL packages and SQL Stored Procedures to load data from ...
- 41
2
votes
2
answers
53
views
Send environment info to package
At my work we have three different environments: dev, test, and prod. We also quite a few different SSIS packages. Currently, when we need to deploy to a particular environment, we update the ...
- 3,028
0
votes
1
answer
23
views
SSIS DB installaion
I am kind of new to SQL server database. I am using sql server 2014
Can anyone help me to install SSIS DB. Is it mandatory to install to SSISDB
- 1
1
vote
3
answers
2k
views
SSIS Package display MessageBox in Task
I have a SSIS package task that is displaying messageboxes even those that code in the task is commented out when I run it. How do I stop that?
- 247
2
votes
2
answers
549
views
SSIS upsert from CSV File Source
I'm trying to determine the most effect way to do an upsert in SSIS with CSV source 100+ columns
1 x CSV Source (loop through multiple CSVs same structure 100 + columns)
1 x Staging Table Destination
...
- 689
2
votes
1
answer
1k
views
Protecting Virtualized SSIS and SSRS servers
I have a stand alone instance of SSIS and SSRS running on 2 seperate VM's in production. And I want to know if the Servers can be protected with some kind of Snapshot method like Veeam to restore them ...
- 625
1
vote
0
answers
326
views
Dcom Error running a SQL Package
This is the first time I have come across this type of error. I am running a process task and it runs fine until I add it to the scheduleer ( sql agent).
Date 3/2/2017 4:13:05 PM
Log ...
- 11
0
votes
1
answer
345
views
SSIS Issue -- Related to #Temp Table Referencing
I am completely new to SSIS.I am using SQL Server 2014.
I am not able to understand what is wrong with code as it results good in SSMS.
I use OLEDB connection as source assistant and input the below ...
- 1,580
3
votes
1
answer
396
views
Automatic ETL between similar tables
I have 43 tables on 2 different servers which I have to sync regularly. Because of our network policy and IG reasons, we can't have link server object between these two servers. Standard procedure so ...
- 522
5
votes
1
answer
9k
views
Passing NULL to SSIS variables
I'm building a package to execute a procedure with parameters from a configuration table.
I've built two Execute SQL Task boxes. One populates SSIS variables from the configuration table. The ...
- 742
1
vote
0
answers
104
views
"Fire and forget" in SSIS
I need to run pairs of packages from one job. First packages needs to run linearly. Second ones in parallel. I want to use configuration table for those packages, and use SSIS foreach container to run ...
- 742
0
votes
1
answer
979
views
SSIS - XML with row delimiter to a single column of a SQL table
I'm trying to upload data from a CSV file into a SQL server table through SSIS package (Flat File source and OLEDB destination). This CSV has comma as column delimiter and {CR}{LF} as row delimiter. ...
- 11
2
votes
2
answers
338
views
Configuring SSIS,SSRS, and SQL Server - three server model
I have a requirement for a third party vendor application which requires SSIS and SSRS. The vendor had suggested this 3 server model, and my management is going ahead with the same. Three VMs are ...
- 625
1
vote
1
answer
8k
views
How to implement a merge statement in a SSIS package?
we have a process that is called update price list
every season (4 seasons per year) we have new items for sale and there are price variations in many items. we have also many different markets (...
- 15.8k
1
vote
1
answer
2k
views
SSISDB Maintenance job cleaning data very slow on SQL Server 2014
On SQL Server 2014 Microsoft (12.0.2430) SQL Server Enterprise: Core-based Licensing (64-bit) after changing the retention period from 365 to 90 since last 2 days job clean up about 2 GB of data only. ...
- 103
1
vote
1
answer
2k
views
Set an SSIS SQL Agent job to unexpected error or failure
I was running a series of SSIS packages in a SQL Agent job on a virtual server when the server was shutdown - hard.
The package execution report shows it as still running even though I believe it is ...
- 9
1
vote
1
answer
5k
views
SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher
Me and my colleague using the same version of .Net framework (4.5) and installed SSDT in visual studio 2013. He prepared a package (not so complex) and can able to execute the same from SSDT without ...
- 11
0
votes
1
answer
3k
views
SSIS - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
I am trying to use explisit transactions within SSIS. The database I am working with does not have the DTC enabled, so I cannot use SSIS' inbuilt transaction handling.
I have execute sql tasks to ...
- 1,244
3
votes
1
answer
11k
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 ...
- 3,154
4
votes
1
answer
3k
views
Can I create a random number in an SSIS derived column
I have a task where I am trying to assign a random PIN to an account. I have SQL code that will generate a random integer between 1000 and 9999. I was wondering if I could put that in a Derived Column ...
4
votes
1
answer
5k
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 ...
- 180
0
votes
1
answer
439
views
Is it good to avoid Linked Server by utilizing JOIN task under SSIS package?
We are trying to setup a centralized SSIS server (SQL Server 2014). I want to know the following:
Any procedure which is utilizing Linked Server and if that procedure gets called from SSIS SQL Task, ...
- 299
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
1
vote
1
answer
437
views
Timeout in SSIS package running DBCC CHECKDB
I have created an SSIS package to run DBCC CHECKDB on all the databases on the server and send error messages by email.
But for several of the databases, I get an exception running my script:
...
- 143
5
votes
1
answer
16k
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 ...
- 2,877
7
votes
2
answers
5k
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-...
- 71
3
votes
2
answers
1k
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 ...
- 3,138
6
votes
1
answer
352
views
Designing a dimensional DB off a normalized source that already implements SCD's
I have built an SSIS ETL to integrate various data sources (one from MySQL, two from SQL Server) into a single SQL Server relational and normalized database, which I've called [NDS].
The SSIS ETL ...
- 894
2
votes
0
answers
902
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 ...
- 131
1
vote
0
answers
63
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 ...
- 1,434
16
votes
4
answers
81k
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.8k
2
votes
2
answers
10k
views
Data Flow Transform Not Automatically Mapping Columns
I'm in the midst of creating a set of SSIS 2014 packages (using Visual Studio 2013) importing data from 50 tables. Since most of the tables are being filtered on the same column and I'm importing all ...
- 1,913
4
votes
2
answers
6k
views
How to resolve COM server application local activation permission for machine account
I am trying to enable SSIS package logging to event logs. When an attempt is made to write to the event log the following error appears in the system event logs:
The application-specific permission ...
- 81
2
votes
1
answer
195
views
Permissions to be granted to developers to investigate jobs, queries, and packages
I have just installed a new sql server 2014 server, migrated some databases there and connected some applications to them.
These servers have also SSAS and SSIS.
I have a group of developers there ...
- 15.8k
1
vote
1
answer
4k
views
The application is not installed - Visual Studio 2013
I need to deploy packages using visual studio 2013 but I am seen this message, the application is not installed, as you can see on the picture below.
what do I need to install?
- 15.8k
9
votes
2
answers
22k
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.8k
1
vote
1
answer
673
views
When installing SQL Server 2014 SSIS, what are the meaning of "authentication level"?
After installing SSIS 2014, it is the time to deal with the permissions.
When dealing with the DCOM Config (control Panel->Administrative Tools-> Component Services), you want to set up the ...
- 15.8k