Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
258
questions with no upvoted or accepted answers
5
votes
2
answers
5k
views
Issues running SSIS package as SQL Server Agent job
I've got some ridiculously stubborn difficulties when trying to run SSIS packages SQL Server Agent jobs on two SQL Servers, one in a domain and one without. I've read hundreds of posts on dozens of ...
- 333
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
0
answers
2k
views
SSIS Package failing to access UNC path it has permissions to
Trying to run an SSIS package that tries to access \\fileserver\share\file.txt, but the SSIS job returns:
"File System Task:ERROR: File or directory "\\fileserver\share\file.txt" represented by ...
- 41
4
votes
1
answer
2k
views
22046 "Impersonation error" running SQL Server Agent job
(New to SSIS. None of the similar questions mention this particular impersonation error.)
I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. ...
- 181
4
votes
0
answers
1k
views
Is there any way to set the default data source for the SQL Server Import and Export Wizard?
When I launch the SQL Server Import and Export Wizard for SQL Server 2014, the selection in the Data source drop-down defaults to .Net Framework Data Provider for ODBC. This happens regardless of ...
- 179
4
votes
1
answer
8k
views
SSIS Data Transfer vs. Linked Server
I'm currently designing a SSIS package, which will do following:
Clear unwanted records in destination
Do ETL using stored procedures and put everything into a table prepared for export in source
...
- 1,020
3
votes
0
answers
42
views
Automatically pass through Sort and Merge Join transforms without needing to manually tick lots of boxes
We have many SSIS packages that sort incoming data, and merge-join to other tables from other systems.
Some of these packages may have 10 or so joins (each preceded by a sort on the join fields).
In ...
- 156
3
votes
0
answers
676
views
Import Oracle unicode data with OLE DB Source and destination in SSIS 2015
My issue is identical to this post on mssqltips.com, however my issue is importing from Oracle, not Excel.
I get all the way to the bottom where I need to map the output columns from my data ...
- 1,093
3
votes
0
answers
46
views
Migrating Access Saved Import Specifications to SSIS Package
We currently receive a multi-hundred column flat file from a vendor, and the tech prior to me had created an import specification in Microsoft Access to automatically split the file into the right ...
- 308
3
votes
0
answers
1k
views
Specified initialization vector (IV) does not match the block size for this algorithm
I built an SSIS project on "test" server, and deployed the project across to "live" server. The project has property ProtectionLevel set to EncryptSensitiveWithPassword. Everything deployed ...
- 382
3
votes
1
answer
2k
views
Can't deploy SSIS package - The certificate chain was issued by an authority that is not trusted
I have an Azure Windows Server in a managed Domain (AD Domain Services) running SQL Server Enterprise.
I am trying to simply deploy a local (laptop) SSIS project to the remote SSIS server. When the ...
- 131
3
votes
0
answers
5k
views
Connecting to SQL Server from within an SSIS script task
In SSIS, I have defined a Native OLE DB\SQL Server Native Client 11.0 connection that I've calledMyConnection.
Then I've created a data flow task, and within the Data Flow of that task there is:
A ...
- 2,278
3
votes
0
answers
1k
views
SSISDB Retention Period
I have looked into articles to get the size of SSISDB down. One good way to do this was set the retention window for logging lower, currently down to 150 from the default of 365 days.
I would like ...
- 497
3
votes
0
answers
2k
views
SSIS ASCII requirement: "Code page is 1252 and is required to be 20127"
I have a requirement to export a database to a tab-delimited file in the ASCII format. I am using derived columns to convert any Unicode strings to non-Unicode strings. For example, a former Unicode ...
- 31
3
votes
0
answers
6k
views
Why does an SSIS package fail when executed from the IS Catalog?
When executed via the SQL Server Agent under the same user context, is there any reason a package would successfully run when using the file system (i.e. DTExec.exe), but fail to execute when launched ...
- 3,700
3
votes
0
answers
2k
views
SSIS Recordset Destination error
I have an SSIS package targeted at SQL Server 2012.
I have it deployed into the Stored Packages in Integration Services and then have a SQL Server Agent job which executes it.
The first part of the ...
- 161
3
votes
0
answers
193
views
How to use a foreach loop with project deployment model
I have a SSIS project that is configured to project deployment model, within this I have two connections one for a flat file and one for a sql db. Both connections are in project mode. Now I want to ...
- 2,857
2
votes
0
answers
263
views
SSIS: How to export more than 4000 characters to Excel
I have the following basic setup in SQL 2008 R2. This does not work however as I always get the errors
Error 1 Validation error. SQL to XLS: SQL to XLS: Columns "Original" and "...
- 194
2
votes
0
answers
61
views
Migrate Mysql to Azure Sqlserver db
I read some articles and also tried SSMA, linked server and other things, but not help. I have one client's mysql Db (later we need other too) in my local pc and have Azure DB which used for SAAS-...
- 121
2
votes
0
answers
814
views
Why does DTExecUI.exe run fine but DTExec.exe gives me an error?
When I try to execute an SSIS package with DTExec.exe with a batch file or in the command prompt it gives me error code 0xC0040019 Data Flow Task 1 (SSIS.Pipeline) data flow objects cannot be loaded. ...
- 21
2
votes
1
answer
354
views
Installing SSIS package outside of SSIDB catalog
I'm trying to simplify deployment of a SSIS package to a remote server. At the moment, I have
Local Visual Studio SSDT and SSIS package that has 4 connection managers - DEV environment
Remote server ...
- 143
2
votes
1
answer
2k
views
SSIS - Setup OLEDB Connection to SQL Server Always On Listener
I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017).
I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I ...
- 523
2
votes
0
answers
116
views
Create/Edit SSIS Derived Column Task From Text/Code for Large ETL
I have to insert a large UTF-8 encoded flat file to a 1252 encoded sql server. In order to do so, I am using SSIS to type cast each column. Is there a way, other than copy pasting through all 400 ...
- 23
2
votes
0
answers
206
views
Paralleled SSIS package flatlining at 20% CPU
I'm a DBA and my developers are wanting to aggregate data as quickly as possible. They have run the 3 main executions within SSIS to run in parallel but rather than make the CPU hurt for a shorter ...
- 95
2
votes
1
answer
428
views
SSIS comparison load between new, updated and deleted records and populate a staging database
So far,
My control flow looks like:
Execute SQL Task
- This executes a statement that creates a staging table. If one exists, it is dropped so everytime the package is run, the staging table will be ...
- 21
2
votes
0
answers
1k
views
How do I do an ODBC connection to ORACLE from SSIS/ Visual studio?
Despite that OLE Db and ADO.NET are newer than ODBC potentially, I've heard that ODBC connections to Oracle are potentially faster within SSIS.
So --- I'm trying to set up a simple ODBC connection to ...
- 1,613
2
votes
0
answers
76
views
SSIS - dynamically merging a group of files
I'm trying to create an import package for our new mssql database and I'm having to merge a group of 20 files together to be fed into a table on the database. However not all of those 20 files will be ...
- 143
2
votes
0
answers
753
views
SSIS Script Component Not Logging
How can I log from within an SSIS Script Component?
I am using the below code, which I believe should work.
public override void PreExecute()
{
this.Log("Entering PreExecute()", 0, new byte[0]);
...
- 121
2
votes
0
answers
2k
views
Attempt at SSIS Execution Throws Invalid object name 'SSISDB.catalog.customized_logging_levels'
I can run the SSIS package in SSMS 2014 if I set the build to SQL Server 2012, but attempting to run in SSMS 2017 v17.4 upgraded from 17.3x (didn't work there either) SQL Server version 13.0.4451.0 ...
- 121
2
votes
0
answers
441
views
SSIS log subcomponent name
I want to log information using a 2016 SSIS C# (2015) script task that will be easily findable in my SSIS reports I have built. My plan is to use the IDTSComponentEvents.FireInformation method, and ...
- 193
2
votes
0
answers
471
views
What SSIS variable/ paramter to use to capture looping server name?
I am a newbie to SSIS.
BACKGROUND:- We are trying to make Database Inventory of about 100 SQL Instances. First task is to check SQL connectivity for all servers and capture the failed connections. ...
2
votes
0
answers
270
views
SSIS multiple parallel ETL: Task Scheduler or Service Broker
We have one SQL Server 2008 on which we run several ETL packages in parallel.
This setup has always suffered stability issues.
Since I have taken over I am trying to find a way to increase stability ...
- 173
2
votes
0
answers
73
views
SSIS - Import and Export wizard - Dataflow OLE Destination create not executed before injecting data?
I've created an SSIS package using the Import and Export Wizard using SSDT to generate the package.
After analyzing this package I see that it consists of a SQL Execution Task to create the table and ...
- 45
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
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
0
answers
70
views
Visual studio 2015 closed when re-run SSIS pack
I recently installed visual studio 2015 and there is a really simple action that I can not make,
When I rerun (Ctr+Shift+F5) SSIS package (re-run, not run), it causes visual studio to throw this error:...
- 219
2
votes
0
answers
674
views
SSIS 14 - Buffer Memory - Good practices
I've a some packages in SSIS 14 in order to extract the data from a lot of operational databases and load into my Data Warehouse.
I've the following virtual machine:
Windows Server R2 Standard
RAM:...
- 123
2
votes
0
answers
3k
views
SSIS Script Component as Source AddRow Null Exception
I have a SSIS package with single Dataflow Task. The first step in this Dataflow is a Script component setup as a source, the default 'Output 0' has been renamed to 'Out' and it has a single column '...
- 943
2
votes
0
answers
72
views
Controlling or deugging validation behavior of SSIS packages
After investigating a production issue, I have found a number of references to SSIS packages getting stuck in 'validation' when run with DTEXEC or through as SQL Agent job steps.
Specifics of my ...
- 1,297
2
votes
0
answers
541
views
Export SQL View to CSV flat file. SSIS job stuck
I created a database view with the desired data and format. I used SQL Server Import and Export wizard to create a flat file export procedure and saved it to SSIS package.
The data exports correctly ...
- 153
2
votes
0
answers
2k
views
SSIS or SQL Agent weird error
We are like SSIS heavy shop and there are more than 30+ packages that runs in that 24 hr cycle. However, lately every few other weeks we are noticing that the packages fail to execute and throws out ...
- 507
2
votes
2
answers
502
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
"Login failed for user..." with cross-instance access on same server in SSIS package
I have 2 SQL Server instances running on the same server.
Lets call the instances Instance_A and Instance_B and the server Svr0045.
Both server use the same service accounts (a service account is ...
2
votes
1
answer
507
views
Split SSIS project in to multiple files to avoid merge hell
Since SSIS does not like being merged nicely I was wondering how to have a big SSIS package split up. With the idea of having multiple devs working on this simultaneously, and a minimal chance of ...
- 129
2
votes
0
answers
65
views
Insert records from output 1 to every record of output 2 from the same XML file in SSIS
I am using an XML document as a source in SSIS as below
<?xml version="1.0" encoding="utf-8"?>
<Doc>
<Header>
<ShipmentCode>ABC-EFG-123</ShipmentCode>
<...
2
votes
0
answers
699
views
SSIS parameter crashes Visual Studio
I am experiencing a weird behavior with SSIS parameters.
At the start of the SSIS package, I have a script task. In the Script Task Editor I select a package parameter as a read only variable.
When ...
- 179
2
votes
0
answers
24
views
Transformations and source info for data ditionary
I am currently building our data dictionary for our data warehouse, but two things we would like to add is the source for the fields, and the transformations that were applied to them. Is there a way ...
- 21
2
votes
0
answers
245
views
Image Data Type Export Different with BCP vs SSIS
I have a Stored Procedure process(scheduled SQL Job) created using BCP to export PDF file data from a image datatype to a PDF file on a network share.
Due to a client request, I'm wanting to ...
- 21
2
votes
0
answers
127
views
Retrieve data from OLTP MySQL to SQL Server DWH using SSIS
I'm running a BI solution currently reading data from an OLTP SQL Server 2008 to a SQL Server 2012 data warehouse.
The ETL phase is implemented with SSIS and runs twice a day. This phase relies on a ...
- 21
2
votes
0
answers
29
views
How to retrieve the distinct domains of a compound domain from DQS using SSIS?
MS Data Quality Services (DQS) has a concept of Compound Domains, which lets you turn say "'99 Chevy Maliboo" into {year:1999, brand:Chevrolet, model:Malibu}
SSIS has a "DQS Cleansing" component. It ...
- 8,211