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

SSIS - Excel destination does not allow characters more than 255

My SSIS package has a ado.net source, which returns columns with width more than 2000 characters,but those columns get truncated when redirected output to excel destination. Any idea, how to resolve ...
1
vote
0answers
74 views

Cross Server and Instance Login SSIS SID or ID Issues

I have some challenging login/SID/ID issues with authentication between databases on separate instances and servers. We have a Development, Test, Pre-Production and Production server/instance ...
3
votes
1answer
745 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 ...
0
votes
1answer
152 views

How can I restore ##MS_SSISServerCleanupJobLogin##?

At some point the SSIS cleanup job login (##MS_SSISServerCleanupJobLogin##) got deleted from my sql server (2012). I'm trying to upgrade to SP2 and I'm getting an error message I believe is related to ...
2
votes
0answers
1k views

Create SSIS package to copy data between SQL Server 2008 installs on different machines?

I'm new to the whole SSIS world. I'm trying to create a process to copy data between SQL Server 2008 environments. I want to be able to run a job to export data from my prod database and then use that ...
1
vote
2answers
75 views

best way to deal with imperfect xls source file that needs to be imported into SQL via SSIS

The data is formatted like this: Date Type Account 1/1/14 AAA 111 112 113 BBB 114 115 ...
1
vote
0answers
54 views

Will redeploying a project to the SSISDB invalidate current jobs

I’ve been given 2 dtsx packages to replace 2 in production. This is for a SQL 2012 server with the packages deployed in the project model. The only way I see to do the update is to export the project, ...
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 ...
1
vote
0answers
121 views

Errors in the metadata manager. The process type specified for the Effect dimension is not valid since it is not processed

Trying to hunt down an issue with an SSIS package we have and I don't know what to look for to help correct this issue. Error: Errors in the metadata manager. The process type specified for the ...
0
votes
0answers
34 views

Got error with lookup transformation in sql server 2005 BIDS

I have query to seperate the current and history DB's ** select , year(doj) from source where year(doj)<=(year(getdate())-3)* Here the moved data(<=2011) from current DB should be deleted and ...
2
votes
3answers
411 views

Looking for a utility to move data between databases (prod to dev) SQL Server 2012 [closed]

I am looking for an easy way to copy table data from a production SQL Server 2012 machine to a development machine (same version, level). We currently just do an export from within SSMS, but we have a ...
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 ...
1
vote
0answers
92 views

SSIS - the 1st item in the data flow package view always imports way slower than the others

In the SSIS data flow task, I've been importing CSV files to my SQL Server via Flat File Source -> OLE DB Destination. When I'm setting things up in the package design preview, if I have just one ...
2
votes
2answers
376 views

Importing different file types to SQL Server

I'm looking for direction here. I need to automate a process that will import several different file types into SQL Server. They are all text based files (no excel) but the internal format of the ...
2
votes
0answers
194 views

SSIS Bulk Insert Task Operating system error code 1311

I have been running SSIS 2008 R2 Packages for a long time and when ever my Packages trough SQL Server Job accesses FTP Server which is on the same domain it uses correct credentials ,e.g. ...
8
votes
1answer
1k views

SSIS 2012 Create environment variable fails

I'm working a script to port an Environment from one server to another. I am running into an issue calling catalog.create_environment_variable wherein I get the error "The data type of the input value ...
0
votes
0answers
140 views

How to fix type conversion error in Data Flow task?

I have an SSIS job with a data flow task having an OLEDB source and OLEDB destination. I am moving almost 40,000 rows from one DB to another DB. In my SQL query**(Source**) , i have 10 columns all ...
1
vote
0answers
106 views

How to copy a subset of SQL objects across databases within a transaction?

We already have a .dtsx package to copy a subset of tables (about 20) from one database to another (at least once in every month). It is using "Transfer SQL Server Objects Task". However this does not ...
1
vote
2answers
1k views

SSIS error Property BackupDirectory is not available for Settings

I am trying to edit the SSIS Back Up Database Task And it gives the folowing error Property BackupDirectory is not available for Settings 'Microsoft.SqlServer.Management.Smo.Settings'. This ...
0
votes
1answer
451 views

How to Implement a Retry Feature in SSIS 2012 When a Database Connection Fails?

We have an SSIS 2012 package that supports our internal analytics reporting database by extracting data from an external database server. Last night, for the first time, the package failed due to a ...
1
vote
1answer
66 views

How to deal with multiple error columns in data flow

I'm writing a simple package to load data into tables and a I have multiple destinations within the same package. I'm attempting to first attempt batch inserts but want to fall back to row by row in ...
0
votes
0answers
681 views

ssis ado.net destination has failed to acquire the connection

I am Transferring data from sql server source db to sql server destination db (cross db). but some times getting error "ssis ado.net destination has failed to acquire the connection.The connection may ...
0
votes
0answers
78 views

Create replication sql server for DWH [closed]

I have a DWH and I need to make an update once a day from STG Database to DWH I need use replication in SQL SERVER, How I do this?
1
vote
1answer
185 views

How to reinstall SQL Server Integration Services I've deinstalled from a Production server by mistake

In deinstalling a named instance of SQL Server 2012 from a Production server, inadvertantly, I selected SSIS as a product to deinstall. Now, I need to reinstall it with the least disruption possible. ...
1
vote
0answers
110 views

What type of fact table / loading solution for a reservation system?

I am designing a Data Warehouse with SQL Server 2012 and SSIS. The source system handles hotel reservations. The reservations are split between two tables, header and header line item. The ...
1
vote
1answer
826 views

SSIS: Get Date for Day of the Week

In SSIS, how do I get the date for a specific day of the week? For example, how do I get last Sunday's date? I assume I have to do some subtraction on the day-of-week, but not sure: DATEADD("d", ...
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 ...
4
votes
1answer
581 views

Deleting Rows is Increasing Index Size

We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table ...
0
votes
0answers
44 views

Merge data using Integration Service

Please Consider this scenario: I have a table in my database. I want move this data in my OLAP database using SSIS.I can move all record from my table to OLAP database.The problem is I don't know how ...
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
2answers
4k 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 ...
1
vote
0answers
285 views

Timeout while using table valued parameter

I'm trying to find the cause for this error while calling a stored proc with a table valued parameter: Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target ...
1
vote
1answer
82 views

Return value formatted similar to if casted as money

My client's system is very outdated, and there is nothing I can do about it. What I'm trying to achieve is to extract values always with at least 2 decimal places. E.g. From 1826.5695600000 ...
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 ...
1
vote
1answer
304 views

Copying Data to Other Database Tables

Should I use SSIS or stored procs to copy data from a number of database tables on one server to database tables on a different server? Each table's structure is identical to the destination server ...
1
vote
0answers
616 views

In the Import/Export Wizard inside SSDT, why is the MSAccess DB Engine data source looking for a workgroup information file?

Running MSSQL12, VS10 Shell, MSA2013 with local .accdb files, Microsoft Access Database Engine 2010 Redistributable (x86_64) and Microsoft Access Database Engine 2007 Redistributable (x86) installed. ...
2
votes
1answer
124 views

SSIS Transaction Level for Single Task

I have 3 tasks in a SSIS package. If the final task fails I only want that task to rollback. Do I just set that task to REQUIRED? Or leave it as SUPPORTED? Thanks!
2
votes
1answer
891 views

Is there a way to use a SQL Server 2000 database in SSIS 2012?

I just got my first chance to install SQL Server 2012 on a test machine. I also installed all of the management and Visual Studios components. I created a copy of one of my SSIS 2008 packages and ...
1
vote
2answers
76 views

SSIS via Import Wizard SQL Server 2008 R2

I have a flat file i need to import into SQL. For a lot of boring reasons, I curently don't have access to use BIDS, only the import wizard in SSMS. The flat file looks sort of like: A 2 B ...
-2
votes
1answer
333 views

SSIS SQL Server 2012 cluster Availibility Group [closed]

We have a three server cluster using AlwaysOn technology, and have created an Availability Group for the SSISDB catalog. How would you change the code to only execute on the primary server?
2
votes
3answers
9k views

Error: “To run a ssis package outside of sql server data tools you must install send successful email of Integration Services or higher.”

Version SSIS 2012. Package was originally 2005 ver. Package on file system. DB Engine / Agent on Host 1, SSIS on Host 2. Execution from Host 2 runs successfully. Execution from Host 1 gives the ...
1
vote
1answer
241 views

SSIS v/s Replication v/s logshipping

I have 2 SQL server instances, on different servers. One is Production instance and gets lots of reads and writes daily. Another one is for external applications to use a copy of few of the tables ...
2
votes
1answer
2k views

ssis convert ddmonyyyy dd/mm/yyyy

Hi guys trying to convert 16JAN2014 --> 16/01/2014. Any ideas??? i'm trying to avoid the horrendous expression of ? JAN : 1 ? FEB : 2 etc. etc. SSMS will quite happily do SELECT MONTH('16JAN2014') ...
0
votes
0answers
232 views

Can an SSIS variable have a default value?

I have to migrate a SQL Server 2000 database to version 2008. A large part of that work is to update all the DTS packages to SSIS. Many of the packages require using the current date to establish ...
2
votes
1answer
4k 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 ...
1
vote
1answer
2k views

How to pass a table of values to a stored procedure from SSIS?

I am really new to SSIS, so I apologize for the "dumb" question. I know open ended "Best Practices" questions are seriously frowned upon, so I will try to be as specific as I can. I have a basic SSIS ...
0
votes
2answers
258 views

how to access my password in SSIS 2005 by VB.NET

A VB.NET component delivered following message: Login failed for user MyDBUser It is about an OleDbConnection. btw The user works fine togehter with SQL Server Management Studio. Retaining a ...
2
votes
3answers
4k views

How to import table's data in MySql from Sql Server?

I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error. Here this error may be ...
4
votes
4answers
2k 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 ...
6
votes
2answers
327 views

Data Flow Task Produces No Rows with Declare Statement

I have a SSIS package that is doing a simple SELECT statement and then loading into a table. I have anonymized the sql statement which should be painfully obvious when you look at the table name. I ...