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

learn more… | top users | synonyms

0
votes
3answers
467 views

SSIS - Best way to insert large number of rows

So here's the scenario: I have an XML file, which is in size of 500GB, and with data of around 600 million rows (once on a database table). I'm using SSIS for the operation, and since it consumes a ...
2
votes
0answers
41 views

BULK INSERT clent and session requirements

I'm setting up an ETL process using SQL Server 2014 and SSIS. My Bulk Insert Task is utilizing a source file and format file on ShareA targeting a DB on InstanceA. The package is run via SSA job on ...
2
votes
1answer
40 views

Non clustered index for non-evenly distributed data in large SQL table

I have the following table containing 2.000.000.000 rows: CREATE TABLE [VeryLargeTable] ( [dataCol1] [NVARCHAR(MAX)] , [dataCol2] [NVARCHAR(MAX)] , [Manufacturer] [NVARCHAR(10)] , ...
2
votes
1answer
43 views

Duration measure in fact table

I have a fact table with the user reservations with StartDate and EndDate. I am thinking what is the proper way of serving the duration queries in the reports. Should I calculate DurationDays and ...
1
vote
3answers
2k views

SSIS: How to check if a record DOESN'T exist in flat file but exists on the database

I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. ...
1
vote
1answer
112 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 ...
1
vote
1answer
2k views

SSIS Job “Argument ”ORARA********.xxxxxxx.COM“ for option ”connection“ is not valid. The command line parameters are invalid. The step failed. ” error

My SSIS package is running fine in BIDS but when I tried to schedule the job it's showing: Argument "ORARACOCCUAT1.xxxxxxx.COM" for option "connection" is not valid. The command line parameters ...
2
votes
1answer
693 views

Most efficient way to insert rows into a temp table in a stored procedure

I am working in SQL Server 2008. I have a stored procedure with 5 queries inside of it that select rows from very large fact tables (on the order of millions of rows) into another table (call it ...
2
votes
1answer
32 views

Are there security vunerabilites/implications with SQL Mail, currently using SQL Server 2008 R2

I am an accidental DBA and responsible for series of data load jobs. I would like to use SQL Mail for altering me to job failures. SQL Mail is not currently turned on are there security concerns in ...
6
votes
1answer
484 views

SQL Server: Unable to save import export package, but am on Standard Edition

I am confused. Issue: SSIS ("SQL Server Import Export Wizard") should let me build a data import package and save it to a file, for easy re-running (converting to a task, etc.). I want to save a ...
1
vote
1answer
157 views

What is the relationship between the SSIS Package Store and Catalog?

I have a server with a working installation of the Sql Server 2012 SSIS Catalog. I need to set up an additional instance including the SSIS Package Store service as an interim step while the packages ...
1
vote
1answer
105 views

SSIS Transaction Scope

My question is in regards to the scope of a transaction when using SSIS transactions. If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence ...
2
votes
0answers
873 views

How can I eliminate false “connection string format is not valid” messages from SSIS log?

I have a project that uses the SSIS catalog with project-level connection managers. The connection managers' connection strings are overridden at the server using a project parameters file, to enable ...
1
vote
1answer
94 views

Simple SSIS Package Failed User does not exist or has not permission

I created and build SSIS Package with "Transfer SQL Server Objects Task". I Copied the package.dtsx to network folder with permissions set to everyone. I created the SSIS Job where i set path ...
2
votes
1answer
449 views

Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)

I try to deploy new version of existing SSIS project into Intergration Service Catalog and getting error: "Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)" ...
1
vote
1answer
92 views

How to export SSIS (SQL Server Integration System) packages to third party data source

I want to export SSIS packages into a HANA databse. Please guide me through the exact steps to achieve this.
2
votes
1answer
591 views

Searching SSIS packages for the use of specific tables

I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.
0
votes
1answer
41 views

SSIS 2008R2 - Use Package Config Value in Expression

Is it possible to add a SQL Server package configuration value for an Expression attribute on, for example, a 'Flat File Connection Manager'?
0
votes
0answers
155 views

SQL Server 2012/SSIS - How to Import Partitioned Table Data to Reporting DB?

What's a recommended SSIS process flow for importing a daily partitioned table to a reporting DB table (SQL Server 2012)? Will be switching partitions on main DB to a staging table each day and ...
2
votes
0answers
178 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 ...
1
vote
0answers
84 views

SSIS - The value could not be converted because of a potential loss of data, runs fine in BIDS, not on the server

The value could not be converted because of a potential loss of data I'm getting this error when running an SSIS package from a server job. Normally this is some sort of type error, but I can't ...
1
vote
0answers
67 views

Is There A Benefit To Using SSIS Packages Over Linked Servers?

We have a new head DBA, and he has decided he does not want us to use linked servers for development moving forward. In addition he would like to replace all existing code that uses linked servers to ...
1
vote
0answers
20 views

How can I prevent SMB digital signing from killing SSIS performance?

My company recently turned on SMB digital signing on the Windows 2012 r2 server that runs SSIS. This has had a severe impact on data transfer performance. The security team did this and I can't get ...
1
vote
2answers
814 views

SSIS Package - Error Exporting Data to Flat File - Invalid Character

I have an SSIS package where I pull data from a 3rd party source into a CSV file. I get this error: "Data conversion failed. The data conversion for column "Description" returned status value 4 and ...
0
votes
1answer
89 views

Post installation of SSIS for existing SQL Server instance with different build versions

I would like to install SSIS into existing SQL Server 2014 Fail-over cluster instance with latest cumulative update 7 which was applied. I know how to do it but I want to know if consistency of ...
2
votes
2answers
834 views

How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package?

I want to pass a parameter (@date1) in my SSIS OLE DB Source so I created a variable and I tried to pass the parameter using '?' but it's showing 'Syntax error, permission violation or other ...
10
votes
1answer
1k views

Avoiding “Row by row” fetch method when dealing with source LOB columns

I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying: 'Row by Row' fetch method is enforced because ...
0
votes
1answer
202 views

SSIS Package Error when Scheduled in Job

I have created a package that brings in data from another database and with the data package populates a table in SQL database. The job runs fine when it is executed. I've imported it into SQL and it ...
1
vote
1answer
3k views

SSIS package hits “login failed for user” when simultaneous operations occur against the same target database

Quick summary We have been working on migrating our library of SSIS packages from SQL Server 2008 hosted on physical Windows Server 2003 to SQL Server 2012 hosted on virtual Windows Server 2012. The ...
0
votes
3answers
184 views

SSIS or SQL Server way of sectioning a flat file

I have a few hundred thousand flat text files which contain a single column of values. The first 40 values are parameters followed by between 4000 - 8000 rows of sensor data. The files are imported ...
3
votes
3answers
172 views

SQL SSIS syntax question

What do you call the following expression? (TableName == NULL) ? "0" : TableName I have a sense of what it does, but would like to look up the whole syntax definition, however as there is no real ...
1
vote
1answer
30 views

SSIS Package Security

I am writing a Package for one of my client according to his requirements. Package development is totally complete now time is to protect it with some security. Is this possible I encrypt all the data ...
0
votes
1answer
443 views

Issuing Inserts/Updates to Progress OpenEdge via ODBC

I'm attempting to build some ETL to integrate our SQL Server environment with a remotely hosted 3rd party app running Progress OpenEdge. I have a linked server to it set up in SQL Server 2014. ...
-3
votes
1answer
1k views

SQL Server Agent Job Error running an SSIS Package

I've set up a SQL Server Agent job to run an SSIS package, but it is failing with an error: This Job failed. The Job was invoked by Schedule 1114. Does anyone know why I'm getting this error? ...
0
votes
0answers
124 views

SSIS OLEDB connections dropping out during long-running data flows

Long-running SSIS Data Flows (OLEDB, which transfer > 4M rows) intermittently fail with error "Protocol Error in TDS Stream" and detail "An existing connection was forcibly closed by the remote host" ...
-1
votes
2answers
273 views

How to format SSIS Expression for weekend

I have the following format to account for weekends while naming an excel output. How do I get only the date and not the full startime formatted like 20150429? ...
10
votes
1answer
316 views

SSDT Schema Compare doesn't work while a BULK INSERT is in progress

I'm working at a large ETL and DW project where we use TFS/source control together with both SSIS and SSDT. Today, I found out that while an SSIS package is performing a BULK INSERT into a database ...
2
votes
0answers
81 views

SQL Agent AD account gets permission denied for ODBC connections in SSIS packages

We have an AD account specifically created for the agent service and when running SSIS packages that connect to databases we get permission denied even though we have granted that account access to ...
1
vote
0answers
236 views

how to disable and enable SSIS package using commandline?

I have a scenario where SSIS packages are deployed at the customer's site and they have some data issues. Because of this we are developing new packages to fix the corrupted data. When my new ...
0
votes
2answers
246 views

Receiving the following error in step failure of an agent job:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x00040EDA Description: "Warning: Null value is ...
2
votes
1answer
623 views

SSIS Packages get stuck in SQL Agent jobs [closed]

We run about a dozen scheduled SQL Agent jobs which run SSIS packages. Intermittently, every scheduled job will get stuck in a running state. i.e. we'll arrive in the morning and notice that there's ...
0
votes
0answers
297 views

Problem running SSIS package remotely using PoSH

For various reasons I'm trying to run an SSIS package remotely using Powershell. I've enabled remote scripts on my server using the following command. Set-PSSessionConfiguration -Name ...
2
votes
0answers
213 views

SSIS: FTP task - concurrent connections

I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job ...
0
votes
1answer
83 views

How to keep the data warehouse synchronised with the source systems? [closed]

I'm a DBA who is responsible for exporting data from an ERP system for Business Intelligence purposes. The data from ERP system is being sent to the data warehouse and they are used in the ETL ...
0
votes
0answers
189 views

Decimal Data Type as Primary Key

One of my tables is set up like this: create table dbo.Source ( testID decimal (10,3) PRIMARY KEY , column 2 somedatatype , column 3 somedatatype ) Whenever I perform SSIS into this table ...
0
votes
1answer
177 views

SSIS Package not retaining parameters in SQL Server agent job

I have an SSIS package stored in the Integration Services catalogs on a SQL server instance. This package is used as a step in a SQL Server agent job. The package has 4 required parameters. Using ...
1
vote
0answers
927 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 ...
-1
votes
2answers
2k views

How to Execute multiple select statements in SSIS 2008 using Data flow task [closed]

I am migrating data from one database to another database using SSIS package and i need to write multiple select statements for same table depending on conditions as mentioned below. Actual Queries i ...
0
votes
2answers
201 views

Executing stored procedures asynchronously using tasks in SSIS

I have a stored procedure which is calling 20-30 stored procedures. I need these stored procedures to run asynchronously. From what I have read, it seems like executing those stored procedures using ...
0
votes
1answer
66 views

Treat database field as a flat file source

We have a partner sending us csv data to a web service of ours that gets sent to a messaging system that is SQL Server based. I am writing data to disk and then importing to another database by using ...