SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
2
votes
1answer
189 views
Data migration between databases in the same instance
I have two almost identical (structurally) databases:
Documents
DocumentsOld
The original database existed before table partitioning was available, so the solution they found was to create the ...
2
votes
4answers
238 views
What “tool” to use for monitoring instances and where to store this data?
I have three SQL Server instances in my DW project, development, test and production. I also have two SSIS servers (dev/test, production). My intention is to query data from these three SQL Server ...
2
votes
1answer
835 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 ...
2
votes
1answer
202 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
502 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
142 views
Advice on how to approach data cleaning & importing
I am importing and cleaning data from a datasource that have records like this:
The data needs sorting and rearranging based on the Event Type.
The destination schema would be something like ...
2
votes
1answer
242 views
Export SQL Server varbinary field to string using Visual Basic script in SSIS?
I'm using SSIS to export data from a set of tables to flat files. The export is running great except for one table with a varbinary field. The export is running a Visual Basic script task to load the ...
2
votes
2answers
582 views
how to use database mail from SSIS
I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks
2
votes
1answer
780 views
SSIS how to parse column with erratic data
I have a question on how to parse out a column and hoping a SSIS/Database guru can help out :)
Here is the problem. I have a column saved as a string/varchar format in SQL Server, and need to parse ...
2
votes
2answers
1k 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 ...
2
votes
1answer
550 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 ...
2
votes
1answer
155 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 ...
2
votes
1answer
350 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 ...
2
votes
1answer
217 views
Jet OLEDB driver refuses to open a linked table where the original has a lock - any way to force this?
I have a requirement to read reference data from several access databases for an ETL job. The data owners have produced a file with a series of linked tables that consolidates them all into one ...
2
votes
2answers
1k views
Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
We are just starting design for a new data warehouse and we're trying to design how our date and time dimensions will work. We need to be able to support multiple timezones (probably at least GMT, ...
2
votes
1answer
448 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 ...
2
votes
2answers
180 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 ...
2
votes
1answer
2k 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 ...
2
votes
1answer
180 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 ...
2
votes
2answers
389 views
SSMS 32bit can't connect SSIS 64bit
I can't find the solution and searching for info doesn't give desired results
Environment:
Server: W2K8 EE 64 bit + MSSQL SE 2K8 R2 64 bit
Client: Windows XP Prof 32 bit
Using SSMS on client ...
2
votes
2answers
138 views
SQL server job deployment
I have a SQL server job which has got different steps, including running a SSIS package. Currently it is on my pc (my local instance).
I should deploy it to UAT and assume some steps need to be ...
2
votes
1answer
330 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 ...
2
votes
1answer
774 views
SSIS PL/SQL task hangs with message “Multiple-step OLE DB operation generated errors.”
I've configured a Microsoft SQL Server 2008 R2 SSIS package with an Execute SQL Task to run a PL/SQL query on an Oracle 10.2 database.
The Execute SQL Task (named "SQL Update MY_STATUS flag") is ...
2
votes
1answer
138 views
use variable table in OLE DB source
I try to export data to a flat file from OLE DB source. Here is what I do under OLE DB source editor. In SQL command Text:
declare @t
table (customerid varchar(100),[enrollment_Date] ...
2
votes
1answer
81 views
Data Extract and submit across the network
I've been given a somewhat vague requirement regarding a data extract process which I need some help with. I'm fairly new to database warehousing etc. but I will try to explain everything relevant.
...
2
votes
1answer
181 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 ...
2
votes
0answers
41 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
1answer
77 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 ...
2
votes
0answers
311 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 ...
2
votes
0answers
72 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 ...
2
votes
2answers
223 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 ...
2
votes
0answers
118 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
1answer
293 views
Is there a way to do data export from Amazon Web Services RDS for SQL Server to On-Premises SQL Server?
We have a large Amazon Web Services RDS for SQL Server instance and we would like to do incremental data transfers from RDS to On-Premesis SQL Server on a regular basis.
The On-prem server will be ...
2
votes
0answers
574 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 ...
2
votes
0answers
180 views
SSIS - Derive column values based on join/subselect from a non-table source
I have a flat file source that contains a lot of duplicate records, which have the same key fields but differ by their "changed date". I need to split this up into two outputs, one for the "latest" ...
2
votes
1answer
516 views
Export data into word documents
My boss has a kindle and likes to save a lot of stuff to his clippings. Currently I'm going through and cutting out each clipping and pasting it into a seperate word document with the title of the ...
1
vote
2answers
52 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 ...
1
vote
2answers
3k 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 : ...
1
vote
2answers
70 views
Will SSIS 2012 work with SQL Server 2008R2 (not integrated)
Our company has SQL Server 2008R2 and plans to upgrade to 2012 in late 2013 or early 2014. I would like to use SSIS 2012 now as we do have the software.
We have a company policy where we cannot run ...
1
vote
2answers
79 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 ...
1
vote
1answer
844 views
SQL Server 2005: Integration Services Stored Packages is empty. Why?
I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored ...
1
vote
2answers
141 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
vote
2answers
114 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 ...
1
vote
2answers
160 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 ...
1
vote
2answers
385 views
Moving data from Oracle to SQL Server : Table size optimization
I am loading data using SSIS from Oracle to an SQL Server database. One table I moved had a size of 4 MB under Oracle but when loaded to SQL Server, the size grows to 7.96 MB!
I analyzed the size ...
1
vote
1answer
503 views
SSIS: “The buffer manager cannot write 8 bytes to file”
We have about 60 or more SSAS cubes on one server. All the cubes have the same structure. Each cube has a different size because each cube has a different underlying data warehouse. However the schema ...
1
vote
3answers
396 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 ...
1
vote
2answers
514 views
Using SQL Profiler on SSIS package
Is it possible to use SQL Server Profiler to trace a running SSIS package? I tried to do that, but all I can see in the trace is executing dp_sqlagent_... and the other internal commands! None of the ...
1
vote
1answer
56 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 ...
1
vote
1answer
290 views
uninstalling SSRS and SSIS from clustered SQL Server 2008 R2 SP1
Due to the security recommendation, i will need to uninstall the unused SSRS and SSIS component from a production cluster SQL Server 2008 R2 SP1, so what is the caveats and what do I require to do to ...
