I am trying to deliver an SSIS package to SQL Server 2005 Enterprise Edition running on Windows Server 2003 R2 (64bit). The package itself is quite simple, just a basic read/write of a MySQL table (testing the integration). I am able to build and deploy the package on my development machine, and I'm able to debug & build the package o...
I'm trying to update Table1 in DB1 with data from Table2 in DB2. I can connect and get the data from DB2 Table2 into DB1 Table1, but the issue I'm having is getting the MOST RECENT data from DB2 Table2. I'm looking at 3 fields in DB2: f1, f2, & f3. f1 contains duplicates (and is where I'm matching from DB1 Table1) and f3 is a date f...
Is there any way to execute a scalar-valued function from within a Derived Column transformation in SSIS? -Scenario- I have a function in my source DB that converts weights based on a UOM value in the record's UOM column. I want to utilize this function in the ETL process to ensure that my weight measures are always pounds. Can I call ...
An interesting one, we're evaluating ETL tools for pre-processing statement data (e.g. utility bills, bank statements) for printing. Some of the data comes through in a single flat file, with different record types. e.g. a record type with "01" as the first field will be address data. This will have name and address fields. A record ty...
Hi. Is there a high quality sample code for Data Flow Components in SSIS? I specifically need the following: OLE DB Source Derive Column Transformation Flat File Destination. Lets say that the table schema comprises of two columns, ID of varchar(2) and Description of varchar(20). The output is a flat file ID of 1 char (just get th...
I have an automated daily import where I am running a SQL Server Integration package programmatically. The error message it reports is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." I believe this is the same message reported when ADO.NET commands timeout. I have lo...
I'm on a 2 developer team. We're running SQL Server 2008 and SQL Server 2008 SSIS. We have an external library that I've written to take advantage of some reusable functionality in a script task. I've signed the assembly with an .snk, compiled it, and added it to the GAC on the dev machine. When I load a project, I can add a reference to...
I am using a script task in ssis 2005 which takes some time and getting a problems timing out with "The operation has timed out". Where abouts is the setting to change the timeout period? I cant seem to find it in the properties in task and I do not wish to make a global change to timeout. ...
What is the best way to design a SSIS package? I'm loading multiple dimensions and facts as part of a project. Would it be better to: Have 1 package and 1 data flow with all data extract and load logic in 1 dataflow? Have 1 package and multiple data flows with each data flow taking on the logic for 1 dimension? Have 1 package per dim...
I created SSIS packages a while ago, today I need to re-point them to a different SQL server. I have a bunch of "Execute SQL Task" tasks, which need their Connection property changed to a different server. I created a new connection manager, double-clicked on each of the sql tasks, changed their Connection property to the new one, ran. ...
Hey Everyone. Okay, so I'm at a temporary office and they've given me a machine with XP to do dev on. Awesome. I've set the site up in IIS and I need it to serve shtml files. Problem is, when I browse to the shtml file, the browser tries to download the file, rather than display it properly. I've tried a few things that I've found on...
what is the best place to start learning about SQL Server 2005 Integration services? I'm looking for links for setup and tutorials on how to get started. I've read I need a toolkit called SSIS designer however I haven't been able to find it on the SQL Server Enterprise ed dvd nor online anywhere. I did come across some links in vario...
I have an SSIS package I am developing. I am attempting to write data from SQL Server 2005 to MS Access 2007. I am pretty stumped on how to convert a SQL char(1) field to an Access Yes/No field. From the information I have gathered, the SQL equivalent of an Access Yes/No field would be a bit field, with values of either 0 or 1. My SQL...
Within an SSIS package I have a Web Service task which uses an Http Connection Manager. I need to set credentials for the connection so that it will authenticate with the remote web service. I can set these at design time, however I would like these credentials to be provided to the package using an XML package configuration file. The p...
We are trying to have faxes automatically be sent from within an SSIS package. We have figured out how to send faxes utilizing the Right Fax COM API (rfcomapi.dll) But our issue is that in order to reference any external assembly within SSIS, that assembly must be in the GAC because SSIS utilizes VSA and not the full-blown dev environm...
I have a fairly standard OLTP normalised database and I have realised that I need to do some complex queries, averages, standard deviations across different dimensions in the data. So I have turned to SSAS and the creation of OLAP cubes. However to create the cubes I believe my data source structure needs to be in a 'star' or 'snowflak...
It seems like it should be simple but as of yet I havent found a way to save the value stored in an SSIS string variable to a text file. I've looked at using the flat file destination inside of a data flow but that requires a data flow source. Any ideas on how to do this? ...
Here's the scenario: I'm an SSIS virgin, but I found an excuse to start playing with it. I have a single Excel source dataset in the following format, with each row representing one month (the last day of the month): [Date] [Value] 4/30/2008 3.38 5/31/2008 3.65 6/30/2008 3.97 ... My destination is a table that has a ro...
I have a mdx query that returns valid results in Sql Server Management Studio and would like to automate the execution of this query and put the result into an email. SSIS seems to be the natural fit for this. I have been able to run the mdx in an "Execute SQL Task" and populate an object variable with the result, but I am unsure how to...
Hi, I am trying to import a column of dates from a spreadsheet in Excel 2003 into SQL Server 2005 using SSIS. I am in the UK so want dates formatted as dd/MM/yyyy. Unfortunately, the column in the spreadsheet contains a mixture of dates stored as strings in dd/MM/yyyy (with Excel 'General' formatting) as well as dates using Excel 'Dat...