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

3
votes
2answers
50 views

sql server max memory includes SSIS?

I have installed 2 instances of sql server plus SSIS on the following server. Note the amount of RAM is nearly 384 GB And this is the max and min memory settings that I have applied to my 2 ...
1
vote
1answer
35 views

How to restrict access to SQL Server user in some period of time

I have two SQL Server users 'A' and 'B'. I use user 'A' for ETL and 'B' for reporting. My requirements is to restrict user 'B' access while user 'A' performing ETL (Extraction, Transforming and ...
0
votes
2answers
61 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 ...
1
vote
0answers
22 views

Maximum character length for a column if I concatenate all the columns together in SQL Server for SSIS packets

I have a project requirement which extracts data on a daily basis and sent to another data warehouse. Each query has different set of data and number of columns returning are different and also the ...
1
vote
0answers
15 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
28 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)] , ...
1
vote
0answers
10 views

How can I return the full text of a document indexed in SQL Server Full-Text?

I'm using SQL Server 2014 with FileTables to store a large number of documents in different formats. The iFilters are working great, and everything is getting indexed with FTS + Semantic Search. Now ...
2
votes
1answer
22 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
2answers
37 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
29 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 ...
0
votes
1answer
83 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 its showing "Argument "ORARACOCCUAT1.xxxxxxx.COM" for option "connection" is not valid. The command line parameters are ...
1
vote
1answer
64 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 ...
1
vote
1answer
20 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 ...
4
votes
0answers
38 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
24 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
votes
0answers
9 views

SSIS 2005 Truncate Table

when I switch from "delete from" statement to a "truncate table" statement in a transactional sequence container, Data flow remains yellow in execution. any ideas ? thanks
1
vote
1answer
18 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 ...
1
vote
0answers
35 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
36 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 ...
1
vote
1answer
75 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
23 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
36 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
18 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
41 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
40 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
33 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
21 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
9 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
55 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 ...
2
votes
2answers
82 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 ...
8
votes
0answers
196 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
68 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 ...
0
votes
1answer
121 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
52 views

SSIS or msSQL 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
148 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
23 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
146 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. ...
-1
votes
1answer
265 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
39 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" ...
0
votes
0answers
42 views

Tracking row level status of SSIS ETL process

I have an ETL Process in SSIS in which I am loading around 150 tables in each run - Truncate and Insert. I have four packages each from different sources. Each package loads different tables. ...
-1
votes
2answers
80 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? ...
9
votes
1answer
122 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
41 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
92 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
42 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
120 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
64 views

Load Large data set SSIS AWS EBS

We have configuration of SQL 2012 std edition hosted in Amazon cloud that has data disk of 3 TB, Index disk of 3 TB, Log disk of 500 gb all raid 0 stripped. Am trying to load a table that has 8 ...
0
votes
0answers
114 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
75 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
52 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 ...