Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [ssis-2012]

SSIS 2012 is Microsoft SQL Server Integration Services 2012, and is part of SQL Server 2012. 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.

3
votes
2answers
24 views

dtexec returns immediately without any messages

I'm trying to setup a command-line call to dtexec to validate packages, using the project model. I'm calling dtexec like this: dtexec /val /proj my.ispac /pack my.dtsx /rep v Though the real ...
1
vote
1answer
35 views

Fail SSIS “Execute Process Task” if false is returned

I have an "Execute Process Task" that calls Powershell and checks if a folder is empty. I'm wanting the SSIS job to fail if $false is returned. Here is the code param ([string] $FolderLocation) $...
1
vote
2answers
41 views

Reverse SQL Server Listener?

The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to. We also have SSIS on the secondary replica of the HA ...
1
vote
0answers
24 views

SSIS: Control Flow Task Skipped

I'm writing an SSIS package that is supposed to import flat files into a database then move them to another folder when done. The flat files are in a bunch of sub folders so I'm using a Foreach Loop ...
5
votes
1answer
137 views

“SQL command” vs “Table or view” when importing entire tables

When using an OLEDB source editor, is there a performance benefit or difference in using "Sql Command" over "Table or View" when you want the entire table? This is a variation on this question, in ...
1
vote
0answers
142 views

Data Flow Task runs successfuly but wrong rows are updated

I created a simple Data Flow Task, that has two Data Flow components (I'm new to SSIS, so the follwoing may be trivial): OLE DB Source - it simply connects to some table on some database Script ...
0
votes
1answer
244 views

SQL Server 2012 Import Wizard appending new data only

I am using SQL Server 2012's Import wizard to import data from DatabaseA to DatabaseB. Source: DatabaseA table A Destination: DatabaseB table B I have written a source query that does the job fine, ...
0
votes
0answers
215 views

SSIS package Job problem

I have an SSIS package that loads data nightly from a warehouse to a database. Both the SSIS and SQL Server agents are on SQL server 2012 Enterprise and Windows Server 2012 R2 Standard. When I run ...
0
votes
0answers
161 views

SSIS Error on cryptographic error

I have upgraded a Package from VS2005 to VS2008 to VS15, now the package contains export and then i am using a FTP task to load the file to a location from export location. Now whenever I am ...
0
votes
0answers
701 views

SQL Server Agent job fails to run SSIS package, but the package runs fine manually

SQL Server Agent job fails to run SSIS package, but the package runs fine manually The SQL server is SQL Server 2012. The job has been working for a long time fine. This is the first time when it ...
0
votes
0answers
261 views

Writing Date as text in Excel using SSIS

I am exporting data to an Excel sheet using SSIS using an SQL Command SELECT CONVERT(VARCHAR(10), CAST(COALESCE(DateJN, DateNM)as date), 101) AS [DateJJ], CAST(T1 AS NVARCHAR) AS [T1], ...
1
vote
1answer
184 views

CDC Capture Instance or State to ensure referential integrity

I'm implementing an SSIS/CDC based data warehouse solution and have a question relating to referential integrity on the destination database. As an example, I have INVENTORY_TRANSACTION in one ...
2
votes
1answer
324 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. ...
3
votes
1answer
105 views

SSIS Excel export changes # to

I am exporting an excel file through SSIS using excel connection manager. I am using SSIS 2008 version and the excel is .xlsx CREATE TABLE `Sheet1` ( `Date` DATETIME, `NMMO` NVARCHAR(30), ...
1
vote
0answers
204 views

What is the difference between ssisdb.catalog.executions.status values of 7 and 9?

7 means package succeeded. 9 means package completed. What is the difference and under what circumstances does 9 occur?
1
vote
0answers
111 views

flat file datetime issue

I have a flat file with this datetime format 5/24/2017 05:12:44.243 PM. I have been struggling to convert this format to something like this 2017-05-24 17:12:44.243. I tried using this derived column ...
3
votes
1answer
3k views

SSIS flat file import - Issue with datetime format

I have a flat file with datetime format "5/19/2017 05:22:23.777 PM", and when I try to insert this data into a table using SSIS transformation I am getting these errors: [Flat File Source [2]] ...
1
vote
1answer
272 views

Is it normal that a data flow task's OnError event handler fires 3x and how to avoid that?

In an SSIS package there are event handlers on every data flow tasks which send an email with a brief description of the error. I fail the first task by introducing an error condition and get 3 emails....
3
votes
2answers
111 views

Optimize SSIS package for fewer queries

I need to associate customers' order with their "level" (Silver, Gold, etc.) when they placed the order: CRM server::CRM db::CRM table ---------- CustomerID PreviousLevel NewLevel ...
3
votes
0answers
328 views

Why does it appear as though SSIS is not reading a connection string properly?

I am executing an SSIS package on a db server and receiving an error. I can execute it just fine on my local machine but when I try to execute it via a SQL Job I get the following: Executed as user: &...
0
votes
1answer
407 views

I am facing issue while decryption (Gives NULL value), While export/Import my Colum level encrypted data

I am facing issue while decryption, Please help me if my column encryption/decryption process is wrong. I encrypted table in Source DB and back up certificate, then exported my encrypted table result ...
5
votes
1answer
87 views

Any Tricks to Better Identify Disabled SSIS Elements?

Does anyone have any tricks they care to share that will help better identify disabled elements within SSIS packages? Ideally there's some manner to adjust the text color to red or or something along ...
2
votes
0answers
773 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 ...
5
votes
1answer
427 views

SSIS changes SQL Server source data type from date to WSTR

I want to work with a table from SQL Server in an SSIS data flow. One of the columns of the table is date type. When I import it to SSIS using OLE DB source component, SSIS changes the metadata of ...
0
votes
1answer
473 views

Run an SSIS package located on a linked server from a stored procedure

I am creating a stored procedure on my First machine named Server1 and would like to call an SSIS package named mypackage.dtsx located on my Second machine named Server2 Noting that I already ...
4
votes
1answer
377 views

'Connection not found' error thrown at end of task, after the package has successfully used the connection it refers to as missing

Sorry for the long title and this long question! I'll set the scene. I'm fairly new to VS and SSIS. I have created several SSIS packages, basically a package for retrieving data and creating a csv ...
6
votes
3answers
1k views

SSIS 2012 VS_ISBROKEN on Derived Column

I have converted a SSIS package built in VS2008 to open in VS2010. I have a Derived Column component that fails validation. It works fine in VS2008. Here is the Expression used in Derived Column... ...
2
votes
2answers
8k 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 ...
0
votes
1answer
520 views

Integration Services 'All Execution Reports' Loads Forever

I'm using SQL Server 2012 to run my Integration Services (SSIS) package by deploying it to the SSIS catalog. Then an error happened in my packages, so I went to check what it is by opening: Reports |...
1
vote
0answers
236 views

SSIS Package Failing Login Failed

Context: User foo has access to both the server its running on and the server its targeting with the package. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "...
0
votes
1answer
935 views

SSIS processing only 5000 rows from csv file to sql table

I am loading data from csv file to my sql table through SSIS. Is there any default limit specified on number of records to be read from csv file ? While loading the csv file, my data flow component ...
2
votes
1answer
4k 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
votes
1answer
130 views

Maintain Foreign Keys using Import/Export Wizard when creating a SSIS package

I´m trying to create a SSIS package in SQL Server Management Studio using the Import/Export Wizard. I´m trying to copy the tables from an existing db to another one, but I´m getting errors because of ...
0
votes
1answer
360 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. ...
0
votes
1answer
269 views

ssis control flow loop

I need an SSIS package that loops back to the initial execute task after the wait for delay block is executed. here is the logic for the below package: execute SQL task executes a set of commands and ...
2
votes
0answers
471 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
152 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 ...
1
vote
1answer
176 views

Transfer audit data using SSIS

I want to transfer my audit data from an audit file to a table using SSIS in SQL Server 2012. I don`t have experience with SSIS, so I am asking for your help. I have already created a table to store ...
0
votes
1answer
482 views

SSIS 2012 Project Connection Manager does not expose ServerName property for configuration in SSMS 2012

We have deployed one SSIS 2012 project with two ADO.Net Project Connection Managers. (There are five packages within the project.) The project has a related Environment, with a variable containing a ...
0
votes
1answer
460 views

SSIS slow performance to insert short rows

(Edit: I changed the post after some more explicative tests) I'm experiencing a lack of performance between a SSIS server (name: Server1) and MSSQL server (name: 7551) when my ETL inserts rows of ...
2
votes
0answers
1k views

Validation Errors in SSIS Package when columns are mapped correctly

I have a SSIS package that I am working on. As soon as I open the SSIS package I am given the following error: Validation error. Populate Organisations Table: Organisation Table destination: '...
1
vote
1answer
127 views

SQL Server Integration Services 2012 - Legacy deployment

I'm currently reading a lot about SSIS and I have read that there are now two models for deploying a solution : the one using "Projects" and the one using "Packages". Starting SQL Server 2012, a ...
2
votes
0answers
26 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 ...
0
votes
1answer
436 views

data validation Datawarehouse ETL

Need a suggestion from Datawarehouse and ETL experts. I have a project which has bunch of fact and dimension table, and we wire the facts using surrogate keys to dimension (religiously following ...
0
votes
0answers
2k views

Failed to execute IS server package because of error 0x80131904 … System.ComponentModel.Win32Exception: The handle is invalid

I have an SSIS package that is executed every 30 seconds by a SQL Agent Job. Every few days, an execution will fail with this message in the Job History: Failed to execute IS server package because ...
3
votes
1answer
130 views

Changing database name based on project environment

I have three environments for our BI project (DEV, QA, PROD) and two databases in each environment, source(EDB) and target(EDM) DEV environment: Source = EDB_DEV Target = EDM_DEV QA environment: ...
-1
votes
0answers
190 views

How to add SFTP task in toolbar in SSDT 2012

I want to add SFTP Task same like given in https://ssissftp.codeplex.com/documentation link. I tried the same steps and then refresh the toolbar but couldn't see SFTP task. I executed PowerShell ...
1
vote
0answers
33 views

Default Binding and Default Schema is coming blank in local db, after running SSIS package

I am trying to copy database from SQL server 2012 which is hosted on AWS, to my local SQL server 2012 Database, I had tried couple of ways to perform this task, keep on getting similar issue with all ...
2
votes
2answers
4k views

SSIS ODBC: Mapping result set columns to variables returns error “Value does not fall within expected range”

In SSIS 2012, I'm using an "Execute SQL Task" to call a stored procedure that returns a single row with two columns. I'd like to map that result set to two SSIS user variables. Should be easy, right?...
3
votes
2answers
2k views

SSIS - How to parse extensions from path-filename strings

I'm importing a large set (126M rows) of file data from CSVs. Import works fine, except I'm trying to derive a column as I import rather than trying to script it out afterward (due to the number of ...