Hi, I have the following table which I'm pivoting: (ID, Name, Value) 1, 'Task ID', 123 1, 'Language', 'Java' 2, 'Task ID', 456 I have set the PivotKey to be the Name column, and the PivotedValue to be the Value column. For each of my new columns (Task ID and Language) I have set their PivotKeyValue to be 'Task ID' and 'Language' re...
What is the best way to import highly formatted data from Excel to SQL server. Basically I have 250+ Excel files that have been exported from a reporting tool in a format that our business users would prefer. This is a 3rd party tool that can not export data in any other format. I need to "scrub" these files on a monthly basis and impor...
I have a million rows in a database table. For each row I have to run a custom exe, parse the output and update another database table How can I run process multiple rows in parallel? I now have a simple dataflow task ->GetData->Run Script (Run Process , Parse Output)->Store Data For 6000 rows it took 3 hours.Way too much. ...
I need to build SSIS packages on a build machine, and do not want the overhead of installing SQL Server Management Studio on this machine. A SQL Server 2008 SDK would be ideal, but I could not find where to download it. The dlls I require are (for example): Microsoft.SQLServer.ManagedDTS Microsoft.SqlServer.PipelineHost Microsoft.Sql...
Hi, I need to import data into my SQL Server 2005 from an e-mail datasource on an Exchange mail server. It means that when a mail is sent to a particular mail address I must retrieve the mail subject and the attached file and then I must import these data into my SQL Server 2005, using IMAP. Can I do this with SSIS, or do I have to wri...
I was talking with a co-worker yesterday regarding a situation where he used SSIS (or something like that) to do some really cool thing with an SSIS Package where he passed in a name like "Dr. Reginald Williams, PhD." and based on some weighting scheme the system was smart enough to figure out how to tokenize it and store it in the datab...
I need help with an SSIS package. I have a server that populates a DB table as a part of a daily job. Then the server writes to a process status table to let me know that its done. The process status table looks like this: Job | Ready | Downloaded myJob True False I want to create a process that will check if the Ready Flag ...
I'm using SSIS with SQL Server 2k5 to build a transfer task to copy all of the data from one database to another. This works quite well, except for one problem - the source database will periodically have schema changes (generally just additions like new columns) but the transfer task seems to choke if the two schemas don't match exactly...
I have an SSIS data flow conditional split transformation that is returning a >= result when it should be returning a > result. From the beginning... My SSIS package executes a SQL task that stores a max(date) from one table in variable User::max_date of type DateTime that has package scope. My package then proceeds to execute a Data...
We are trying to get data out of a SQL Server DB and submit it to a WCF web service. Since using a windows service is not a option in this case, I thought of using SSIS. I have tried using the Web Service Task, but I'm having difficulties getting it to work. I also understand there are limitation to using Web Service Task. What is the ...
H guys. I'm developing a custom component for SSIS. I'm having a problem when processing inputs. The problem is that the "ProcessInput" method gets executed more than once. Two times in this case. This is the process input snippet: public override void ProcessInput(int inputID, PipelineBuffer buffer) { IDTSInput90 input = Component...
I am running an Execute SQL Task statement in my SSIS package. The Execute SQL Task is running sql and checking that the tables have more than 1000 rows. If they have less than 1000 rows, I want to fail the package. How do I force a fail inside of a SQL statement? ...
Hi I'm just trying to understand SSIS packages a bit better and how they are deployed. Correct me I'm wrong but for any deployment, I believe there needs to be at least two files a .SSISDeploymentManifest and a .dtsx. The .SSISDeploymentManifest acts as the equivalent windows installer package which points to the .dtsx. The dtsx is the...
Within a Foreach loop in SSIS is there a way that if a task fails you can break out of the loop to the next iteration? I am looping over xml files and performing a lookup using values within this file, if the lookup doesn't return any values i'd like to report on this and then not perform any other tasks for this file. If there is no e...
I am supposed to perform ETL where source is a large and badly designed sql 2k database and a a better designed sql 2k5 database. I think SSIS is the way to go. Can anyone suggest a to-do list or a checklist or things to watchout for so that I dont forget anything? How should I approach this so that it does not bite me in the rear later ...
I have an XML source in a Microsoft SSIS 2005 package and when I debug the package I am receiving warnings like: [DTS.Pipeline] Warning: The output column "AccBasicRateDesc" (15229) on output "AccFwdDetail" (303) and component "XML Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increas...
I'm developing a custom component that inherits from a PipelineComponent in SSIS. This is the "ProvideComponentProperties" snippet of code: IDTSOutput90 output = ComponentMetaData.OutputCollection.New(); output.Name = "Output"; output.SynchronousInputID = input.ID; output.ExclusionGroup =0; m_DefaultOutputId = outpu...
I have created a few DTS packages and saved them on the server. where I can edit these DTS packages on the server? ...
I have a cube in SSAS 2005 that calculates running sums over millions of records. The current implementation is using a PeriodsToDate sum function in MDX and it is horribly slow, taking 15 minutes or more to return results in some cases. There are a huge number of solutions to this problem so I am hoping someone else has already tested...
I am getting the following error when my DTS package is scheduled: Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER I am not sure how to get my DTS packages to execute. They run fine when I manually execute them I am running SQL Server 2005. DTS Packages were created using the Import/Export Wizard. ...