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

SSIS Multicast performance question

I'm wondering how multicast actually works. So here is my setup. I have one source flat file(csv) of about 100k rows, maybe about 250-300 columns, and I'm multicasting that file into 7 one-to-one ...
4
votes
4answers
1k views

best ETL design to transfer transaction tables records into the data-warehouse

I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table. but for ...
4
votes
1answer
4k views

Convert SQL Server 2012 SSIS package to 2008

I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state. Is there a way to convert the 2012 packages to ...
4
votes
1answer
870 views

What is a “Reporting Task for Subplan” task in SSIS, and how can I create one?

I'm trying to script a daily full backup to a job using an SSIS package. I've inherited several databases from a previous DBA, and I'm trying to emulate the ones he made in order to be consistent. ...
4
votes
1answer
1k views

SSIS Lookup Component Irregularities

I'm seeing odd behaviour in a dataflow with a lookup operation. If I select 'full cache' mode, some portion of the lookups are failing and I have no explanation why. 'No Cache' works correctly, as ...
4
votes
2answers
250 views

SSRS: how to handle unavailability of service when datawarehouse is being rebuilt

We use SSIS to build datawarehouse (full Microsoft environment). This is legacy SSIS package that starts by truncating all tables in datawarehouse, run nightly. During this time, we restrict access ...
4
votes
1answer
2k views

SSIS package blocks itself if uses TRUNCATE

There is an SSIS package with Required transaction on the package level and Supported on the tasks level. The main part of the package is deleting the data and inserting new data: Here the Delete ...
4
votes
2answers
819 views

SQL server agent SSIS error

I get the following error when I execute my package as a SQL server agent job. It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive. I don't ...
4
votes
2answers
395 views

Executing Child packages without using master package

I have many Fact and Dimension packages which are executed using vb script . Today one of my seniors proposed that these packages should be executed individually instead of creating a master package ...
4
votes
2answers
3k 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
2answers
384 views

Integration Services Catalogs - Version Description

In Integration Services Catalogs I can see what versions of a package/project are available and if necessary revert to an older version. On this dialog I see a field "description" however I found no ...
4
votes
1answer
4k views

Importing a large column (8000 chars) from Excel using SSIS

I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor. I'm using SQL 2008 R2 SSIS to import it into a table. The problem connection manager is an OLE DB connection ...
4
votes
2answers
2k 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
0answers
195 views

Can't use Fuzzy Lookup in BIDS

A coworker of mine was having an issue with BIDS. He's doing a simple fuzzy lookup and is only looking against two rows(For Testing Purposes). The weird thing is that when he tries to execute an ...
3
votes
2answers
21k views

Converting Non Unicode string to Unicode string SSIS

I am creating a package where I will be exporting data from a database into an empty excel file. When I added only the source and destination components and I ran the package I got a conversion error ...
3
votes
1answer
626 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
2answers
1k views

How can I change the number of @schedule_uid=N'????' to re-create a SQL Server Agent Job?

I have a SQL Server 2005 job in the SQL-Server Agent Jobs that I need to move/transfer to another updated SQL Server 2008 R2 instance. I was able to script it out and then use it as a new query window ...
3
votes
1answer
356 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
1answer
172 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
486 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
662 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
1answer
1k views

How can I get more information about which lookup failed in an SSIS lookup?

I'm frequently getting errors along the lines of Error: 0xC020901E at Build Test Data, Lookup ID String[120]: Row yielded no match during lookup. To resolve these, I'm going back to the source and ...
3
votes
3answers
1k 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
135 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
743 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
1k views

Licensing standalone instance of SSIS

Basic background: I have an application on an app server (no MSSQL installed) that I want to allow to call an SSIS package. One Solution: One option seems to be that we can install just SSIS on the ...
3
votes
1answer
102 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
2k 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
1k 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
9k views

moving data from one db to another using SSIS

I am very new to SSIS and need to work out how to setup a transfer from table A database A to table B database B. table A contains many more fields than I need in table B, so the process needs SQL ...
3
votes
2answers
15k views

The value could not be converted because of a potential loss of data [closed]

Let me start out by clarifying that this is not a duplicate question, nor a potential duplicate for that matter. I have tried implementing every answer to every single variant that already exists of ...
3
votes
1answer
713 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
353 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
913 views

Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

I am trying to run a number of SQL Server 2008 stored procedures that are wrapped in SSIS packages consisting mainly of Execute SQL Tasks. They run fine in Visual Studio 2008 after deploying to a ...
3
votes
2answers
3k views

Why is this stored procedure for linked server job failing in SQL Server Agent?

I have this stored procedure that runs this code: This piece of code uses the MERGE Statement to update Local Tables form remote tables in an Access Database that is linked by LINKED Server in SQL ...
3
votes
1answer
711 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
16k 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
1answer
1k views

SQL column goes from first to last position in output

I have an SQL query inside a SQL Server Integration Services (SSIS) package where the preview shows the column order correctly, however the actual output uses a different column order. This is ...
3
votes
1answer
1k 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
163 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
5k 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
1answer
83 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
420 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
741 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
389 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
288 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
866 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
1k 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 ...
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 ...