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.

Filter by
Sorted by
Tagged with
0
votes
1answer
24 views

changing the connection string in SQL Server

we deployed the packages from old server to new server . how to change the connection string i.e initial catalog or server name parameter from Agent job > configuration > connection manger .Is there ...
0
votes
0answers
8 views

SSIS package || Scheduling in SQL Server 2012

We are tring first time to create SSIS package for export the Data from SQL table to Excel. Using this tutorial in SQL Server 2012. Now package has been created. But we are stucked in scheduling it....
0
votes
2answers
349 views

SSIS Create New Folders On The Fly When Writing To A Flat File Connection

I have an SSIS package that is meant to write to multiple files, which are stored like \\server\MainFolder\DynamicField1\DynamicField2\File.csv where the subfolders DynamicField1 and DynamicField2 ...
1
vote
1answer
55 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 ...
3
votes
2answers
64 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
157 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
53 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
42 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
186 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
219 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 ...
1
vote
1answer
595 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
294 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
981 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 ...
1
vote
1answer
277 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
404 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
158 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), ...
3
votes
0answers
244 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
180 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
4k 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
326 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
121 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 ...
2
votes
0answers
440 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
472 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
93 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
910 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
548 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
545 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 ...
3
votes
1answer
530 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... ...
3
votes
2answers
10k 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
621 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
301 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
1k 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
5k 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
222 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
447 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
353 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
522 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
175 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
213 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
746 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
537 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
2k 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
140 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
490 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
3k 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
155 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
vote
0answers
34 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?...