.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Who can come up with a good SSIS solution to this problem?

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
Test your SSIS optimization skills on this problem: http://ask.sqlservercentral.com/questions/17499/phil-factor-speed-phreak-challenge-6-the-stock-exchange-order-book-state-problem Johan Åhlén http://blogical.se/blogs/jahlen  

View Complete Post

More Related Resource Links

Extreme ASP.NET: A New Solution to an Old State Storage Problem


State management in Web applications is a contentious issue. Should you store user data per session or should you persist it across sessions? You can easily store information temporarily while someone navigates your site by using session state.

Fritz Onion

MSDN Magazine April 2006

Data Mining solution - SSRS Problem

I have created a Data Mining - Time Series solution and I am getting proper results from SSAS(Mining Model prediction tab) & SQL Management Studio query. However when I try to create an report using SSRS, In the field where I am expecting the predicted value, I am getting "#Error" Any suggestions as why this is happening.

Strange Problem - SSIS Fails with "Syntax error, permission violation, or other nonspecific error".

Hello, I have a strange issue while I am deploying a package to one of the environment server. I have 2 XML Source, in the DataFlow, and one will extracted based on a value of variable that is passed from run time (package is executed from a Job) and other will be extracted all time. The next Step I have is a Execute SQL Task in ControlFlow wihich will execute after DataFlow. This has 2 input parameters and some SQL query that uses the param. Now this one fail on the target environment with below error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I got the error when I did a SSIS Text File Log. Note: If I run the package in BIDS with the same XML files it work. Also when I deploy the package to my Dev Server it works. When I compare the Dev DB with the target environment DB - Both are same. The Service Acount has permission - as I can see the DataFlow task completed. Another Point: The XML Load Data Flow that executes based on the Variable Value does not execute on target environment, even if the value is passed as "True" (It is Boolean Type) But this variable is NOT an input for the Execute SQL task that fails. I am not sure w

Problem importing text files with binary zeros (0x00) via SSIS(SQL2005). It is all fine when using D

Hi.   There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following: A17     123.17  first row          BB29    493.19  second             ZZ3     18947.1 third row is longer And in hex format: 00:  41 31 37 20 20 20 20 20 ? 31 32 33 2E 31 37 20 20  A17     123.17  10:  66 69 72 73 74 20 72 6F ? 77 0D 0A 42 42 32 39 20  first row??BB29 20:  20 00 20 34 39 33 2E 31 ? 39 20 20 73 65 63 6F 6E     493.19  secon30:  64 0D 0A 5A 5A 33 20 20 ? 20 20 20 31 38 39 34 37  d??ZZ3     1894740:  2E 31 20 74 68 69 72 64 ? 20 72 6F 77 00 69 73 20  .1 third row is 50:  6C 6F 6E 67 65 72       ?                          longer          I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).   The table is: CREATE TABLE eng ( src varchar (512) )   When i upload this file into SQL2000 using DTS or Import wizard, the table contains: select src, substring(src,9,8), len(src) from eng <               src                ><substr>             <len> A17     123.17  first row           123.17                  25BB29                                493.19                  22ZZ3     18947.1 third row           18947.1                 35   As one can see, everything was importe

SSIS 2005 - Send Mail Task - signature appended to email is garbled - unicode problem?

Hi, I'm pretty new to SSIS so go easy on me. I have a Send Mail Task to notify if a file cannot be imported - the mailbody is created on the previous step by a VB.NET script task to include the name of the file and the path it's been archived to. The problem I'm having is that while the body of the email I've created is displaying fine, our company's Exchange server appends a signature to all emails, and this is coming up as undisplayable characters, presumably due to some kind of unicode encoding problem. I've tried casting the email body in an expression to DT_STR (doesn't work as DT_STR "cannot be converted to a supported type" which seems a bit odd but never mind), DT_WSTR (garbled signature), DT_TEXT/DT_NTEXT (strange error on this one - "Attempted to read or write protected memory") none of those ideas worked, and I'm a bit stumped now. Can anyone help? I'm using SSIS 2005 with SP3

ResGen.exe: Problem when compiling solution appears out of nowhere

  Hi guys!   I have a weird problem with my solution (or perhaps my machine?). When I try to compile my solution with embedded resources (two .docx documents), I receive the following error german error message: Die angegebene ausführbare Datei der Aufgabe "ResGen.exe" konnte nicht ausgeführt werden. Zugriff verweigert    my english translation: The executable file for the task "ResGen.exe" cannot be executed. Access denied. I have no idea, how to resolve this error. Does anyone have a clue? Regards, Florian P.S.: @moderators: I hope, I've posted in the right forum; if not, feel free to move this thread in the proper place.

Problem with Merge Join and condition splt trasnformations in SSIS 2008

  Hey, While working SSIS in 2008 we have encountering some weird problems with Merge, Merge Join and Condition Spilt transformations, here are the details... Condition Split and Merge Join and Condition Split: Please check the below diagram(not really just tried) for the complete details. Just FYI data is in sorted order   condition Split -> 420000      | 18                                                          | 419982  Merge Join(left)  -- src1(6000)      |10  Condition Split      |9     |1   the merge join transformation is not returning the complete 18 rows to the next transformation that's why we are missing 8 rows. But if we adds a sort transformation (getting warning as data is already sorted) before merge then complete 18 rows were passing to the condition split. Also tried modifing the source query just to return 18 rows then also it was passing the 18 rows to next trasnformation except with full load.       Merge: We have two sorted data sources and first one has 40000 records and second source have 12000 records and after the Merge transformation we have Condition Split transfor

Need to Automate in SSIS Cube Drop and Recreate of Dimensions: Had to do on the Solution Explorer Ma

Hi SSAS Engineers: This question is in the context of a complete refresh of the SSAS cube. I'm OK on the SSIS approach thanks to TMcD and past successes (also thanks to him). The question is why did I have to manually drop/delete the dimensions in BIDS SSAS, then readd them with the cube wizard to pull in the dimensions with the same names, but new surrogate key relationships and more surrogate keys in total? We need to Automate in SSIS Cube Drop and Recreate of Dimensions. I originally had to perform this function on the Solution Explorer Manually in SSAS UDM, since the names are the same, but the structures and the data are changing for the dimensions on the refresh (and the fact table is subsequently increasing). Herein lies the problem. We had a keynotfound error in the cube processing, since the old keys were being kept. I had run Full processing on the dimensions, but since the dimensions have the same name (no drop old names and add new names), the old surrogate keys remained. The fact table had the old row count as well, since the surrogate key pipeline to look up the surrogate keys for the new dimensions only had the old dimensions, even though I truncated the fact table. Yes--I had already rebuilt the dimensions in SQL Server and the row counts were the new refresh row counts. The dimension counts were right according to refresh numbers after

is the overarchitecting of a solution a major problem in the development world today.

Hello all,I have been on this project for a while and wanted to get your thoughts.I have been working on a Sharepoint solution for a while to create a ASP .Net feature in Visual Studio. Now this is your standard CRUD application that queries data, displays data, updates data etc.As I am proceeding to code in this the original developer has used quite a few patterns MVP, observer, commands, repository and a couple of others. Being that this is Sharepoint developement and integration with Sharepoint is not good at all, I am finding that to much time is spent managing facets of the patterns rather than actually finishing the app. Another point is that debugging a Sharepont app with VS2008 is a pain because I have to redeploy the app everytime to make sure my changes worked.My chief concern is that its taking me going through about5 hours, 8 different objects having to redeploy the app to sharepoint over 10 times to test my code just to populate a couple of grids and comboboxes. It just seems more efficient use of my time program this app just seperating out my data access and business logic layers and calling the them from my code behind in my ASP .Net pages. It just seemed like he used the patterns just to use them without understanding why they are needed or a patterns proper place in an app..What do you guys think?

Index droping problem in SSIS package?

Hi All, I have one ssis package. After load the staging data I am creating index in one column. I am dropping the index end of package. But sometimes after create the index if I got any error I need to re run the package, already index is created so that’s why  once again I will get a error. So that purpose drop index statement I used before create index sqltask.If the first time package runs the table don’t have index. If the table have index it will drop the index otherwise it will skip that task. I used below code. But I am getting the error index is not dropped properly. IF EXISTS (SELECT name FROM sys.indexes WHERE name =   N'IND1_TRAN_DT ')  DROP INDEX  IND1_TRAN_DT  ON SLS_STNT_DTL_STGNG;   Thanks CMK

SSIS - Adjusting command timeout (good or bad practice)

Hi,   I'm building a bank reconciliation process where I'm leveraging SSIS to import, transform and insert data into a database.  I had a command timeout error at the transformation segment (Derived Column methods). At first was it difficult to determine (Ambigious SSIS errors), but in the end was easy to resolve.  I simply increased the timeout duration. Being a developer, I find this to be bad practice as the application should be optimized as much as possible prior to making a timeout adjustment.  An ideal method would have been to cache more data prior to calling the database command, so the data is available for processing and not keeping the command open unnecessarily. I'm relatively new to SSIS, but have been unable to determine a method to remove the transformation bottleneck. I could do it easily enough with customized .NET code, but I'd prefer not to do that.  My question - There must be a method in SSIS that buffers the data originating from the transformation prior to calling my INSERT to database method?   Thank you,   Russ

SSIS package is giving error when ran thru JOB...its running good when I execute in BI Studio or Exe

Friends I am getting weird issue...I built an SSIS package. In one variable I used express builder and built like this... RTRIM( (DT_STR, 2,1252)  (((DT_I4) ( (DT_WSTR, 3) (SUBSTRING( @[system::machineName] , 12, 16 ))) +1) /2)) when I run the package outside ie., with VStudio or execute utility it running absolutely with out any problem. But when I created a job just to execute that package in particular timings...the job is totally failing by throwing error....the error output is like the following: Started:  10:22:33 AM Error: 2010-02-27 10:23:06.23    Code: 0xC00470C2    Source: Rerun_MissingFiles    Description: Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_I4. Error: 2010-02-27 10:23:06.23    Code: 0xC00470C4    Source: Rerun_MissingFiles    Description: Casting expression "(SUBSTRING( @[system::machineName] , 12, 16 )))" from data type "DT_WSTR" to data type "DT_I4" failed with error code 0xC00470C2. End Error Can any one help me please? Thanks in advance.

Solution Deployment Problem (Administrative Framework Object Dependencies)

I'm having a problem with the deployment of a SharePoint workflow solution - hopefully someone can help me out.Here is the sequence of STSADM commands and their output from the console:C:\Workflows>stsadm -o deactivatefeature -name IL.SharePoint.Workflows -url http://localhost/ -forceOperation completed successfully.C:\Workflows>stsadm -o uninstallfeature -name IL.SharePoint.Workflows -forceOperation completed successfully.C:\Workflows>stsadm -o retractsolution -name IL.SharePoint.Workflows.wsp -localDeploymentSucceeded : server : The solution was successfully deployed.il.sharepoint.workflows.wsp: Operation completed with errors.C:\Workflows>stsadm -o deletesolution -name IL.SharePoint.Workflows.wspOperation completed successfully.C:\Workflows>stsadm -o addsolution -filename IL.SharePoint.Workflows.wspAn object in the SharePoint administrative framework, "SPSolutionLanguagePack Name=0 Parent=SPSolution Name=il.sharepoint.workflows.wsp", depends on other objects which do not exist.  Ensure that all of the objects dependencies are created and retry this operation.IL.SharePoint.Workflows.wsp: The Solution installation failed.Here is the related log info from the SharePoint logs:error-redacted.txtAs far as I can see, SP is attempting to insert a "SPSolutionLanguagePack" object into the configuration database, with an invalid foreign key reference

Problem running SSIS package in Jobs

Hello friendsI'm trying to run my SSIS packages as a step inside a sql agent job.This runs normally when the package uses the SAME database as used to create my job.The problem occurs if I create a job to run the package and this package must execute queries in another database. Probably authentication problem. So how to authenticate my package and the job runs normally ?thanks in advance.

Need a good solution on how to allow a user to upload multiple files


Im currently working on a project where i have a form that users fill out in order to submit issues.
My ask is to give the user the ability to upload multiple files together with the form submission and also go back each time they have an update and upload more if they need.
The way i have it right now is that i only have one FileUpload Control and once the Save button is clicked the file is save in the database together with the form info.

 I already has a separate table for the files with reference to the ticket but im just not sure how can i allow the user to submit as many files as they want in an easy way.

Any ideas?

Problem using SSIS to move DB2 data to Oracle



I will need your advice on a SSIS package which moves IBM DB2 (for z/OS V7) data to Oracle (AIX 10g). Thank you in advance.

Basically, this is a very simple SSIS package (created by SSIS import / export wizard) and just maps column by column. I used IBM OLEDB provider for DB2 to connect the source and Oracle Provider for OLEDB to connect to the destination.

The problem I ran into was that if the DB2 source value contains special (or invisible) characters then this value will be converted to a SPACE (' ') value on Oracle. To be more detailed, take the following example:

On DB2, value  000002D0000002D7 (EBCDIC HEX codes). This is actually a value of a column defined on DB2 as CHAR(8). Please note HEX '00' in EBCDIC stands for NULL and HEX 'D0' stands for character 'K';

On Oracle, this value will be stored as a SPACE (or a string of SPACEs) after data move.

On both DB2 and Oracle this column was defined as CHAR(8).

I guessed this was related to a code page issue, so I tried a few different code page to set for the source property (by setting the DefaultCodePage of the DataFlow source property), I used code page 37, 500, etc... All resulted in the same Oracle value.

The character set on DB2 is EBCDIC and the one on Oracle is ALT32UTF8. I guess it was the source provid

SSIS Script task design script problem



  I have placed a script task in my SSIS project, package.

When I click Design Script button, script editor is not opening, instead its returning to Control Flow. But no error.

 What is the problem?

I just created a new folder in D:\ drive with the name My SSIS. 


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend