SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
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 ...
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" ...
1
vote
0answers
125 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
1answer
202 views
How do I handle importing high amounts of transaction data into application DB?
For the moment, this is a hypothetical question. I've got a database that has a high level of transactional data coming into it. I also have a database that supports my website (my application ...
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] ...
1
vote
1answer
106 views
ssis delete database on error
I'm writing an ssis package that imports a number of access databases nightly. Each database has the potential to have a slightly different schema. Since I'm creating the tables than doing a direct ...
3
votes
1answer
435 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 ...
6
votes
5answers
242 views
SSIS ETL Question/Option
In our current prod environment, when the ETL launches, it first checks an environmental variable at the OS level which tells it where the "config" database and table is for it to get the necessary ...
3
votes
2answers
995 views
Can I export a maintenance plan without using Integration Services?
I'm trying to export a simple maintenance plan from an SQL Server instance.
I want to check the export into source control, and apply the exported maintenance plan to functionally identical ...
3
votes
1answer
119 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, ...
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
2answers
374 views
Using SSIS to get record counts for tables that may not exist
I have an SSIS package that executes simple queries against a list of servers. I am having an issue with one particular simple query: If a table exists, get the rowcount. Otherwise report "not ...
4
votes
1answer
284 views
Silent truncation of last character when using SSIS with Oracle
I am using SSIS to export data from an Oracle database. Sometimes when I export a VARCHAR2 column to NVARCHAR the last character gets truncated (silently). It's completely random as far as I can tell. ...
6
votes
1answer
1k views
Running an SSIS package owned by a domain user from SQL Server running on a local service account
I want to run an SSIS package containing Transfer SQL Server Objects tasks. The involved servers are on the same domain, but the SQL Server services are running on local service accounts. So the ...
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 ...
4
votes
2answers
174 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 ...
3
votes
1answer
1k 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 ...
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 ...
0
votes
1answer
145 views
How to load more than one MS-Access-db tables with data in SQL-Server-DB
I'm just getting into SQL-server 2008 R2 ssis , so forgive me if this is a basic question. I'm building an application using integration service.
On of my computer folder contain more than one ...
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 : ...
0
votes
1answer
291 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 ...
3
votes
1answer
873 views
On-Demand Fuzzy Lookup
We've got a customers table (Who doesn't?), containing many records that are, from a business perspective, duplicates. I've been able to create an SSIS package to perform fuzzy grouping, and report on ...
1
vote
1answer
201 views
Sending SQL Server query as CSV via e-mail
Currently I'm using SSIS w/ a hacky solution to get around it not supporting auth on SMTP servers.
Is there a better way I can save a query to a CSV file, then mail it as an attachment?
Thanks
0
votes
1answer
266 views
Generate a new Sub Plan/Sequence ID in SQL 2005/8 Business Intelligence Development Studio
I have what is effectively a template SSIS package (well a maintenance plan actually) that is copied and used as the basis of new maintenance plans.
In this package there is a Sequence (or a ...
1
vote
1answer
2k views
how to send mail from gmail smtp server by using the ssis
I'm just getting into sql-server ssis, so forgive me if this is a basic question. Use the ssis want to send mail from gmail smtp server .
Smtp host =smtp.gmail.com
port=587 ,
...
2
votes
3answers
595 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 ...
2
votes
1answer
781 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
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
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
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.
...
0
votes
1answer
137 views
BI development in Microsoft BI
I need to learn how to create a dimensional modelling for an organisation that do not has a BI tool and BI model.
Where can I learn more about BI development that should be used in a company?
IT's ...
3
votes
1answer
100 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 ...
4
votes
3answers
657 views
How can I make my SSIS process consume more resources and run faster?
I have a daily ETL process in SSIS that builds my warehouse so we can provide day-over-day reports.
I have two servers - one for SSIS and the other for the SQL Server Database. The SSIS server ...
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, ...
11
votes
2answers
321 views
To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS
I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
1
vote
1answer
2k views
SSIS Script Component - How to modify the Output0Buffer
I've a script component which accepts records from SQL Azure database table. The script then invokes a web service, which returns the number of failed and success records.
For all the records, I ...
1
vote
2answers
701 views
Flat file manager TextQualifier not working
I've a CSV text file which contains
col1,col2,col3
"abc,cdef",a,b
When I use the Flat File Source and preview the
a
bc,cdef appears on first column.
But when i run and view the result in ...
4
votes
2answers
787 views
SSIS Data Flow Error Output runs all the time
I've a SSIS Data Flow Task. Within which i've souce flat file and OLEDB destination. I've an error output sent to redirect row on OLEDB Destination.
The problem is that the redirect row always run ...
5
votes
2answers
1k views
SSIS flat file manager - skips the last record
I've a a flat file which is pipe delimited as shown below:
header|header2
abc|abcdef
abc|abcdef
When I create a package on a 64 bit machine it always drops the last record. But on preview it shows ...
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 ...
4
votes
1answer
3k views
Migrating from one SQL Server 2008 R2 instance to another
I just started a new dba job and have been given a new server and told to move everything from the old server to the new one, brand new windows and SQL Server installations. I know I have to transfer ...
2
votes
2answers
816 views
Can I downgrade SSIS packages to run on older version of SQL
We have some new SQL 2008 SSIS jobs that were recently developed, mostly to do some simple flat-file loading and targeting a set of tables on a 2008 server.
As it turns out, they may have to run on a ...
2
votes
1answer
2k 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 ...
6
votes
3answers
191 views
Find material for improvement in Microsoft BI
Which websites, books etc. can you recommend me to improve my basic knowledge in Microsoft Business Intelligence 2008, SSAS, SSIS and SSRS?
Please remember that I'm a newbie in technical BI tool.
If ...
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
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 ...
5
votes
1answer
659 views
Migrate DTS Jobs to 2008 Server
We have been running SQL Server 2000 for years.
Finally, at long last, we have an SQL 2008 Server being shipped in.
We have many DTS jobs on our SQL 2000 Server that need to be migrated to the SQL ...
6
votes
1answer
1k views
Duplicating some rows of a data flow in SSIS
I have a data flow to migrate data from an old database to a new one. The old design had all the data and historical information (changes) stored in a single table with a "version" (incrementing ...
5
votes
4answers
928 views
Duplicate database and keep it up to date - Replication or SSIS?
I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally.
We don't need ...
