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

4
votes
2answers
5k views

SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

I have a package that imports excel data into a SQL table. One of the columns in the table is of "smallint" data type and it allows for nulls. The excel file column that has the data that is suppose ...
4
votes
1answer
122 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 ...
4
votes
2answers
3k views

Splitting a large SQL Server MDF file

I have a large (1.2 terabyte) SQL Server database that I need to migrate to a new server. Most of the database lives on a single, 1.25 TB data file, and a little bit sits on a much-more-manageable 550 ...
4
votes
1answer
3k views

SSIS Row Count: Getting a null variable error where there is clearly a selected variable

Validation error. Build Files Count VIE [245]: The variable "(null)" specified by VariableName property is not a valid variable. Need a valid variable name to write to. From what I can tell, this ...
4
votes
0answers
40 views

Large Variation in Bulk Insert time

So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart. The process is a simple data flow task with default settings for "Rows per batch" and the ...
4
votes
3answers
1k views

Twice job results in sql server, last one is left in progress

I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1. Fist result ...
3
votes
4answers
5k 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
933 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
473 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
9k 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
1answer
206 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
790 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
872 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
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
134 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
1k 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
1answer
664 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.
3
votes
3answers
173 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
175 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
75 views

Don't see it : Integration Services Catalog, and open the [All Executions] report

I have SQL Server 2012 Enterprise Edition (64-bit) on Windows NT 6.2. I am not seeing the option to the "[All Executions] report" in the Integration Services Catalog . How can I solve it? Update: I ...
3
votes
2answers
86 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
315 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
1answer
20 views

Error When Trying to Run MDW Data Collector

I have SQL Server Management Data Warehouse (MDW) set up across multiple instances, all in the same way with the same accounts and permissions. On one server I get an error when trying to run the ...
3
votes
1answer
3k views

How can I resolve SSIS Excel Connection Manager Error 0xC0209303?

I have created an SSIS package that imports an excel file into a SQL Server table. The SSIS package runs without any problem when I run it locally on my machine but when I run it on the server where ...
3
votes
1answer
1k 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
464 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
26k 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
991 views

Update Schema in SSIS Export Package When Source Schema Changes

I have servers with lots of client databases, all with very similar, but not necessarily identical schemae. Some customers want their data in MSAccess on a monthly basis. This was getting tedious to ...
3
votes
1answer
33 views

Unknown Windows SID Mapped to Proxy During SSIS Package Execution

I'm running SSIS packages stored in a SQL Server 2014 catalog via scheduled SSA jobs. I'm reviewing my SQL Audit capture and noting calls to SSISDB are coming from the proxy I created when looking at ...
3
votes
1answer
79 views

SSMS: Import from flat file (.csv)

In SQL Server Management Studio (SSMS) 2008 R2, I wish to import a not-quite-properly formatted CSV into a database table. I was trying to use the wizard at <database name>->Tasks->Import Data. ...
3
votes
1answer
91 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
79 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
216 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
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
176 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
8k 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
346 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
253 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
2answers
5k views

Unable to connect to FTP server using “FTP Connection Manager”

I'm trying to setup an SQL agent job to use my SSIS package. The package contains an FTP task that is failing. I'm getting the following error in both the job history and logging that I've setup (Log ...
3
votes
1answer
9k 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 ...