SQLServerCentral / SQL Server 2005 / SQL Server 2005 Integration Services

Conditional Split failing to catch bad date

Brandie Tarvin — Fri, 16 May 2008 15:06:52 GMT

This is driving me nuts. I've re-written this particular split condition 3 times and it still won't pick up the date.Date is 01/11/5200. I'm using smalldatetime datatype in my staging table. The conditional split is supposed to send the invalid date record to a text file instead of a staging table. Here's the condition:ISNULL(RetroDate) == FALSE && (RetroDate (DT_DATE)"2079-06-06")Now I did have the dates listed "01/01/1900" and "06/06/2079" but that didn't work either. Then I put the (DT_DATE) conversion in front of the RetroDate field to double-check the conversion was the same, but that didn't work either...ARGH.Thoughts appreciated. Thanks in advance.

Start Debug tries to build project???

Brandie Tarvin — Wed, 14 May 2008 18:44:57 GMT

This just started happening out of the blue. Nothing changed (that I know of) except that I'm developing on an ETL project. So I get some major code changes done, go to test it by hitting the green arrow and the entire solution tries to build instead of debugging the one package I have open and want to run.WHAT?Has anyone seen this before? BIDS is complaining about not having a deployment server and even if I go into the menu and click Start Debug, I get the same problem. Tried restarting my PC, logging off & on, leaving BIDS off for a while, log back in and same problem. It tries to build the solution instead of debugging the one project I have checked out and open then complains because I don't have the "build" pointing anywhere.Help, please. How do I get this to stop?Thanks,

to rename a file by attaching datestamp at the end of filename using SSIS File task

Alicia Rose — Mon, 25 Feb 2008 15:05:51 GMT

Hi SSIS Gurus,I have a dataflow task that creates a text file and posts it in a FTP share.In need to rename the file attaching datestamp(current date of package execution) at the end of filename.For ex: My dataflowtask creates a file called 'Samplename.txt'I need to rename it to : 'Samplename20080225.txt.I think it can be done using the File task...I see an option to rename the file but not sure of how to configure the task to attach current datestamp.Suggestions and Help would be highly appreciated.Thanks,

Unable to UPDATE or DELETE using SSIS and OLEDB connection

randy.witt — Fri, 16 May 2008 07:38:20 GMT

Greetings. I have been trying to develop an SSIS package that updates external data (Visual FoxPro tables) from SQL Server 2005. I have tried this various ways: using various Data Flow task components that flow to an OLEB Destination; using an Execute T-SQL Task; and even trying Management Studio interactively with the OpenDataSource('vfpoledb', etc.) statement. For each of these techniques, I have no problem performing a SELECT from the VFP data. Also, I have no problems performing an INSERT of new records using any of these techniques. However, both UPDATE and DELETE of existing records fail.Is it possible the the OLE DB driver doesn't support UPDATE and DELETE operations? It appears that I'm not allowed to change or delete existing records, only add new ones. Or, are there other techniques I can be trying?Thanks much,Randy Witt

Why Use SSIS?

The underdog! — Fri, 16 May 2008 10:48:29 GMT

Basically the issue is-If i run t-sql srcipts, to pull data from the staging area, remove duplicates, validate , data and perform lookups i can do it for each of the 6 channels that i pull data from in one day.However, replicating the same using SSIS packages takes about a day(at best!!) for [b]Each[/b] channel. I used these tools/packages to basically automate all my ETL/Data cleansing tasks, it works perfectly fine, but takes agonizingly long. What can i do to improve the performance? i mean i have done all the basic things like setting the cache levels when i do a lookup etc. Or should i just use my T-SQL scripts.

Error-During data import/cleansing using SSIS packages(urgent!!)

The underdog! — Wed, 06 Feb 2008 13:25:40 GMT

Hi!I get the following error message when i run my data import task.[font="Arial Black"]Information: 0x4004800C at Data Flow Task, DTS.Pipeline: The buffer manager detected that the system was low on virtual memory, but wa[size="2"][/size]s unable to swap out any buffers. 8 buffers were considered and 8 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.[/font][font="Arial"]What my import task does is basically[/font]----------------1]Import Data from DB table(about 140 Mil rows)2]Fuzzy grouping of the data on the basis of Customer-ID, first name, last name, email ..with a similarity level of 13]Sort the data, deleting duplicate data 4]Stores the Deduplicated data into a DB(on the same server)The package works fine for smaller sets of data, but i know the basic problem is the huge volume of data. [font="Arial"]What i've done so far is [/font]1] Reduce the size of DefaultBufferMax rows from 10000 to 25002]Increase the size of Default buffersize to 20MBNeither seems to work.I need to import all the data and deduplicate but the system cannot find enough memory buffers. My System Admin sai dhe cannot increase the size of the paging/virtual memory.MAybe running the package in batches would help...but i have NO IDEA how to do this.Could someone pleasee help me with this..its my first Data Import project and this is really bogging me down.Any Advice..any Would be greatly appreciated.............!!!!!!!!!!!!

Downloading file by script task over http

mengmou — Tue, 13 May 2008 15:09:24 GMT

Hi,I need to download a page via http. I tried 2 ways but they were all not working because they didn't pass the authentication. The file downloaded is not what I need but the page used for entering username and password. the 1st way: Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain Public Sub Main() ' ' Create an HttpClientConnection and use it to download ' a file from the location the connection manager specifies ' Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection Dim temp As Object ' Try to get the connection Try temp = Dts.Connections("FileToDownload").AcquireConnection(Nothing) httpConnection = New HttpClientConnection(temp) httpConnection.DownloadFile(Dts.Variables("DOWNLOADEDFILE").Value.ToString(), True) Catch ex As Exception Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0) End Try Dts.TaskResult = Dts.Results.Success End SubEnd Class-----------------------------------------------------I do enter the username and password in FileToDownload http connection, but it doesn't work. the 2nd way:Imports SystemImports System.NetImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain Public Sub Main() Dim MyWebClient As WebClient Dim RemoteUri As String Dim LocalFileName As String Dim FireAgain As Boolean Try MyWebClient = New WebClient() ' get the context from variables RemoteUri = "http://abc.com/DataUniverse.aspx?SecurityTypeId=ST00000001" LocalFileName = "C:\Test.html" ' tell the user what we're downloading where Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", LocalFileName, RemoteUri), String.Empty, 0, FireAgain) ' do the actual download MyWebClient.Credentials = New System.Net.NetworkCredential("UserName", "Password") MyWebClient.DownloadFile(RemoteUri, LocalFileName) Dts.TaskResult = Dts.Results.Success Catch ex As Exception ' post the error message we got back. Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0) Dts.TaskResult = Dts.Results.Failure End Try End SubEnd Class------------------------------------------------------this way also doen't work.Thanks,mengmou

Newbie SSIS Question

Julian.Slaughter@mfs.misys.co.uk — Fri, 16 May 2008 09:32:35 GMT

I created an SSIS package that imports text data into one table and inserts it into another table with correct data types, i.e. datetime, int, etc. I have a SQL job set up to run this package every night.It seemed to run OK for a couple of months but over the last few days or so I have noticed an issue with dates.Bearing in mind I live and work in the UK, when I look at the data following the automated running of the package, dates appear in US format (mm/dd/yyyy) which is not what I want. If I run the package manually the dates appear in british format (dd/mm/yyyy) which is correct. As I say,this had been working OK for several months.The dates in the orginal text file are in british format, they remain in british format in the intermediary table but change to US when I do my INSERT INTO...... statement.Is this something I have done or have I found an "undocumented feature" ;)Thanks in advance for any help

Remove Null value from dt_ntext field

Rusty — Fri, 16 May 2008 09:22:43 GMT

In my ssis package I am taking data from an access db and inserting it into sql server 2005. Here is an example of the tables.Access TableField1 memoSql Server tableField1 nvarchar(max) not nullMy issue is that when I have a null value in the memo field and append it to sql it gives me an error that i am violating integrity constraints. I understand the reason for the error, but I am not sure how to check and replace a null in the field. I tried to use (ISNULL(Field1) ? "" : Field1) but it says:The data types "DT_WSTR" and "DT_NTEXT" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.Are there any suggestions on how I can replace the null value or will I have to change my field to allow nulls?Thanks for any help you can give

Using Temporary Tables with SSIS

chileu17 — Thu, 15 May 2008 16:32:10 GMT

I have found myself with another problem which is using temporary tables from SSIS. My sql command creates and uses some temporary tables but when I try to put these command into my OLE DB Source in SSIS I get an error saying that it can't find my temporary tables do you know if there is a way of using temporary tables in SSIS?Thanks for any suggestions. :hehe:

SQL Server 2005 DTS packages

venki — Fri, 16 May 2008 07:03:55 GMT

I have 8 different DTS packages scheduled in SQL Server 2005 to transfer data from Lotus notes DB to SQL Server DB. Some of them are working fine as they are pulling and loading the exact rows for tables. But some of the packages are not pulling the total rows from lotus notes DB. [b]Any body help me on this?[/b]I connected the lotus notes database by [i].Net provider/ODBC data provider[/i].I connected the SQL Server database by [i]Native OLEDB/SQL Native Client[/i].I am taking total rows from source and keeping them in Raw file in local system.Deleting the destination tables records. Then again loading the Destination table with the Raw file data.Ex:I have [b]DTSpack1[/b] to pull the data from lotus notes db to SQL db.In Lotus notes DB, I have around 10000 records but the package is only pulling around 2000 records. I don't know why this is happening for some of them.

Clarification Required in SSIS implementation

prisaras — Thu, 15 May 2008 09:33:45 GMT

Hi,We need clarifications in SSIS implementation for the below mentioned queries Requirement : 1 The flat file which is coming from the upstream server will be placed in the particular directory of the loadserver linux Box.Then,This flat file will be moved to windows shared drive folder(s:\) by SSIS FTP task. Once the flat files comes to shared drive,It should be referred dynamically by flat file connection manager one by one for the loading process. Problem Area : The flat file name will be like cmbwipsc00077.dat.The Sequence number in red color will keep on changing.In flat file connection manager,We just browse through and select a file name.But it should be automated.That is,Once file is placed in shared drive folder,The unique file name should be refered automatically in order to initiate the LOADING (loading into sql server 2005 staging tables)process . Questions: 1. Is there any way for the flat file connection manager to select filename dynamically one by one from the particular folder? 2. Is there any other way to accomplish this requirement through SSIS?Requirement : 2 1 .we need to validate the flat file whether it is having header and trailer information. 2.The flat file name (eg. cmbwipsc00077.dat) will have sequence number ie 00077.This sequence number(00077) will be checked against flat file header information which also has the sequence number. eg.. flat file header content : 0HEADER AARDCMBC2007-02-25-04.22.1000077 (We should get the value from 36th position to 40th position(ie 00077) from the flat file header information and check with filename sequence numbber) If both number matches(Header number and filename sequence number),The Loading will be initiated.Same way checking happens with Trailer.If anything goes wrong,mail will be sent and Loading will not happen. Questions: 1. Is there any way to accomplish this validation through SSIS package tasks? If so , please provide us the details.

How to export connection manager properties to external file

BEN — Fri, 16 May 2008 03:53:52 GMT

How do I export the connection manager properties to an external file such as a xml file.Regards,Ben

Using SSIS Return the results in to Excel.

Lee Hemmings — Fri, 16 May 2008 05:21:57 GMT

Afternoon,Am so new to SSIS and have very little experience but am struggling to do the above.I want to run a query and return the results in to an excel spreadsheet, if I use the import/export wizard and save it as a package its work fine once and then will give various error messages.Which leads me to think there is a problem with creating the excel file or using the excel file again when the job runs.So I would like to write/create SSIS package from fresh project.If anybody could list the things I need to do or point me in the direction or a tutorial or a step by step guide that would be brilliant.Regards Lee

Exporting to Excel....aaarghh!!!

FreeHansje — Tue, 13 May 2008 07:44:59 GMT

I have to export weekly data to Excel. Let's not discuss reporting services, that's past.I have googled the Net and found, that I can use the drop/create table statement to del;ete old data and insert new data. I have this script:[size="1"]drop table `ExcelDestination'GOCREATE TABLE `ExcelDestination` ( `DBKey` INTEGER, `ServerName` NVARCHAR(60), `DB` NVARCHAR(128), `File` NVARCHAR(8), `Size_in_MB` NUMERIC (19,2), `Space_Used` NUMERIC (19,2), `Available_Space` NUMERIC (19,2), `CheckDate` DATETIME)[/size]And I cannot see why I receive a syntax error. This should work, but it does not.Can any1 give me a hint?

Migrate SQL 7 DTS to SQL 2005 SSIS

darran — Thu, 15 May 2008 09:51:48 GMT

Hello AllIs anyone here familiar with migrating SQL 7 DTS packages to SQL 2005 SSIS.I have managed to migrate the databases although they are still not raised to the the SQL 2005 functional level. Is it possible to migrate the DTS straight to 2005 or is it necessary to migrate to 2000 first and then onto SQL 2005?Any help would be appreciated.Thanks :)

SQL Server 2005 and Sybase ASE 12.5.2

bobw — Wed, 19 Mar 2008 06:28:01 GMT

Greetings.Some background: I have a SQL 2000 database that is currently handling reporting and data warehouse functions. It pulls data from all our production dB servers, including 3 Sybase ASE 12.5.2 servers, using DTS packages. All data pulls in just fine. I use Sybase ODBC drivers on this box.I'm trying to get the datawarehouse converted to SQL 2005 SP2, using DTSX / SSIS. I wrote the extract packages using SSIS, and, for the most part, they run fine. I'm using the Sybase OLEDB drivers, version 12.5.2 for connectivity.All the database structures are identical on my 3 ASE servers. One of the ASE servers is located on the same LAN segment as my MS-SQL servers. I have two WAN Segments; the lower-speed WAN segment contains 1 ASE server, and a higher speed WAN segment contains 1 ASE server.The extract packages are identical for all three ASE servers, differing only in which server they connect to.ASE LAN Server 1 - all DTSX packages run fine.ASE WAN Server 2 - all DTSX packages run fine.ASE WAN Server 3 - the longest running DTSX package fails intermittently. All packages are typically executed during off-hours; however, if I manually execute a failed package during peak business hours, it might run just fine.The failure is always logged as:[i] "Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "Socket closed.".[/i]Now, the given package might run fine for a week, then suddenly it will fail with the above error.I've checked everything I can think of (ASE configuration, load) and am coming up empty. Now, to make matters even more interesting: I can successfully extract the same data, every night, with no problems to my SQL 2000 server, using DTS packages.I also have a problem with creating an ASE Linked Server in SQL 2005: I can set up the Linked Server, and I can successfully view all databases on the server. I can also, for all databases except 1, view all tables in those databases. However, when I attempt to view tables in this one database, I get an error displayed in SQL Management Studio: "Value cannot be null. Parameter name: context (ObjectExplorer)." I can display views in this same database, however.This linked server problem happens with all 3 of my ASE servers, and the behavior is identical. I also have this same problem in the November CTP of SQL 2008.Coincidentally (?), the database which refuses to display in a linked server is also the database from which I'm having trouble extracting data on 1 server.Thanks for any thoughts on this.

DTS Migration Wizard Error

chileu17 — Mon, 12 May 2008 15:09:53 GMT

Hi guys, I have to do some migration from sql server 2k to sql server 2k5 but when I use the dts migration wizard I get an error which is the one above. I have crawling the web but with no luck :unsure: Any ideas? hope you guys can help me. Thanks in advance.TITLE: DTS Migration Wizard Error------------------------------Exception of type 'Microsoft.SqlServer.Dts.MigrationWizard.HelperUtility.DTSMWException' was thrown.Click Abort to stop the migration of the current package.Click Retry to retry the operation.Click Skip to skip the migration of the current task and continue with the next task.------------------------------BUTTONS:&Abort&Retry&Skip------------------------------

scheduled package runs longer time than execution in BIDS

binchang — Thu, 15 May 2008 14:26:55 GMT

Hi,I have a package designed as bring data tables over to SQL Server. There are 9 data flow tasks that runs parallel. In BIDS, when I execute the package, it runs like 8 minutes. Or if I start the scheduled job manually, it runs around 8 minutes too. But the scheduled job runs about 30 minutes. I wonder what I can do to speed up the scheduled job.Thanks

ACTIve X Script and/or Script Task...Alternatives

gyanendra.khadka — Thu, 15 May 2008 10:16:48 GMT

AT First, Sincere appology to all, not being able to COde things. I am very poor at code. not so good knowledge about VB or Active X. I have this (code Below) Active X script task in DTS , and i AM moving to SSIS. My Active X task fails. ( Many of u are asking why u r using ACtive X in SSIS). simple answer . Poor at coding. I need help --If some one can code this one for me in Script task. ANy help will be highly appreciated. Or someone can understand the logic behind the code and give me alternatives. If i can do the same piece of work with combining mnay others tasks.Or som eone can write code for me in Script task and give me mor ehints to complete.Thnaks to all in ADVANCE>

Compare Source Safe with production SSIS

Chris Schmidt — Thu, 15 May 2008 11:36:46 GMT

Hi EveryoneI had to do a production install recently where I had to coordinate changes from a few programmers. We had Data Warehouse changes, Cube changes and SSIS changes.The SQLcompare tool works great for the database schema changes. Visual Studio takes care of all of the cube changes during the deployment. But how do we account for all of the SSIS changes? I was given a list by the programmers of their changes and I moved what I needed into the package store. Where we see a hole is that someone could leave something off of their list.Is there a tool out there to compare what is in Source Safe to what we have in our package store?Can anyone come up with a suggestion on how to manage SSIS installs?Thanks

CLARIFICATION REQUIRED IN SSIS IMPLEMENTATION

prisaras — Wed, 14 May 2008 09:28:44 GMT

HiWe need clarifications for some other implementation through SSIS package.1. The flat file which coming from the upstream server will be placed in the particular directory of the loadserver.This flat file will be moved to windows shared drive folder(s:\) by SSIS FTP task,then the file name should be refered by flat file connection manager. The flat file name will be like cmbwipsc00077.dat.The Sequence number in red color will keep on changing.In flat file connection manager,We just browse through and select the file name.But it should be automated.That is,Once file is placed in shared drive folder,The unique file name should be refered automatically in order to initiate the LOADING (loading into sql server 2005 staging tables)process.2. The Flat file is validated whether it is having header and trailer information.The flat file name (eg. cmbwipsc00077.dat) will have sequence number ie 00077.This sequence number will be checked against flat file header information which also gives the sequence number Eg. 0HEADER AARDCMBC2007-02-25-04.22.1000077 (We should get the value from 36th position to 40th position(ie 00077) and check with filename sequence numbber) If both number matches,The Loading will be initiated.Same way checking happens with Trailer.If anything goes wrong,mail will be sent and Loading will not happen.With Regards,prabhu.m

Debug Script

Terrie Smith — Thu, 15 May 2008 09:01:01 GMT

I'm using SQL Server 2005 SP2 Standard. I have a single script task that I want to set a breakpoint on so I can debug. I keep getting the message "SQL Server Integration Services Task has encountered a problem and needs to close." Has anyone else encountered this?Terrie

Problem with Excel Connection Manager

brad.luettke — Wed, 14 May 2008 10:18:10 GMT

Hi, I've got a small problem with an excel connection manager. Below is the series of error messages I receive: Error: 2008-05-14 08:59:21.17 Code: 0xC0202009 Source: WiReCAPSLoad Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".End ErrorError: 2008-05-14 08:59:21.18 Code: 0xC020801C Source: Data Flow Task 1 Source - Sheet1$ [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.End ErrorError: 2008-05-14 08:59:21.18 Code: 0xC0047017 Source: Data Flow Task 1 DTS.Pipeline Description: component "Source - Sheet1$" (1) failed validation and returnederror code 0xC020801C.End ErrorProgress: 2008-05-14 08:59:21.18 Source: Data Flow Task 1 Validating: 66% completeEnd ProgressError: 2008-05-14 08:59:21.18 Code: 0xC004700C Source: Data Flow Task 1 DTS.Pipeline Description: One or more component failed validation.End ErrorError: 2008-05-14 08:59:21.18 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation.End ErrorDTExec: The package execution returned DTSER_FAILURE (1). So from the message I'm assuming that somewhere in the package configuration its missing the path to be able to find the Excel spreadsheet in question. But when opening up the package configuration I can't see anything immediately wrong. For the path of the spreadsheet location I've used the full UNC path instead of any mapped drive locations I've being guilty of using in the past. Below is the Excel Connection Manager section of my configuration file: I've checked the rest of the configuration file and wherever something needs a username and password, they are entered correctly. I've also made sure that the account I'm logged onto our server and running the package under can access the excel file in question with no issues. What is abit strange is that I have another package that runs in a similar way and also uses an Excel Connection Manager but from a different folder in the same area and that runs fine. I've tried to compare the 2 sets of connection managers to see if I can spot the difference but I've been unable to so far. Al that I can see that is different is the folder winningproject somtimes being in lowercase and then sometimes having capitals, but that doesn't seem to make any difference. I'm hoping a fresh pair of eyes will be able to spot something obvious that I've missed!! Below is the Excel Connection Manager section of the configuration file I have that works fine.: I went to check that I can gain access to the Excel Spreadsheet through BIDS and I noticed when reloading the project in BIDS it came up with the error AcquireConnection method call to the connection managerfailed with error code I noticed there was a red cross on the Source Excel Data flow and when doubling clicking on it and then clicking preview it pops up with the box Password Required, An user id and password are required to connect to Excel Connection Manager. I don't remember having to specify a username and password when setting it up so I just used my own network id and password but that wasn't good enough, so I'm not sure what User Id and password it wants there. So I created a new Excel Connection Manager and went through the process of specifying the full unc path of the spreadsheet, specifying the sheet it is to use then clicking on preview and open pops the spreadsheet with the relevant information. I then recreated the configuration package referencing the new Excel connection manager and making sure it doesn't reference the old one and save everything to my sql server where I want to run it from. Again the same error message appears when trying to run it so I open the project backup in BIDS and this time the error message about the AcquireConnection appears and the red cross is back on the data flow and when I click on it and click preview the Password Required box reappears for the new connection, even though when setting it up originally it was all happy to let me view the spreadsheet but not now!! So with the Excel Connection Manager, where is the option to specifiy a username and password that it should be using to access this spreadsheet? Because when I go to set it up I get the option to specify the Excel File Path and Excel version is Excel connection settings, and then when clicking on the Excel Source Editor I have the options of OLE DB connection manager, Data access mode and Name of the Excel Sheet. From there I can choose Sheet1$ and hit preview and it brings up the data. I then can't see anything in Columns or Error Output that would appear to be relevant to Username or Password. Whereabouts it is possible to specify this and want account shall I be using? Thanks

Lookup task failed to match the records correctly

shreeprakashr — Thu, 15 May 2008 07:36:06 GMT

I am using SSIS 2005 Lookup task to match two tables. Though i have matching records between two tables, Look task still rejects records. We have used the same paramter to make the "inner join" between the two tables where the number of records return is not matching with results of Lookup task. The issue seems to be in SSIS Lookup task. Please let me know if you have faced the situation already. Thanks in advance.

How to "right-justify" data sent to AS400 file

JCC — Wed, 14 May 2008 14:43:56 GMT

Hi All, I have a very simple SSIS package that takes information from a SQL server table and puts it into a table in an AS400 database. It is working well except for one small issue. One of my fields is defined as a decimal(10,4). When it transfers over to the AS400 table, it is left justified - meaning that if the data in the field is '23.400' the file in AS400 just has blanks after it. The AS400 programmer would prefer it to have the blanks to the left so that essentially the information is right justified. Is it possible to do this? I've been wading through a lot of documentation but have not been able to find anything on this topic. Thanks in adavance for your help! JCC

Check if file exists in a FTP folder

Putts — Tue, 13 May 2008 13:16:06 GMT

I have a working package that gets some XML files out of a FTP folder, pulls them down local, processes them and then deletes the original from the FTP source.The plan is for the package to run every hour to check for recent activity.However, when there are no files in the FTP folder, my get files step errors. Is there a way to check the FTP folder first for existing files prior to doing the get step?Thanks in advance

Lookup Transform Error

The underdog! — Wed, 14 May 2008 13:21:03 GMT

Hi,I get the following error when i configure the lookup transform in the data flow task "Input column has a data type that cannot be matched".This is the query that i use to set the reference table datasetselect firstname, lastname, address, email from customers_dimension cd , cust_test ct where cd.address<>ct.address.I basically want to try and find all those records that have the same firstname, lastname, email in the customer dimension table where the records do not match. Both the input fields and the lookup fields have the same data type [varchar()].Does anyone have a better idea as to what the problem is?Thankyou

Transfer Database Task - Problem

Celeste Zurbrigg — Wed, 14 May 2008 17:06:45 GMT

Last year while doing some testing on SQL2005, I tried to use SSIS to replace the DTS package that we are using in SQL2000. After a lot of frustration, I posted a message entitled, "Transfer SQL Server Objects Task - Problem" on Aug 20, 2007. MG replied indicating that the task did not work and that Microsoft was not planning to address this in the 2005 version: http://www.sqlservercentral.com/Forums/Topic392144-148-1.aspx?Highlight=transferI am now trying to use the "Transfer Database Task" instead, and am having problems. Is this buggy also, or am I doing something wrong? Please see below:1. It seems that when using this task, I must use the SMO Server Connection (not an OLE DB Connection). [b]Is this true?[/b] In this type of connection, it does not allow me to identify a database (just the server, auth type, id and password).2. Then in the "Transfer Database Task" properties, I specify the source and destination Database Names. In addition, it requests that I identify the Database Files. I've read how we need to format it as: \\ServerName\SharedDrive\Rest_Of_Path\DBName_Data.ndf. I have done that, and I'm receiving the following Validation Error (I have replace my info with the above placeholder): Could not find the file "\\ServerName\SharedDrive\Rest_Of_Path\DBName_Data.ndf" in the source database "DBName"Yet the path is correct. [b]Is it a permission problem?[/b] I have read that I must "have administrative access to the share." I assume that is talking about my network ID since I'm working within SQL Server Business Intelligence Development Studio, and not the SQL Server User Name that I have specified in the SMO Server Connection properties. My network ID participates in a group which is in the Server's Administrators group and has Full Control. The SQL Server User Name is the owner of the database.Please advise.

Has the File Been updated?

Richard Farrington — Fri, 09 May 2008 02:20:26 GMT

Hi All, I am struggling to do what sounds like a simple task (famous last words)I have a SSIS package which pulls data from an Excel Spreadsheet and puts it a SQL DB. (simply put)The Spreadsheet is produced by a scheduled Crystal report.What i want to do in my Package is to check to make sure the Spreadsheet has been updated today before bringing it into the DB. If it hasn't i want to be notified (the package can still run but need to know the data is out of date)Any thoughts on the best method of doing this?Thanks very much in advance!

SSIS

dougk — Wed, 14 May 2008 12:15:10 GMT

Loaded SQLExpress with Visual Studio. I also subsequently loaded Management Studio. What I have not been able to do is to access SSIS. Is SSIS (BI Development Studio) a separate piece of software I need to load? If so, where do I find it. Really stuck!!

Raw File Destination/Source builds raw file on the fly, validation errors

Devo_Scott — Wed, 07 May 2008 11:00:53 GMT

Hi, I'm trying to 1) create a Raw file on the fly in the directory C:\SSIS, 2) write data to a Raw File Destination in a DFT, and 3) read data from the same raw file using a Raw File Source in another DFT. All three steps occur in the same package.I have set "ValidateExternalMetadeta = False" for both the destination and source. The destination is also set "WriteOption = Create Always" to ensure that the file gets created if it doesn't exist. The destination is no longer giving me validation errors, but the Raw File Source is alerting me of the following (see below). Is it possible to use a Raw File Destination and Source, created on the fly by the same destination, in the same package? It appears that the source continues to validate, even though I have told it not to do so until runtime.Thanks!TITLE: Package Validation Error------------------------------Package Validation Error------------------------------ADDITIONAL INFORMATION:Error at DFT Retrieve Location_* Sub-Class Data [RFS LocationTablesData [2350]]: File "C:\SSIS\LocationTablesData.txt" cannot be opened for reading. Error may occur when there are no privileges or the file is not found. Exact cause is reported in previous error message.Error at DFT Retrieve Location_* Sub-Class Data [DTS.Pipeline]: component "RFS LocationTablesData" (2350) failed validation and returned error code 0x80004005.Error at DFT Retrieve Location_* Sub-Class Data [DTS.Pipeline]: One or more component failed validation.Error at DFT Retrieve Location_* Sub-Class Data: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration)------------------------------BUTTONS:OK------------------------------

Zip Files Using Script Task

raghav.gurram — Fri, 07 Mar 2008 18:42:25 GMT

Hi All, Iam New to this forum, Can any one please tell how to Zip two or more files Using Script Task. Thank You,-Raghav.

ETL Server Performance

Megan Kelly — Tue, 13 May 2008 16:42:46 GMT

We have a core set of SSIS packages that are used by each server (12 Servers totals). It's become a real pain to deploy these packages each time we bring a new server on-line. I'd like to store a single copy of each SSIS package on a dedicated ETL Server. Does anyone know of any serious performance issues this might cause? Each of our SQL 2005 Servers would be calling the same SSIS package at the same time...or at least within a minute or two...of the other server. If a package is called from a remote server...where does the processing occur?I've searched all around this site and microsoft and can't seem to find the answer...any help is greatly appreciated!!

SSIS REPLACE Double Quote

Ed — Tue, 27 Feb 2007 14:36:00 GMT

I was trying to use the Replace function in the Derived Column transform to strip double quotes (abbrev for inches as in 6'4") out of a data field, but couldn't get the syntax right. Here is what I tried unsuccessfully:

REPLACE([AssetDescr], ""","in")

Does anyone have a syntax that works here?

As a workaround, I modified the SQL query in the OLEDB Source with the SQL REPLACE function, but I was just wondering if it can be done via the Derived Column.

How can i Dynamically set a property in fuzzylookUp dataflow task + ssis?

melisathomas_s — Mon, 12 May 2008 04:37:12 GMT

HiI am working on a SSIS Package with FuzzyLook up as my dataflow task.I need to dynamically set the MaxOutputMatchPerInput property of the FuzzyLookUp dataflow task.And the value would be select count(*) from table1".Number of Records in this table changes during runtime. 1) If i am setting this count in a variable how can i access it in my FuzzyLookUp component? How can i set this MaxOutputMatchPerInput property?2) I want my input record to match with all the records of the reference table.How can i do this otherwise?ThanksJesica

SSIS Package to import data from txt files to Tables

kishore.kk — Tue, 13 May 2008 01:21:01 GMT

This question is regarding SSIS:I am trying to loop through a directory of directories that contain directories which contain files. i.e:C:\databases\client1\client1.txt need to map into Table Client1C:\databases\client2\client2.txt need to map into Table Client2C:\databases\client3\client3.txt need to map into Table Client1Does anyone know how to do this? I was thinking a foreach loop, but I don't know how to create connections and looping.Thanks in Advance,kishore

SSIS + Sql server 2005

swatianand83 — Tue, 13 May 2008 01:45:34 GMT

I am using Sql server 2005 as a database. While using SSIS Derived Column Transformation Editr control, i have added 2 new columns to resultant datasets and their values are being set as:Date = (case when charindex('TDt', Description,1) != 0 then Assessment_Date else 0 end) but the above command gives an error that : 'Attempt to parse the expression failed. Might contain an invalid or incomplete token..... 'Please suggest what could be an issue here. thanks.

How to execute SSIS package based on a file existence

Mh — Tue, 13 May 2008 15:26:52 GMT

I am not sure if we can execute SSIS package using t-sql, but I want to do the following:Step1. Execute SSIS package1Step2. Check for the file1.txt in c drive using the below code:Declare @result intexec xp_fileexist 'c:\file1.txt', @result output IF (@result = 1) --if file exists exec ssis package begin Execute SSIS package2 endIf we can't use t-sql, please let me know how I can do that as part of sql job. Thanks!!

adding columns using derived column transformation

thenepatsrule — Tue, 13 May 2008 16:47:39 GMT

hi guys,i have a question regarding the use of expressions in the derived column transformation of ssis,i have about 4 columns ,two of them are needed for this expressionbasically here are the columnscolumn: A (useless)column: B (useless)column: trueORfalsecolumn: Valuei want to add two more columns to this table using derived column transformation i.enew_column_total_trueandnew_column_total_falseand the VALUE(one row) under these new columns will be the sum of Value when it is true / falsefor ex. if Value is 10,20 when trueORfalse is T then the value under new_column_total_true column should say 30