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

1
vote
2answers
2k views

SSIS Expressions Date Format get just YY for year

I found many articles on date formatting expressions in SSIS but I couldn't find one that specified how to get just the Year such as 13 for 2013 or 12 for 2012... I tried DATAPART("yy",GETDATE()) ...
-1
votes
1answer
403 views

SSIS SQL Result to tab delimited txt file [closed]

I have created a package that Runs a Sql Query and then puts the results into a flat tab delimited txt file. When I opened the flat file in excel everything was in the right columns but in columns ...
3
votes
1answer
198 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 ...
1
vote
2answers
437 views

SSIS Visual Studio 2008 reference variable scope in expression builder

When creating an SSIS package in Visual Studio 2008, in the expression builder, I know I can reference a variable by using @[User::FilePath] for example but this only works if you are evaluating the ...
5
votes
2answers
1k views

Relating ExecutionInstanceGUID to the SSISDB

The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions ...
5
votes
1answer
4k views

SSIS File System Task Copy File Could not find part of the path error

I have created an SSIS package that uses a File Systen Task (copy) that copies a file from 1 folder to another. The package runs fine when I run it from my local computer but when I import the package ...
2
votes
1answer
1k views

SSIS Package Data Flow Task RollBack on Error

I tried running a package and it failed but I noticed in the output box (in visual studio) that it managed to write 2000+ rows to a table. I was wondering if an SSIS package would roll back the ...
0
votes
0answers
92 views

Errors loading one set of data from Staging to Data Mart Fact Table

I am very new to SSSIS and am trying to load sales data from 3 sources. I have created data flows to extract the source data into staging, all of these work OK with no error. I now want to load ...
3
votes
1answer
322 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
0answers
411 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 ...
5
votes
2answers
521 views

In SSIS 2012, what is the purpose of flagging a package as an “Entry Point” package

In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package" Doing a search I found this page on MSDN which states: The value of 1 signifies ...
0
votes
1answer
34 views

Concurreny update error when using SSIS

I am encountering concurrent update error when using OLE DB command to execute a update procedure in SSIS. Below is the segment of code which will check for concurrency update. Currently I removed ...
0
votes
2answers
1k views

Advice for ETL from Oracle to SQL Server?

I'm building a data mart in SQL Server which needs to extract each month from an Oracle database. Currently this is done by exporting to flat files, then running all the inserts/updates in SQL ...
3
votes
1answer
1k views

SSIS 2008 Package Configurations being ignored

With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time ...
2
votes
2answers
147 views

Pull Access schema into a SQL Server table

We had an assembly that used to pull a list of tables and their columns from an Access database and place the listing into a SQL Server table for later use. For various reasons the assembly is no ...
2
votes
0answers
139 views

Limit Output Flat File Size In SSIS

I am using Flat File Destination to export my data's to a Text file. I need to limit the output File size to 1 MB. Is it possible?
2
votes
3answers
2k 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 ...
0
votes
0answers
21 views

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

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 ...
2
votes
2answers
535 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 ...
1
vote
1answer
180 views

SQL Data Collector - SSIS required on source server?

For SQL Data Collector: Do you need SSIS (SQL Server Integration Services) installed on the server that you are collecting data from. Or can the packages be run from another server -- like the one ...
6
votes
2answers
371 views

SSIS Validation slow. OLTP OLAP running on same server

I have four identical servers 2 are used in production and the other two are demo machines/emergency fail over boxes. The more powerful machine a 16 core 127 gigs of Ram hosts both our Datawarehouse ...
0
votes
1answer
477 views

Convert rows to columns in SSIS. Unpivot?

If I have data like this "","",WAREHOUSE ID,1,2,3,4,5... "","",WAREHOUSE CODE,BOS,HOU,LAX,NYC,CHI... "","",WAREHOUSE NAME,BOSTON,HOUSTON,LOS ANGELES,NEW YORK CITY,CHICAGO... But want to transform ...
3
votes
1answer
377 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 ...
4
votes
2answers
213 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 ...
0
votes
2answers
384 views

Import batches of rows from Excel in parallel

We receive an Excel file weekly currently containing about 250k rows. The file grows by about 300 rows each week. In SSIS I've hooked up an Excel source to an OLE DB destination to import the file ...
2
votes
0answers
411 views

Importing Excel files with SSIS

I'm trying to import an Excel spreadsheet into SQL Server 2012 using SSIS. There is only one sheet in the file. The file is a SalesForce export. I believe the Sheet name changes depending on when the ...
5
votes
1answer
951 views

SSIS package with BULK data load

I analyzed a C# program over the weekend for a customer that basically does the following: Truncate Table Fetch Data (source on these is either remote database or local database where job runs) ...
1
vote
0answers
891 views

sql agent job hangs without any errors or warnings

Problem: we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without providing any errors or warnings. It just hangs. BUT when i stop the job and run again ...
1
vote
1answer
403 views

How to avoid log space error while executing SSIS packages?

I'm executing ssis package with more than 70 million data's. But I'm facing issue as log space full error message while executing the package. Is there any option to resolve this issue?
-2
votes
1answer
218 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
0answers
103 views

How to use a foreach loop with project deployment model

I have a SSIS project that is configured to project deployment model, within this I have two connections one for a flat file and one for a sql db. Both connections are in project mode. Now I want to ...
3
votes
2answers
533 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 ...
6
votes
1answer
2k views

Timeout expired error while using copy database wizard

Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get ...
1
vote
0answers
123 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
5
votes
1answer
2k views

ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
2
votes
1answer
2k views

Login failure when running a SSIS package from a SQL Server job

I have a SSIS package that migrates data from a Sybase database to SQL Server. The connection to the source database has been made through an ODBC connection. If I run the package directly on my ...
2
votes
1answer
724 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 ...
2
votes
1answer
8k 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 ...
4
votes
1answer
786 views

Can I connect to SQL Server 2008 R2 database from SSIS 2012?

I need to redevelop an ETL process and I'm evaluating options. I'm running SQL Server 2008 R2 database, and SSIS 2008 will take too long to develop and test the required packages. I understand that ...
2
votes
0answers
281 views

Microsoft Visual Studio is Busy

I am working in Visual Studio 2008 on a SSIS package. It is really frustrating that I get "Microsoft Visual Studio is Busy" message quite often. It almost happens every time I try to open a data ...
2
votes
2answers
1k views

Average Time to Load 1.2 million records from Oracle INTO SQL SERVER 2008R2

I have an ssis package which loads 1.2 million records of data from oracle into SQL SERVER 2008R2(with 32 GB RAM) dataware house table once every week. (truncating and loading) This process is taking ...
2
votes
2answers
569 views

SQL Server imports data in wrong charset with SSIS

I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001. However, when I run a SELECT * FROM tableName the data appears mangled. For ...
0
votes
2answers
492 views

migrate SQL Server geography data to PostGIS geometry data

I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the ...
2
votes
1answer
345 views

Debugging a SSIS OleDbCommand

I have an OleDb Command component in a dataflow that does an update(yes, I'm aware of the performance implications). Running the pack in BIDS shows me that the component is being reached and runs ...
1
vote
1answer
352 views

Connect to SQL Server 2012 Database from 2008 Agent Job containing 2008 SSIS Package

I'm planning the rollout of SQL Server 2012. My client has a number of SQL Server Integration Services (SSIS) packages currently deployed to SSIS 2008 and consumed by SQL Server Agent Jobs defined on ...
1
vote
2answers
257 views

Optimizing SSIS Packages

I have a SSIS package that loads data in an Access DB to tables on a SQL Server DB. Currently I double click the package and excute it. There are times when there is a lot of data to load. My question ...
5
votes
1answer
424 views

Calling a .NET method in a SQL Server script

In the data migration process, I need to convert some columns values form the source system before writing to the target table. I've been provided with a Csharp code which reads a string and do the ...
3
votes
1answer
661 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, ...
0
votes
1answer
89 views

Logging of incorrect row in SQL Server Integration Services

I newbie to SQL Server Integration Services and I have a problem: Data loaded from file looks like this: 1 'a' 'b' 2 'a' 'b' 1 'c' 'd' And this rows would be loaded in table like this: CREATE ...
2
votes
1answer
697 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 ...