Questions tagged [ssis]

SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.

238 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
1answer
626 views

SSIS Package Not Being Deployed

I have an issue with an SSIS package, that when deployed via VS2017 to the integration services catalog is not actually being deployed. This is an individual package deployment, and not a project ...
5
votes
2answers
4k 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 ...
4
votes
0answers
432 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 ...
4
votes
0answers
1k 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 ...
4
votes
1answer
829 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. ...
4
votes
0answers
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 ...
3
votes
0answers
546 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 ...
3
votes
0answers
40 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 ...
3
votes
0answers
898 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 ...
3
votes
1answer
1k 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 ...
3
votes
0answers
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 ...
3
votes
0answers
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 ...
3
votes
0answers
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
votes
0answers
1k 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 ...
3
votes
0answers
191 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 ...
3
votes
0answers
1k views

Microsoft Visual Studio is Busy

I am working in Visual Studio 2008 on a SSIS package. It is really frustrating that I get "Microsoft Visual Studio is Busy" message quite often. It almost happens every time I try to open a data ...
3
votes
1answer
7k 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 ...
2
votes
0answers
44 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-...
2
votes
0answers
194 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. ...
2
votes
1answer
485 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 ...
2
votes
0answers
61 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 ...
2
votes
0answers
93 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 ...
2
votes
1answer
251 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 ...
2
votes
0answers
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 ...
2
votes
0answers
57 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 ...
2
votes
0answers
616 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]); ...
2
votes
0answers
1k 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 ...
2
votes
0answers
330 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 ...
2
votes
0answers
365 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
0answers
229 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 ...
2
votes
0answers
70 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 ...
2
votes
0answers
117 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
votes
0answers
720 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
votes
0answers
67 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:...
2
votes
0answers
639 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:...
2
votes
0answers
2k 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 '...
2
votes
0answers
66 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 ...
2
votes
0answers
463 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 ...
2
votes
0answers
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 ...
2
votes
0answers
385 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 ...
2
votes
0answers
59 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
0answers
647 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 ...
2
votes
0answers
22 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 ...
2
votes
0answers
206 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 ...
2
votes
0answers
121 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 ...
2
votes
0answers
28 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 ...
2
votes
0answers
671 views

What can cause an "SCD is not compatible with this version of the DataFlow" error?

I created an SSIS package which includes several SCD data flows. This works fine in Visual Studio SSDT, but when scheduled (using SQL Agent), it fails, with this error repeated for each SCD: Code: ...
2
votes
0answers
830 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 ...
2
votes
0answers
225 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
2
votes
0answers
205 views

incremental update in HBase using sqoop

i am working on a POC where i need to implement same like SSIS for sql to hadoop. out DW are in SQL Server and we want to create a HBase at central location where all the data from diff DW will be ...

1
2 3 4 5