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
1answer
838 views

Logging Questions for SSIS 70-463

I have been looking at sample questions on logging for 70-463 exam. For the below, the answers given do not make sense to me. The first answer given does not even say how to implement logging. I ...
3
votes
1answer
444 views

SQL Server 2008 SP1 to SQL Server 2008 R2 upgrade

One of our project is planning to upgrade from SQL Server 2008 SP1 to SQL Server 2008 R2. I would like to know from this community the suggestions on: Is there any configuration or any change ...
3
votes
3answers
1k views

SQL Analysis Services - SSAS

Do you know what the best practices would be regarding SSAS and having real-time reports. I am not sure if we should be pulling data off our live database or using SSIS to move data periodically to a ...
3
votes
1answer
8k views

Running an SSIS package without the SSIS service installed

On a SQL Server 2005 instance in our DMZ, we have BIDS installed but not the SSIS service. If I have an SSIS package on that server, can I still schedule to run it without SSIS service? I tried ...
3
votes
3answers
904 views

SSIS 2012 metadata not refreshing on re-deploy

I have three systems: DEV, UAT, PROD, all running SQL 2012 Enterprise. I have existing packages that hit objects on these systems. Every time I have to make a change that affects metadata, it's not ...
3
votes
3answers
4k views

Not able to create SSISDB catalog

Getting the error below while trying to create a catalog in sql server 2014 integration services. Any idea what I missed in installation or anywhere else? The catalog backup file 'C:\Program ...
3
votes
1answer
199 views

Why SQL Server installs multiple services for AS and RS, but only one for IS?

When I install multiple SQL Server instances on a same computer, every instance gets its own SQL Server Service for Analysis Services and Reporting Services. But I get only one service for Integration ...
3
votes
2answers
698 views

SQL Server Memory Issues

We have a SQL server that appears to be leaking memory. Memory consumption linearly increases over time until the server is completely out of memory, causing major performance issues. The SQL server ...
3
votes
1answer
814 views

How can I make sure the SQL Server query optimizer uses the exact tables in the query

I have a query running in SQL Server 2008: select count(*) from table1 join table2 on table1.col1 = table2.col2 In the data warehouse where this script runs, a table called thin_table1 is ...
3
votes
2answers
11k views

SSIS SQL Server 2008 errors

I just want to know how to import data from source to SQL Server 2008 database using SSIS. I have a simple txt file (actually it's from this blog : ...
3
votes
2answers
3k views

Table data transfer using SSIS and triggers on destination table

I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using. I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do ...
3
votes
2answers
127 views

What could cause “lagging” in SSIS execute SQL task?

Recently I noticed that the part of my ETL process loading the data into the staging area sometimes takes longer and sometimes shorter. With the following query (executed in the integration services ...
3
votes
3answers
975 views

Downloading files based on most recent date and timestamp

I have a requirement to download zipped files from an SFTP site using C# and Script Task within SSIS. We have multiple files on the server with the same name, but with different date and timestamps. ...
3
votes
3answers
3k views

Fixed length text file insert into SQL table

I am not a DBA on this one and I will not have access to BCP or anything like that. So I'm wondering if there is still a way to do it in SQL keeping it very basic. I have 100's of text files that ...
3
votes
3answers
167 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 ...
3
votes
1answer
1k views

Conditional data flow task in SSIS

Brief Architecture: I am creating a SSIS package to generate Excel reports. There are different types of reports, So I have created a table to track each user request for report generation and ...
3
votes
1answer
142 views

Replace column values?

I recently got into SSIS and haven't been able to find what I am trying to achieve anywhere on the web. I am trying to take some rows as input, and then replace one specific column's value with ...
3
votes
1answer
3k views

Duplicate primary key in bulk insert after truncate in SQL Server 2008?

I'm using SSIS to dump data from my SQL Server 2008 prod database to flat files and then using bulk insert to load those flat files into the SQL Server 2008 dev database. The SSIS package is running a ...
3
votes
1answer
1k views

Simplest way to constrain an SSIS Execute Process Task

We have some complex SSIS packages, currently in SSIS 2005, that use Execute Process Task in order to call out to do some business logic. Some of these called programs are not as stable as one could ...
3
votes
1answer
84 views

SSIS Control Flow: Precedence constraints not working as expected

The control flow of my SSIS package is not behaving as expected. What I want to achieve: Test if CLR is enabled on SQL Server using SELECT value FROM sys.configurations WHERE name = 'clr ...
3
votes
1answer
2k views

How can I search SQL Server for maintenance plans that execute specific tasks?

Sometimes certain SQL Server maintenance plan tasks can be problematic, like Shrink Database, Rebuild Index, Update Statistics, etc. I'd like to search for the use of one or more of these tasks on an ...
3
votes
1answer
2k views

error in Execute SQL Task with parameter

I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that ...
3
votes
2answers
25 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 ...
3
votes
1answer
964 views

SSIS hangs during For Each File enumeration

While debugging an SSIS package that loops over thousands of files, execution hangs with no errors and no failed tasks. Slightly simplified, the package does this: For Each File ...
3
votes
1answer
445 views

VLDB with large quantity of data file imports

I am a little out of my league with this scenario and hope this community can help get me on the right direction. I have recently been tasked with re-designing and implementing a database application ...
3
votes
2answers
23k views

Access denied when trying to run an agent job

Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server. 12/03/2012 ...
3
votes
3answers
4k views

Encrypt/Decrypt output & input for SSIS task

I use SSIS packages to output the data from one system as Comma Separated Values, and then import that same data into a second system. I have a new requirement to encrypt the CSV text files that are ...
3
votes
1answer
1k views

Does surrogate key assignment for a fact table require that the source data has natural keys?

Let's say I have a simple OLTP database with orders, products, and customers: And from it, I am building a data mart with an orders fact table, product dimension, customer dimension, and date ...
3
votes
1answer
65 views

Starting SSIS Package through SSIS Catalog

I've developed an SSIS package to load files but it is behaving very strangely. I can kick off the package running start_execution on the SSISDB only when I log directly onto the server. Kicking it ...
3
votes
1answer
43 views

How to change Zeros ( 0 ) to NULL in SSIS?

I would like to change some Zeros into NULL using SSIS 2008. In that context I tried "conditional split" where I have set the columns test in question with the following expression: ...
3
votes
1answer
122 views

SSIS Repeated Header In CSV File

I am writing to a csv file in a Foreach loop. For each loop the code is writing the header so it appears repeatedly throughout the file. How do I ensure the header only appears once? I have tried ...
3
votes
1answer
9k views

How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure

i am executing query like this on sql server 2008 R2 DECLARE @returncode int EXEC @returncode = xp_cmdshell 'dtexec /f "C:\certs\SSIS Disposition\SSIS Disposition\SSIS Disposition\Package1.dtsx"' ...
3
votes
1answer
5k views

SQL server agent job built on SSIS package not working

I transferred a job from the old SQL Server 2005 to an upgraded SQL Server 2008 R2 instance, and I scripted out everything, changed the data source and other details... "This job should copy some ...
3
votes
1answer
1k views

Inserting a new lookup value for the SSIS Lookup Transformation

This may be obvious, but I've been banging my head for a while... please point me in the direction of an article if there is one. I'm importing a series of Excel files into a SQL database. They will ...
3
votes
1answer
2k views

Export multiple tables among servers

We currently have an SSIS package that exports about 70 large tables nightly, with truncate and load at the destination. Our tables keep changing, we sometimes add some tables and remove some and ...
3
votes
1answer
171 views

SQL Calculation based on 'similar' rows within table

I'm using SQL Server 2008 R2, building a fact table for SSAS. I can therefore accept solutions to this via either SQL query to SSIS task. Let's say I have a fact table that looks a little like this, ...
3
votes
1answer
7k views

Having trouble with Merge Join with SSIS

I am currently using Merge Join to perform an inner join on two sorted sets of data in SSIS. The problem I am having is that no records are being returned from the inner join. To troubleshoot I have ...
3
votes
3answers
186 views

Update Table from SP in SSIS

I know there are similar questions to this one, but I could not find one that answered my question. Inside my current SSIS package... I have a dataflow task which... Takes output from a stored ...
3
votes
1answer
217 views

Metadata Headache

A process that I don't have control over is dropping and re-creating tables in a MySQL database every night. This wouldn't be a problem (I think), if it re-created the tables identically every time. ...
3
votes
1answer
284 views

Apply settings to multiple output columns in SSIS flat file source

Is there an easier way to apply property settings to multiple output columns in a SSIS flat file source, rather than manually applying to each output column? (SQL Server 2014.) I am attempting to ...
3
votes
1answer
565 views

SQL Agent Permissions - Job Level

We currently have an SQL Job that runs an SSIS package. The output of the package is required for a report. As a result, the application that can create the report on demand first tries to run the ...
3
votes
1answer
1k views

Why is the “The package can not be saved to the file system since its protection level is server storage” error?

When I execute dtutil /sourceserver localhost /SQL "\Maintenance Plans\MaintenancePlan_daily" /copy file;.\MaintenancePlan_daily.dtsx from the answer to my question "Any way to ...
3
votes
1answer
441 views

SSIS Design consideration with object variables

I have an SSIS package for SQL Server 2008 R2. In the package I am required to work with an Access database (one to many) and iterate through it running validation checks with the data. The validation ...
3
votes
1answer
360 views

SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?

I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows ...
3
votes
1answer
1k views

SSIS Script to split string into columns

I have a dataset (log file) with a number of columns; one of them is "Other-Data" below (unordered string) and need to parse the string to create the derived columns according the u value (U1, U2, U3, ...
3
votes
1answer
120 views

Permissions Dropped after Package Failed

I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live ...
3
votes
1answer
7k views

SSIS OLE DB Error on package execution

Good day, I have an SSIS package that runs on scheduled job for a while now. all was okay, until very recently, i started receiving some failed job executions. When I investigated it, I found the ...
3
votes
0answers
51 views

How do i Optimize/Improve ram while running a massive import into sql server [duplicate]

I currently am importing a massive database of around 180 million records with roughly 300+ columns. I am sure you are all aware this takes a very long time. I am using sql server on a windows server ...
3
votes
0answers
22 views

Why does my procedure run slowly until I update statistics? [duplicate]

I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the ...
3
votes
0answers
832 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 ...