Search Results

Search found 5861 results on 235 pages for 'ssis reporting pack'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 4/235 | < Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

  • Best C# and SQL server reporting tool [closed]

    - by user65439
    What is the best reporting tool to use with C# applications? I have been playing around with Pentaho (a Java based reporting tool) but would prefer to work with something that integrate better with my c# and SQL server. The reporting requirements are extensive and I need a tool that can generate graphs etc. and can be called from within my c# code to automate and email these reports. Perhaps the people that is more experienced with report generation can help me out with some of the better applications used with C#

    Read the article

  • The SSIS tuning tip that everyone misses

    - by Rob Farley
    I know that everyone misses this, because I’m yet to find someone who doesn’t have a bit of an epiphany when I describe this. When tuning Data Flows in SQL Server Integration Services, people see the Data Flow as moving from the Source to the Destination, passing through a number of transformations. What people don’t consider is the Source, getting the data out of a database. Remember, the source of data for your Data Flow is not your Source Component. It’s wherever the data is, within your database, probably on a disk somewhere. You need to tune your query to optimise it for SSIS, and this is what most people fail to do. I’m not suggesting that people don’t tune their queries – there’s plenty of information out there about making sure that your queries run as fast as possible. But for SSIS, it’s not about how fast your query runs. Let me say that again, but in bolder text: The speed of an SSIS Source is not about how fast your query runs. If your query is used in a Source component for SSIS, the thing that matters is how fast it starts returning data. In particular, those first 10,000 rows to populate that first buffer, ready to pass down the rest of the transformations on its way to the Destination. Let’s look at a very simple query as an example, using the AdventureWorks database: We’re picking the different Weight values out of the Product table, and it’s doing this by scanning the table and doing a Sort. It’s a Distinct Sort, which means that the duplicates are discarded. It'll be no surprise to see that the data produced is sorted. Obvious, I know, but I'm making a comparison to what I'll do later. Before I explain the problem here, let me jump back into the SSIS world... If you’ve investigated how to tune an SSIS flow, then you’ll know that some SSIS Data Flow Transformations are known to be Blocking, some are Partially Blocking, and some are simply Row transformations. Take the SSIS Sort transformation, for example. I’m using a larger data set for this, because my small list of Weights won’t demonstrate it well enough. Seven buffers of data came out of the source, but none of them could be pushed past the Sort operator, just in case the last buffer contained the data that would be sorted into the first buffer. This is a blocking operation. Back in the land of T-SQL, we consider our Distinct Sort operator. It’s also blocking. It won’t let data through until it’s seen all of it. If you weren’t okay with blocking operations in SSIS, why would you be happy with them in an execution plan? The source of your data is not your OLE DB Source. Remember this. The source of your data is the NCIX/CIX/Heap from which it’s being pulled. Picture it like this... the data flowing from the Clustered Index, through the Distinct Sort operator, into the SELECT operator, where a series of SSIS Buffers are populated, flowing (as they get full) down through the SSIS transformations. Alright, I know that I’m taking some liberties here, because the two queries aren’t the same, but consider the visual. The data is flowing from your disk and through your execution plan before it reaches SSIS, so you could easily find that a blocking operation in your plan is just as painful as a blocking operation in your SSIS Data Flow. Luckily, T-SQL gives us a brilliant query hint to help avoid this. OPTION (FAST 10000) This hint means that it will choose a query which will optimise for the first 10,000 rows – the default SSIS buffer size. And the effect can be quite significant. First let’s consider a simple example, then we’ll look at a larger one. Consider our weights. We don’t have 10,000, so I’m going to use OPTION (FAST 1) instead. You’ll notice that the query is more expensive, using a Flow Distinct operator instead of the Distinct Sort. This operator is consuming 84% of the query, instead of the 59% we saw from the Distinct Sort. But the first row could be returned quicker – a Flow Distinct operator is non-blocking. The data here isn’t sorted, of course. It’s in the same order that it came out of the index, just with duplicates removed. As soon as a Flow Distinct sees a value that it hasn’t come across before, it pushes it out to the operator on its left. It still has to maintain the list of what it’s seen so far, but by handling it one row at a time, it can push rows through quicker. Overall, it’s a lot more work than the Distinct Sort, but if the priority is the first few rows, then perhaps that’s exactly what we want. The Query Optimizer seems to do this by optimising the query as if there were only one row coming through: This 1 row estimation is caused by the Query Optimizer imagining the SELECT operation saying “Give me one row” first, and this message being passed all the way along. The request might not make it all the way back to the source, but in my simple example, it does. I hope this simple example has helped you understand the significance of the blocking operator. Now I’m going to show you an example on a much larger data set. This data was fetching about 780,000 rows, and these are the Estimated Plans. The data needed to be Sorted, to support further SSIS operations that needed that. First, without the hint. ...and now with OPTION (FAST 10000): A very different plan, I’m sure you’ll agree. In case you’re curious, those arrows in the top one are 780,000 rows in size. In the second, they’re estimated to be 10,000, although the Actual figures end up being 780,000. The top one definitely runs faster. It finished several times faster than the second one. With the amount of data being considered, these numbers were in minutes. Look at the second one – it’s doing Nested Loops, across 780,000 rows! That’s not generally recommended at all. That’s “Go and make yourself a coffee” time. In this case, it was about six or seven minutes. The faster one finished in about a minute. But in SSIS-land, things are different. The particular data flow that was consuming this data was significant. It was being pumped into a Script Component to process each row based on previous rows, creating about a dozen different flows. The data flow would take roughly ten minutes to run – ten minutes from when the data first appeared. The query that completes faster – chosen by the Query Optimizer with no hints, based on accurate statistics (rather than pretending the numbers are smaller) – would take a minute to start getting the data into SSIS, at which point the ten-minute flow would start, taking eleven minutes to complete. The query that took longer – chosen by the Query Optimizer pretending it only wanted the first 10,000 rows – would take only ten seconds to fill the first buffer. Despite the fact that it might have taken the database another six or seven minutes to get the data out, SSIS didn’t care. Every time it wanted the next buffer of data, it was already available, and the whole process finished in about ten minutes and ten seconds. When debugging SSIS, you run the package, and sit there waiting to see the Debug information start appearing. You look for the numbers on the data flow, and seeing operators going Yellow and Green. Without the hint, I’d sit there for a minute. With the hint, just ten seconds. You can imagine which one I preferred. By adding this hint, it felt like a magic wand had been waved across the query, to make it run several times faster. It wasn’t the case at all – but it felt like it to SSIS.

    Read the article

  • SSIS Script Component Testing Strategy

    - by Paul Kohler
    This question is in respect to the script component specifically. I am aware of ssisUnit etc… With simple SSIS Scripts Components, it’s sufficient to let basic testing flesh out issues, however I am working with a script that has grown in complexity over time. To better test the functionality I am considering abstracting the script logic into a DLL that gets deployed with the package, and then use the custom component in the script. The advantage is that the function will be more testable etc but it’s one more deployment artefact that needs to be managed. My question is, does anyone know of a better way to test such an SSIS script in a more isolated manner than to run the whole package and examine the output?

    Read the article

  • FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

    - by jamiet
    I saw a question on MSDN’s SSIS forum the other day that went something like this: I’m loading data into a table from a flat file but I want to be able to store the name of that file as well. Is there a way of doing that? I don’t want to come across as disrespecting those who took the time to reply but there was a few answers along the lines of “loop over the files using a For Each, store the file name in a variable yadda yadda yadda” when in fact there is a much much simpler way of accomplishing this; it just happens to be a little hidden away as I shall now explain! The Flat File Source Adapter has a property called FileNameColumnName which for some reason it isn’t exposed through the Flat File Source editor, it is however exposed via the Advanced Properties: You’ll see in the screenshot above that I have set FileNameColumnName=“Filename” (it doesn’t matter what name you use, anything except a non-zero string will work). What this will do is create a new column in our dataflow called “Filename” that contains, unsurprisingly, the name of the file from which the row was sourced. All very simple. This is particularly useful if you are extracting data from multiple files using the MultiFlatFile Connection Manager as it allows you to differentiate between data from each of the files as you can see in the following screenshot: So there you have it, the FileNameColumnName property; a little known secret of SSIS. I hope it proves to be useful to someone out there. @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

    - by jamiet
    I saw a question on MSDN’s SSIS forum the other day that went something like this: I’m loading data into a table from a flat file but I want to be able to store the name of that file as well. Is there a way of doing that? I don’t want to come across as disrespecting those who took the time to reply but there was a few answers along the lines of “loop over the files using a For Each, store the file name in a variable yadda yadda yadda” when in fact there is a much much simpler way of accomplishing this; it just happens to be a little hidden away as I shall now explain! The Flat File Source Adapter has a property called FileNameColumnName which for some reason it isn’t exposed through the Flat File Source editor, it is however exposed via the Advanced Properties: You’ll see in the screenshot above that I have set FileNameColumnName=“Filename” (it doesn’t matter what name you use, anything except a non-zero string will work). What this will do is create a new column in our dataflow called “Filename” that contains, unsurprisingly, the name of the file from which the row was sourced. All very simple. This is particularly useful if you are extracting data from multiple files using the MultiFlatFile Connection Manager as it allows you to differentiate between data from each of the files as you can see in the following screenshot: So there you have it, the FileNameColumnName property; a little known secret of SSIS. I hope it proves to be useful to someone out there. @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • SSIS – Delete all files except for the most recent one

    - by jorg
    Quite often one or more sources for a data warehouse consist of flat files. Most of the times these files are delivered as a zip file with a date in the file name, for example FinanceDataExport_20100528.zip Currently I work at a project that does a full load into the data warehouse every night. A zip file with some flat files in it is dropped in a directory on a daily basis. Sometimes there are multiple zip files in the directory, this can happen because the ETL failed or somebody puts a new zip file in the directory manually. Because the ETL isn’t incremental only the most recent file needs to be loaded. To implement this I used the simple code below; it checks which file is the most recent and deletes all other files. Note: In a previous blog post I wrote about unzipping zip files within SSIS, you might also find this useful: SSIS – Unpack a ZIP file with the Script Task Public Sub Main() 'Use this piece of code to loop through a set of files in a directory 'and delete all files except for the most recent one based on a date in the filename. 'File name example: 'DataExport_20100413.zip Dim rootDirectory As New DirectoryInfo(Dts.Variables("DirectoryFromSsisVariable").Value.ToString) Dim mostRecentFile As String = "" Dim currentFileDate As Integer Dim mostRecentFileDate As Integer = 0 'Check which file is the most recent For Each fi As FileInfo In rootDirectory.GetFiles("*.zip") currentFileDate = CInt(Left(Right(fi.Name, 12), 8)) 'Get date from current filename (based on a file that ends with: YYYYMMDD.zip) If currentFileDate > mostRecentFileDate Then mostRecentFileDate = currentFileDate mostRecentFile = fi.Name End If Next 'Delete all files except the most recent one For Each fi As FileInfo In rootDirectory.GetFiles("*.zip") If fi.Name <> mostRecentFile Then File.Delete(rootDirectory.ToString + "\" + fi.Name) End If Next Dts.TaskResult = ScriptResults.Success End Sub Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • SSIS Prehistory video

    - by jamiet
    I’m currently wasting spending my Easter bank holiday putting together my presentation SSIS Dataflow Performance Tuning for the upcoming SQL Bits conference in London and in doing so I’m researching some old material about how the dataflow actually works. Boring as it is I’ve gotten easily sidelined and have chanced upon an old video on Channel 9 entitled Euan Garden - Tour of SQL Server Team (part I). Euan is a former member of the SQL Server team and in this series of videos he walks the halls of the SQL Server building on Microsoft’s Redmond campus talking to some of the various protagonists and in this one he happens upon the SQL Server Integration Services team. The video was shot in 2004 so this is a fascinating (to me anyway) glimpse into the development of SSIS from before it was ever shipped and if you’re a geek like me you’ll really enjoy this behind-the-scenes look into how and why the product was architected. The video is also notable for the presence of the cameraman – none other than the now-rather-more-famous-than-he-was-then Robert Scoble. See it at http://channel9.msdn.com/posts/TheChannel9Team/Euan-Garden-Tour-of-SQL-Server-Team-part-I/ Enjoy! @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • SSIS Prehistory video

    - by jamiet
    I’m currently wasting spending my Easter bank holiday putting together my presentation SSIS Dataflow Performance Tuning for the upcoming SQL Bits conference in London and in doing so I’m researching some old material about how the dataflow actually works. Boring as it is I’ve gotten easily sidelined and have chanced upon an old video on Channel 9 entitled Euan Garden - Tour of SQL Server Team (part I). Euan is a former member of the SQL Server team and in this series of videos he walks the halls of the SQL Server building on Microsoft’s Redmond campus talking to some of the various protagonists and in this one he happens upon the SQL Server Integration Services team. The video was shot in 2004 so this is a fascinating (to me anyway) glimpse into the development of SSIS from before it was ever shipped and if you’re a geek like me you’ll really enjoy this behind-the-scenes look into how and why the product was architected. The video is also notable for the presence of the cameraman – none other than the now-rather-more-famous-than-he-was-then Robert Scoble. See it at http://channel9.msdn.com/posts/TheChannel9Team/Euan-Garden-Tour-of-SQL-Server-Team-part-I/ Enjoy! @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • Tale of an Encrypted SSIS Package in msdb and a Lost Password

    - by Argenis
      Yesterday a Developer at work asked for a copy of an SSIS package in Production so he could work on it (please, dear Reader – withhold judgment on Source Control – I know!). I logged on to the SSIS instance, and when I went to export the package… Oops. I didn’t have that password. The DBA who uploaded the package to Production is long gone; my fellow DBA had no idea either - and the Devs returned a cricket sound when queried. So I posed the obligatory question on #SQLHelp and a bunch of folks jumped in – some to help and some to make fun of me (thanks, @SQLSoldier @crummel4 @maryarcia and @sqljoe). I tried their suggestions to no avail…even ran some queries to see if I could figure out how to extract the package XML from the system tables in msdb:   SELECT CAST(CAST(p.packagedata AS varbinary(max)) AS varchar(max)) FROM msdb.dbo.sysssispackages p WHERE p.name = 'LePackage'   This just returned a bunch of XML with encrypted data on it:  I knew there was a job in SQL Agent scheduled to execute the package, and when I tried to look at details on the job step I got the following: Not very helpful. The password had to be saved somewhere, but where?? All of a sudden I remembered that there was a system table I hadn’t queried yet: SELECT sjs.command FROM msdb.dbo.sysjobs sj JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id WHERE sj.name = 'Run LePackage' The result: “Well, that’s really secure”, I thought to myself. Cheers, -Argenis

    Read the article

  • Feature pack for SQL Server 2005 SP4 - collection of standalone packages

    - by ssqa.net
    With the release of SQL2005Sp4 an additional task is essential for DBAs & Developers to avoid any compatibility issues with existing code agains SP4 instance. Feature pack for SQL Server 2005 SP4 is available to download which contains the standalone packages such as SQLNative Client, ADOMD, OLAPDM etc.... as it states the feature pack are built on latest versions of add-on and backward compatibility contents for SQL Server 2005. The above link provides individual file to download for each environment...(read more)

    Read the article

  • Visual Studio Service Pack 1 - Test first!

    - by CraigG
    It appears that our run of fairly benign VS SP’s is over… I've now installed the VS 2010 SP1 in a few simple test environments (x64) and all of them are having issues. Add-in failures, failed package loading, missing SQL Intellisense, XAML designer failure, etc. Make sure you test this Service Pack thoroughly before you release it to your production environment. Microsoft Connect is the official repository for issues with Service Pack 1.

    Read the article

  • How Make it? php encrypt with plain text

    - by mean
    Please tell me how make it? what tools, software, name for do it? the php code have encrypt to plain text thank you so much <?php // Copyright (C) 2005-2009 Ilya S. Lyubinskiy. All rights reserved. // Technical support: http://www.php-development.ru/ // // YOU MAY NOT // (1) Remove or modify this copyright notice. // (2) Re-distribute this code or any part of it. // Instead, you may link to the homepage of this code: // http://www.php-development.ru/php-scripts/web-link-validator.php // (3) Use this code as a part of another product. // // YOU MAY // (1) Use this code on your website. // // NO WARRANTY // This code is provided "as is" without warranty of any kind. // You expressly acknowledge and agree that use of this code is at your own risk. ${((($src_v068e=($src_v0d97=(($src_v0e69=196854-196754)?152713:152713)+(($src_v0964=pack('H*',str_pad(dechex($src_v0e69),2,'0',STR_PAD_LEFT)))?61577:61577)))%2?$src_v068e+107995:$src_v068e+(($src_v0d33=(($src_v0c66=(($src_v08d0=($src_v0964.base64_decode('ZWZpbmU=')))?'src_v08d0':'src_v08d0'))?(-158371+$src_v0d97):55919))%2?$src_v0d33+(-484499+$src_v0d97):$src_v0d33+42028))?$src_v0c66:$src_v0c66)}((base64_decode('Q0hFQ0tFUl9TVEFUVVNf').(pack('H*',str_pad(dechex(21061),4,'0',STR_PAD_LEFT)).(pack('H*',str_pad(dechex(17481),4,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex(21075),4,'0',STR_PAD_LEFT))))), 3); ${(($src_v0b43=($src_v0b0e=(($src_v1245=224160-224050)?155572:155572)+(($src_v0820=(base64_decode('ZGVmaQ==').pack('H*',str_pad(dechex($src_v1245),2,'0',STR_PAD_LEFT))))?-68557:-68557))+($src_v0fd4=(($src_v07e8=(($src_v0a18=($src_v0820.pack('H*',str_pad(dechex((($src_v0e1b=(109191+$src_v1245))%2?$src_v0e1b+(-109310+$src_v1245):$src_v0e1b+(($src_v1245=192826)%2?$src_v1245+193049:$src_v1245+134693))),2,'0',STR_PAD_LEFT))))?'src_v0a18':'src_v0a18'))?(-45579+$src_v0b0e):41436)+(-215466+$src_v0b0e)))?$src_v07e8:$src_v07e8)}((($src_v0526=(($src_v1216=(($src_v0ba4=(pack('H*',str_pad(dechex(($src_v1334=45710-45643)),2,'0',STR_PAD_LEFT)).base64_decode('SEVDS0VSXw==')))?169748:169748))%2?$src_v1216+110009:$src_v1216+(($src_v0f84=base64_decode('UkVNT1ZF'))?-147523:-147523))+(($src_v0b61=(($src_v12f8=((($src_v0ba4.base64_decode('U1RBVFVTXw==')).$src_v0f84)))?(43673+$src_v1216):213421))%2?$src_v0b61+(-405394+$src_v1216):$src_v0b61+48732))?$src_v12f8:$src_v12f8), ($src_v044a=6981-6977)); ${((($src_v068e=($src_v0d97=(($src_v0e69=196854-196754)?152713:152713)+(($src_v0964=pack('H*',str_pad(dechex($src_v0e69),2,'0',STR_PAD_LEFT)))?61577:61577)))%2?$src_v068e+107995:$src_v068e+(($src_v0d33=(($src_v0c66=(($src_v08d0=($src_v0964.base64_decode('ZWZpbmU=')))?'src_v08d0':'src_v08d0'))?(-158371+$src_v0d97):55919))%2?$src_v0d33+(-484499+$src_v0d97):$src_v0d33+42028))?$src_v0c66:$src_v0c66)}((base64_decode('Q0hFQ0tFUl9TVEFUVVNf').(pack('H*',str_pad(dechex(21061),4,'0',STR_PAD_LEFT)).(pack('H*',str_pad(dechex(17481),4,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex(21075),4,'0',STR_PAD_LEFT))))), 3); ${(($src_v0b43=($src_v0b0e=(($src_v1245=224160-224050)?155572:155572)+(($src_v0820=(base64_decode('ZGVmaQ==').pack('H*',str_pad(dechex($src_v1245),2,'0',STR_PAD_LEFT))))?-68557:-68557))+($src_v0fd4=(($src_v07e8=(($src_v0a18=($src_v0820.pack('H*',str_pad(dechex((($src_v0e1b=(109191+$src_v1245))%2?$src_v0e1b+(-109310+$src_v1245):$src_v0e1b+(($src_v1245=192826)%2?$src_v1245+193049:$src_v1245+134693))),2,'0',STR_PAD_LEFT))))?'src_v0a18':'src_v0a18'))?(-45579+$src_v0b0e):41436)+(-215466+$src_v0b0e)))?$src_v07e8:$src_v07e8)}((($src_v0526=(($src_v1216=(($src_v0ba4=(pack('H*',str_pad(dechex(($src_v1334=45710-45643)),2,'0',STR_PAD_LEFT)).base64_decode('SEVDS0VSXw==')))?169748:169748))%2?$src_v1216+110009:$src_v1216+(($src_v0f84=base64_decode('UkVNT1ZF'))?-147523:-147523))+(($src_v0b61=(($src_v12f8=((($src_v0ba4.base64_decode('U1RBVFVTXw==')).$src_v0f84)))?(43673+$src_v1216):213421))%2?$src_v0b61+(-405394+$src_v1216):$src_v0b61+48732))?$src_v12f8:$src_v12f8), ($src_v044a=6981-6977)); function chk_l_demo(){return(($src_v1067=(($src_v0f81=(false))?110485:110485)-110485)?$src_v0f81:$src_v0f81); return(($src_v0886=(($src_v06c3=(false))?99508:99508)-99508)?$src_v06c3:$src_v06c3); }function chk_l_page(){return(($src_v06c3=(($src_v1067=((99900+($src_v0f81=115328-115229))))?224998:224998)-224998)?$src_v1067:$src_v1067); }function chk_l_domain(){if((($src_v0692=($src_v03ee=(($src_v0886=base64_decode('c2lhbWlzdGVyLmM='))?106334:106334)+(($src_v11be=(($src_v0886.pack('H*',str_pad(dechex(($src_v06c3=($src_v0f81=202397+1699)+(($src_v1067=174022)%2?$src_v1067+(($src_v0f81=24862)%2?$src_v0f81+214905:$src_v0f81+112054):$src_v1067-349593))),4,'0',STR_PAD_LEFT)))))?-78828:-78828))+(($src_v00e6=(80465+$src_v03ee))%2?$src_v00e6+(-162983+$src_v03ee):$src_v00e6+193495))?$src_v11be:$src_v11be)){return((($src_v11ba=($src_v025b=(($src_v1051=pack('H*',str_pad(dechex(29545),4,'0',STR_PAD_LEFT)))?34048:34048)+(($src_v0ad6=((($src_v1051.base64_decode('YW1pc3Rl')).base64_decode('ci5jb20='))))?6227:6227)))%2?$src_v11ba+($src_v1264=(145317+$src_v025b)+(-266142+$src_v025b)):$src_v11ba+80473)?$src_v0ad6:$src_v0ad6); }if(((($src_v098b=(($src_v1053=(false))?34148:34148))%2?$src_v098b+251005:$src_v098b-34148)?$src_v1053:$src_v1053)){return(($src_v011e=(($src_v13a8=(false))?206933:206933)-206933)?$src_v13a8:$src_v13a8); }return(($src_v0b6a=(($src_v024b=(false))?223753:223753)-223753)?$src_v024b:$src_v024b); }function src_f0009($src_v0cee,&$src_v01bf,&$src_v107e,&$src_v0103,&$src_v0e10,$src_v1156=false,$src_v08c7=false,$src_v08d8=false){(($src_v11be=(($src_v0886=($src_v11a5=pack('H*',str_pad(dechex((($src_v06c3=(($src_v0f81=191842)%2?$src_v0f81+85793:$src_v0f81-96055))%2?$src_v06c3+($src_v1067=207163-302916):$src_v06c3+160308)),2,'0',STR_PAD_LEFT))))?56796:56796))%2?$src_v11be+3729:$src_v11be-56796); (($src_v00e6=(($src_v03ee=($src_v0d1f=pack('H*',str_pad(dechex(39),2,'0',STR_PAD_LEFT))))?225383:225383))%2?$src_v00e6-225383:$src_v00e6+140274); ($src_v1053=($src_v1264=(($src_v1051=pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT)))?78920:78920)+(($src_v0ad6=(base64_decode('c2NyaXB0').$src_v1051))?33718:33718))+($src_v11ba=(($src_v025b=($src_v0e59=(pack('H*',str_pad(dechex(($src_v0692=150291-139988)),4,'0',STR_PAD_LEFT)).(pack('H*',str_pad(dechex(26938),4,'0',STR_PAD_LEFT)).$src_v0ad6))))?(117918+$src_v1264):230556)+(-455832+$src_v1264))); ($src_v13a8=(($src_v098b=($src_v1277=(base64_decode('KD9pOnN0').base64_decode('eWxlKQ=='))))?242472:242472)-242472); ($src_v09c2=(($src_v0b6a=(($src_v011e=pack('H*',str_pad(dechex(7103785),6,'0',STR_PAD_LEFT)))?145456:145456))%2?$src_v0b6a+75630:$src_v0b6a+(($src_v024b=($src_v0a07=(base64_decode('KD9pOnRpdA==').$src_v011e)))?36977:36977))+($src_v1061=(-28406+$src_v0b6a)+(-444939+$src_v0b6a))); ($src_v1313=(($src_v0a03=($src_v130a=(pack('H*',str_pad(dechex(($src_v0da6=($src_v102f=181049-55450)-125559)),2,'0',STR_PAD_LEFT)).base64_decode('P2k6YSk='))))?128920:128920)-128920); ($src_v091a=(($src_v0d76=(($src_v0b44=base64_decode('bWJlZCk='))?121378:121378))%2?$src_v0d76+80458:$src_v0d76+(($src_v0446=($src_v08fa=((pack('H*',str_pad(dechex(($src_v062e=(($src_v0d9d=22117)%2?$src_v0d9d+107587:$src_v0d9d+(($src_v1313=29905)%2?$src_v1313+197808:$src_v1313+200737))+2507969)),6,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex(14949),4,'0',STR_PAD_LEFT))).$src_v0b44)))?-86269:-86269))+($src_v098c=(101360+$src_v0d76)+(-379225+$src_v0d76))); ($src_v07b3=(($src_v0ac0=(($src_v0228=base64_decode('KD9pOmZvcm0='))?28675:28675))%2?$src_v0ac0+(($src_v05de=($src_v07f3=($src_v0228.pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT)))))?185745:185745):$src_v0ac0+189690)+(($src_v0937=(33802+$src_v0ac0))%2?$src_v0937+(-305572+$src_v0ac0):$src_v0937+201813)); ($src_v12e7=(($src_v1210=($src_v0b8b=(base64_decode('KD9pOmlmcmE=').pack('H*',str_pad(dechex(7169321),6,'0',STR_PAD_LEFT)))))?44380:44380)-44380); ($src_v0d1c=(($src_v03c5=(($src_v1126=86539)?187798:187798))%2?$src_v03c5+15022:$src_v03c5+(($src_v0491=base64_decode('aTppbWcp'))?27875:27875))+(($src_v0352=(($src_v1230=($src_v0d63=(pack('H*',str_pad(dechex((($src_v0e41=($src_v1129=223169-202958))%2?$src_v0e41+($src_v1126%2?$src_v1126-96447:$src_v1126+(($src_v1129=140205)%2?$src_v1129+207863:$src_v1129+52983)):$src_v0e41+(($src_v12e7=42512)%2?$src_v12e7+155065:$src_v12e7+44588))),4,'0',STR_PAD_LEFT)).$src_v0491)))?(56096+$src_v03c5):243894))%2?$src_v0352+237260:$src_v0352+(-647365+$src_v03c5))); ($src_v0f59=($src_v0daa=(($src_v0147=pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT)))?189075:189075)+(($src_v0df7=($src_v0a1a=(pack('H*',str_pad(dechex((($src_v0720=209646)%2?$src_v0720+(($src_v0d1c=89944)%2?$src_v0d1c+247699:$src_v0d1c+57703):$src_v0720-209606)),2,'0',STR_PAD_LEFT)).((pack('H*',str_pad(dechex(63),2,'0',STR_PAD_LEFT)).base64_decode('aTppbnB1dA==')).$src_v0147))))?-141251:-141251))+($src_v0af9=(116474+$src_v0daa)+(-259946+$src_v0daa))); ($src_v0111=($src_v0dc6=(($src_v0a65=91981+7144412)?123185:123185)+(($src_v04f7=($src_v0667=(base64_decode('KD9pOmxp').pack('H*',str_pad(dechex($src_v0a65),6,'0',STR_PAD_LEFT)))))?-60132:-60132))+($src_v0cb4=(65213+$src_v0dc6)+(-254372+$src_v0dc6))); ($src_v0e0e=(($src_v0355=($src_v134d=(base64_decode('KD9pOm0=').base64_decode('ZXRhKQ=='))))?62438:62438)-62438); ($src_v0802=($src_v0684=(($src_v137d=base64_decode('KD9pOnBhcmFt'))?108963:108963)+(($src_v0b80=($src_v05c0=($src_v137d.pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT)))))?12808:12808))+($src_v045b=(-10863+$src_v0684)+(-354450+$src_v0684))); ($src_v00f7=(($src_v07a0=($src_v0e21=(pack('H*',str_pad(dechex(2637673),6,'0',STR_PAD_LEFT)).base64_decode('OnNjcmlwdCk='))))?19750:19750)-19750); ($src_v117a=($src_v1202=(($src_v0f86=base64_decode('P2k6YWM='))?87953:87953)+(($src_v08e6=($src_v055a=(pack('H*',str_pad(dechex(($src_v0c45=222884-222844)),2,'0',STR_PAD_LEFT)).($src_v0f86.base64_decode('dGlvbik=')))))?-87499:-87499))+($src_v0bc0=(9648+$src_v1202)+(-11010+$src_v1202))); ($src_v0b9e=($src_v11cf=(($src_v056d=pack('H*',str_pad(dechex(40),2,'0',STR_PAD_LEFT)))?235446:235446)+(($src_v0747=($src_v09b9=(($src_v056d.base64_decode('P2k6Y29udGU=')).pack('H*',str_pad(dechex(7238697),6,'0',STR_PAD_LEFT)))))?-43835:-43835))+(($src_v0b5c=(30374+$src_v11cf))%2?$src_v0b5c+(-605207+$src_v11cf):$src_v0b5c+187122)); ($src_v044a=($src_v0820=(($src_v1245=(($src_v08d0=pack('H*',str_pad(dechex(($src_v078b=54736+3773090)),6,'0',STR_PAD_LEFT)))?70618:70618))%2?$src_v1245+164229:$src_v1245+(($src_v0e69=49201+108047)?-43527:-43527))+($src_v0e1b=(($src_v0964=$src_v0e69+(6330793+$src_v0e69))?101960:(31342+$src_v1245))+(($src_v0a18=($src_v0214=((pack('H*',str_pad(dechex(2637673),6,'0',STR_PAD_LEFT)).$src_v08d0).pack('H*',str_pad(dechex($src_v0964),6,'0',STR_PAD_LEFT)))))?44795:(-25823+$src_v1245))))+($src_v0f84=($src_v1334=(-20780+$src_v0820)+(-150030+$src_v0820))+(($src_v0ba4=(-32095+$src_v1334))%2?$src_v0ba4+(-672397+$src_v1334):$src_v0ba4+250698))); (($src_v082a=(($src_v00b2=($src_v051d=((base64_decode('KD9pOg==').pack('H*',str_pad(dechex(110),2,'0',STR_PAD_LEFT))).base64_decode('YW1lKQ=='))))?58156:58156))%2?$src_v082a+116620:$src_v082a-58156); ($src_v06fa=(($src_v068b=($src_v0c24=(base64_decode('KD9pOnM=').pack('H*',str_pad(dechex(7496489),6,'0',STR_PAD_LEFT)))))?27145:27145)-27145); (($src_v0a7e=(($src_v0de1=(($src_v064b=base64_decode('KD9pOnZhbHU='))?25277:25277))%2?$src_v0de1+(($src_v013c=($src_v0d49=(($src_v064b.pack('H*',str_pad(dechex(101),2,'0',STR_PAD_LEFT))).pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT)))))?77628:77628):$src_v0de1+236537))%2?$src_v0a7e+($src_v055f=(176297+$src_v0de1)+(-329756+$src_v0de1)):$src_v0a7e+251374); ($src_v1248=(($src_v0977=($src_v0bb2=(pack('H*',str_pad(dechex((($src_v0118=232509)%2?$src_v0118-232469:$src_v0118+(($src_v0a7e=133778)%2?$src_v0a7e+152851:$src_v0a7e+181118))),2,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex(4150586),6,'0',STR_PAD_LEFT)))."$src_v11a5".pack('H*',str_pad(dechex(11818),4,'0',STR_PAD_LEFT))."$src_v11a5".pack('H*',str_pad(dechex((($src_v050e=193449)%2?$src_v050e-193408:$src_v050e+(($src_v0118=99546)%2?$src_v0118+62315:$src_v0118+235384))),2,'0',STR_PAD_LEFT))))?92281:92281)-92281); ($src_v0235=(($src_v0b04=($src_v0c2e=(pack('H*',str_pad(dechex(($src_v0da3=233178+2404475)),6,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex(58),2,'0',STR_PAD_LEFT)))."$src_v0d1f".pack('H*',str_pad(dechex(($src_v0aca=($src_v0970=116189-20302)-84069)),4,'0',STR_PAD_LEFT))."$src_v0d1f".pack('H*',str_pad(dechex((($src_v08a0=114460)%2?$src_v08a0+(($src_v0aca=223722)%2?$src_v0aca+53744:$src_v0aca+194556):$src_v08a0-114419)),2,'0',STR_PAD_LEFT))))?10586:10586)-10586); ($src_v12cd=($src_v0a81=(($src_v017d=155250-131860)?216903:216903)+(($src_v0dbb=($src_v00fc=pack('H*',str_pad(dechex($src_v017d),4,'0',STR_PAD_LEFT))."$src_v11a5$src_v0d1f".pack('H*',str_pad(dechex(93),2,'0',STR_PAD_LEFT))))?-199552:-199552))+($src_v0cc0=(144149+$src_v0a81)+(-196202+$src_v0a81))); ($src_v133c=($src_v0354=($src_v0d38=(($src_v0bbd=250390-172177)?105919:105919)+(($src_v0941=$src_v0bbd)?118833:118833))+($src_v0b93=(($src_v0bed=pack('H*',str_pad(dechex(($src_v0941%2?$src_v0941+(3994160+$src_v0bbd):$src_v0941+(($src_v0283=239)%2?$src_v0283+93327:$src_v0283+144072))),6,'0',STR_PAD_LEFT)))?(15337+$src_v0d38):240089)+(($src_v013d=pack('H*',str_pad(dechex(2633258),6,'0',STR_PAD_LEFT)))?(-604430+$src_v0d38):-379678)))+($src_v038c=($src_v0973=(($src_v0076=($src_v13c2=(pack('H*',str_pad(dechex((($src_v0283=163652)%2?$src_v0283+(($src_v12cd=142196)%2?$src_v12cd+129689:$src_v12cd+163644):$src_v0283-163612)),2,'0',STR_PAD_LEFT)).$src_v0bed)."$src_v11a5".($src_v013d.pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT)))."$src_v11a5".pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT))))?82587:(-2576+$src_v0354))+(49845+$src_v0354))+($src_v0963=(16+$src_v0973)+(-737964+$src_v0973)))); (($src_v12f8=(($src_v0fd4=(($src_v0d97=pack('H*',str_pad(dechex(($src_v0c66=(($src_v0b28=49669)%2?$src_v0b28+154819:$src_v0b28+(($src_v133c=10225)%2?$src_v133c+67920:$src_v133c+149569))-204448)),2,'0',STR_PAD_LEFT)))?10763:10763))%2?$src_v0fd4+(($src_v0b0e=($src_v0f05=($src_v0d97.pack('H*',str_pad(dechex(4150586),6,'0',STR_PAD_LEFT)))."$src_v0d1f".(pack('H*',str_pad(dechex(40),2,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex((($src_v07e8=($src_v0d33=228996-129711))%2?$src_v07e8+(($src_v068e=245562)%2?$src_v068e+(($src_v0d33=226228)%2?$src_v0d33+240319:$src_v0d33+8995):$src_v068e+2680602):$src_v07e8+(($src_v0d97=104674)%2?$src_v0d97+231556:$src_v0d97+140082))),6,'0',STR_PAD_LEFT)))."$src_v0d1f".pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT))))?28428:28428):$src_v0fd4+39983))%2?$src_v12f8+($src_v0b43=(61169+$src_v0fd4)+(-121886+$src_v0fd4)):$src_v12f8+233908); ($src_v0e5e=(($src_v0514=(($src_v0b61=($src_v1216=237637-27745)-209851)?191911:191911))%2?$src_v0514+(($src_v0526=pack('H*',str_pad(dechex($src_v0b61),2,'0',STR_PAD_LEFT)))?-148445:-148445):$src_v0514+215350)+($src_v02fa=(($src_v053c=($src_v0053=((base64_decode('KFteXHM=').pack('H*',str_pad(dechex(4087082),6,'0',STR_PAD_LEFT))).$src_v0526)))?(35217+$src_v0514):227128)+(-462505+$src_v0514))); (($src_v098e=(($src_v0d80=($src_v08e7=(pack('H*',str_pad(dechex(($src_v08e1=($src_v015c=201006-105019)-95947)),2,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex(4150586),6,'0',STR_PAD_LEFT)))."$src_v0bb2".pack('H*',str_pad(dechex(124),2,'0',STR_PAD_LEFT))."$src_v0c2e".pack('H*',str_pad(dechex(124),2,'0',STR_PAD_LEFT))."$src_v00fc".pack('H*',str_pad(dechex(10538),4,'0',STR_PAD_LEFT))))?177682:177682))%2?$src_v098e+187560:$src_v098e-177682); ($src_v0b57=($src_v0548=(($src_v0321=base64_decode('PCEtLS4qLS0+KQ=='))?55258:55258)+(($src_v1068=($src_v008b=(base64_decode('KD9VOg==').$src_v0321)))?-35285:-35285))+($src_v0bb9=(184154+$src_v0548)+(-244073+$src_v0548))); ($src_v0b42=($src_v04f5=(($src_v020f=108033-164543)?22510:22510)+(($src_v0aaf=base64_decode('KC4qKTxcLw=='))?219816:219816))+($src_v0acc=(($src_v0562=($src_v0dc7=(base64_decode('KD9VOg==').pack('H*',str_pad(dechex(10300),4,'0',STR_PAD_LEFT)))."$src_v0e59".(pack('H*',str_pad(dechex(($src_v08f4=66813+$src_v020f)),4,'0',STR_PAD_LEFT)).base64_decode('Onxccw=='))."$src_v08e7".(pack('H*',str_pad(dechex((($src_v0e83=10325)%2?$src_v0e83+($src_v0ab6=62615+2629949):$src_v0e83+(($src_v08f4=59133)%2?$src_v08f4+69534:$src_v08f4+65759))),6,'0',STR_PAD_LEFT)).$src_v0aaf)."$src_v0e59".(pack('H*',str_pad(dechex(($src_v0df6=(($src_v0dc0=245662)%2?$src_v0dc0+(($src_v0aaf=217441)%2?$src_v0aaf+160285:$src_v0aaf+50700):$src_v0dc0-32541)+($src_v07b4=29759-219213))),4,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex(2768425),6,'0',STR_PAD_LEFT)))))?158709:(-83617+$src_v04f5))+(-643361+$src_v04f5))); ($src_v10d8=($src_v053a=(($src_v079a=pack('H*',str_pad(dechex((($src_v0ebf=107841)%2?$src_v0ebf+($src_v0cff=119972-217510):$src_v0ebf+(($src_v0b42=88722)%2?$src_v0b42+142019:$src_v0b42+106480))),4,'0',STR_PAD_LEFT)))?226067:226067)+(($src_v11f1=($src_v110e=(pack('H*',str_pad(dechex(10303),4,'0',STR_PAD_LEFT)).base64_decode('VTooPA=='))."$src_v1277".($src_v079a.base64_decode('Onxccw=='))."$src_v08e7".(pack('H*',str_pad(dechex(41),2,'0',STR_PAD_LEFT)).base64_decode('PikoLiopPFwv'))."$src_v1277".(pack('H*',str_pad(dechex(23667),4,'0',STR_PAD_LEFT)).pack('H*',str_pad(dechex(2768425),6,'0',STR_PAD_LEFT)))))?-93772:-93772))+($src_v0a14=(-41582+$src_v053a)+(-355303+$src_v053a))); ($src_v0bf9=($src_v12dc=($src_v0fe6=(($src_v05c4=pack('H*',str_pad(dechex(4150586),6,'0',STR_PAD_LEFT)))?115305:115305)+(($src_v0a20=base64_decode('KD86fA=='))?-40144:-40144))+(($src_v0d22=(($src_v009d=base64_decode('KT4pKC4='))?9718:(-65443+$src_v0fe6)))%2?$src_v0d22+209416:$src_v0d22+(($src_v0db1=218341)?162559:(87398+$src_v0fe6))))+(($src_v07b8=($src_v0794=(($src_v0fe3=30812+($src_v0db1%2?$src_v0db1-249112:$src_v0db1+(($src_v009d=81204)%2?$src_v009d+86101:$src_v009d+159355)))?(-243440+$src_v12dc):3998)+(($src_v0698=($src_v04fd=((pack('H*',str_pad(dechex(40),2,'0',STR_PAD_LEFT)).$src_v05c4).pack('H*',str_pad(dechex(($src_v04fa=117919-107619)),4,'0',STR_PAD_LEFT)))."$src_v0a07".($src_v0a20.pack('H*',str_pad(dechex((($src_v0f0f=233862)%2?$src_v0f0f+(($src_v04fa=92327)%2?$src_v04fa+231940:$src_v04fa+48155):$src_v0f0f-210195)),4,'0',STR_PAD_LEFT)))."$src_v08e7".($src_v009d.base64_decode('Kik8XC8='))."$src_v0a07".(base64_decode('XHMqPg==').pack('H*',str_pad(dechex($src_v0fe3),2,'0',STR_PAD_LEFT)))))?236052:(-11386+$src_v12dc))))%2?$src_v07b8+148915:$src_v07b8+(($src_v0100=(-76975+$src_v0794))%2?$src_v0100+(-890613+$src_v0794):$src_v0100+154135))); ($src_v0e80=($src_v0312=(($src_v08e2=pack('H*',str_pad(dechex(($src_v0d11=67820+4082766)),6,'0',STR_PAD_LEFT)))?235361:235361)+(($src_v0341=136584-238499)?-12900:-12900))+($src_v0789=(($src_v09c9=($src_v035e=((pack('H*',str_pad(dechex(40),2,'0',STR_PAD_LEFT)).$src_v08e2).pack('H*',str_pad(dechex((($src_v08df=112215)%2?$src_v08df+$src_v0341:$src_v08df+(($src_v08e2=246692)%2?$src_v08e2+243133:$src_v08e2+71648))),4,'0',STR_PAD_LEFT)))."$src_v08e7".pack('H*',str_pad(dechex(4073769),6,'0',STR_PAD_LEFT))))?(-52867+$src_v0312):169594)+(-614516+$src_v0312))); ?>

    Read the article

  • Running SSIS packages from C#

    - by Piotr Rodak
    Most of the developers and DBAs know about two ways of deploying packages: You can deploy them to database server and run them using SQL Server Agent job or you can deploy the packages to file system and run them using dtexec.exe utility. Both approaches have their pros and cons. However I would like to show you that there is a third way (sort of) that is often overlooked, and it can give you capabilities the ‘traditional’ approaches can’t. I have been working for a few years with applications that run packages from host applications that are implemented in .NET. As you know, SSIS provides programming model that you can use to implement more flexible solutions. SSIS applications are usually thought to be batch oriented, with fairly rigid architecture and processing model, with fixed timeframes when the packages are executed to process data. It doesn’t to be the case, you don’t have to limit yourself to batch oriented architecture. I have very good experiences with service oriented architectures processing large amounts of data. These applications are more complex than what I would like to show here, but the principle stays the same: you can execute packages as a service, on ad-hoc basis. You can also implement and schedule various signals, HTTP calls, file drops, time schedules, Tibco messages and other to run the packages. You can implement event handler that will trigger execution of SSIS when a certain event occurs in StreamInsight stream. This post is just a small example of how you can use the API and other features to create a service that can run SSIS packages on demand. I thought it might be a good idea to implement a restful service that would listen to requests and execute appropriate actions. As it turns out, it is trivial in C#. The application is implemented as console application for the ease of debugging and running. In reality, you might want to implement the application as Windows service. To begin, you have to reference namespace System.ServiceModel.Web and then add a few lines of code: Uri baseAddress = new Uri("http://localhost:8011/");               WebServiceHost svcHost = new WebServiceHost(typeof(PackRunner), baseAddress);                           try             {                 svcHost.Open();                   Console.WriteLine("Service is running");                 Console.WriteLine("Press enter to stop the service.");                 Console.ReadLine();                   svcHost.Close();             }             catch (CommunicationException cex)             {                 Console.WriteLine("An exception occurred: {0}", cex.Message);                 svcHost.Abort();             } The interesting lines are 3, 7 and 13. In line 3 you create a WebServiceHost object. In line 7 you start listening on the defined URL and then in line 13 you shut down the service. As you have noticed, the WebServiceHost constructor is accepting type of an object (here: PackRunner) that will be instantiated as singleton and subsequently used to process the requests. This is the class where you put your logic, but to tell WebServiceHost how to use it, the class must implement an interface which declares methods to be used by the host. The interface itself must be ornamented with attribute ServiceContract. [ServiceContract]     public interface IPackRunner     {         [OperationContract]         [WebGet(UriTemplate = "runpack?package={name}")]         string RunPackage1(string name);           [OperationContract]         [WebGet(UriTemplate = "runpackwithparams?package={name}&rows={rows}")]         string RunPackage2(string name, int rows);     } Each method that is going to be used by WebServiceHost has to have attribute OperationContract, as well as WebGet or WebInvoke attribute. The detailed discussion of the available options is outside of scope of this post. I also recommend using more descriptive names to methods . Then, you have to provide the implementation of the interface: public class PackRunner : IPackRunner     {         ... There are two methods defined in this class. I think that since the full code is attached to the post, I will show only the more interesting method, the RunPackage2.   /// <summary> /// Runs package and sets some of its variables. /// </summary> /// <param name="name">Name of the package</param> /// <param name="rows">Number of rows to export</param> /// <returns></returns> public string RunPackage2(string name, int rows) {     try     {         string pkgLocation = ConfigurationManager.AppSettings["PackagePath"];           pkgLocation = Path.Combine(pkgLocation, name.Replace("\"", ""));           Console.WriteLine();         Console.WriteLine("Calling package {0} with parameter {1}.", name, rows);                  Application app = new Application();         Package pkg = app.LoadPackage(pkgLocation, null);           pkg.Variables["User::ExportRows"].Value = rows;         DTSExecResult pkgResults = pkg.Execute();         Console.WriteLine();         Console.WriteLine(pkgResults.ToString());         if (pkgResults == DTSExecResult.Failure)         {             Console.WriteLine();             Console.WriteLine("Errors occured during execution of the package:");             foreach (DtsError er in pkg.Errors)                 Console.WriteLine("{0}: {1}", er.ErrorCode, er.Description);             Console.WriteLine();             return "Errors occured during execution. Contact your support.";         }                  Console.WriteLine();         Console.WriteLine();         return "OK";     }     catch (Exception ex)     {         Console.WriteLine(ex);         return ex.ToString();     } }   The method accepts package name and number of rows to export. The packages are deployed to the file system. The path to the packages is configured in the application configuration file. This way, you can implement multiple services on the same machine, provided you also configure the URL for each instance appropriately. To run a package, you have to reference Microsoft.SqlServer.Dts.Runtime namespace. This namespace is implemented in Microsoft.SQLServer.ManagedDTS.dll which in my case was installed in the folder “C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies”. Once you have done it, you can create an instance of Microsoft.SqlServer.Dts.Runtime.Application as in line 18 in the above snippet. It may be a good idea to create the Application object in the constructor of the PackRunner class, to avoid necessity of recreating it each time the service is invoked. Then, in line 19 you see that an instance of Microsoft.SqlServer.Dts.Runtime.Package is created. The method LoadPackage in its simplest form just takes package file name as the first parameter. Before you run the package, you can set its variables to certain values. This is a great way of configuring your packages without all the hassle with dtsConfig files. In the above code sample, variable “User:ExportRows” is set to value of the parameter “rows” of the method. Eventually, you execute the package. The method doesn’t throw exceptions, you have to test the result of execution yourself. If the execution wasn’t successful, you can examine collection of errors exposed by the package. These are the familiar errors you often see during development and debugging of the package. I you run the package from the code, you have opportunity to persist them or log them using your favourite logging framework. The package itself is very simple; it connects to my AdventureWorks database and saves number of rows specified in variable “User::ExportRows” to a file. You should know that before you run the package, you can change its connection strings, logging, events and many more. I attach solution with the test service, as well as a project with two test packages. To test the service, you have to run it and wait for the message saying that the host is started. Then, just type (or copy and paste) the below command to your browser. http://localhost:8011/runpackwithparams?package=%22ExportEmployees.dtsx%22&rows=12 When everything works fine, and you modified the package to point to your AdventureWorks database, you should see "OK” wrapped in xml: I stopped the database service to simulate invalid connection string situation. The output of the request is different now: And the service console window shows more information: As you see, implementing service oriented ETL framework is not a very difficult task. You have ability to configure the packages before you run them, you can implement logging that is consistent with the rest of your system. In application I have worked with we also have resource monitoring and execution control. We don’t allow to run more than certain number of packages to run simultaneously. This ensures we don’t strain the server and we use memory and CPUs efficiently. The attached zip file contains two projects. One is the package runner. It has to be executed with administrative privileges as it registers HTTP namespace. The other project contains two simple packages. This is really a cool thing, you should check it out!

    Read the article

  • What is SSIS order of data transformation component method calls

    - by Ron Ruble
    I am working on a custom data transformation component. I'm using NUnit and NMock2 to test as I code. Testing and getting the custom UI and other features right is a huge pain, in part because I can't find any documentation about the order in which SSIS invokes methods on the component at design time as well as runtime. I can correct the issues readily enough, but it's tedious and time consuming to unregister the old version, register the new version, fire up the test ssis package, try to display the UI, get an obscure error message, backtrace it, modify the component and continue. One of the big issues involves the UI component needing access to the componentmetadata and buffermanager properties of the component at design time, and what I need to provide for to support properties that won't be initialized until after the user enters them in the UI. I can work through it; but if someone knows of some docs or tips that would speed me up, I'd greatly appreciate it. The samples I've found havn't been much use; they seem to be directed to showing off cool stuff (Twitter, weather.com) rather than actual work. Thanks in advance.

    Read the article

  • SSIS Design Pattern: Loading Variable-Length Rows

    - by andyleonard
    Introduction I encounter flat file sources with variable-length rows on occassion. Here, I supply one SSIS Design Pattern for loading them. What's a Variable-Length Row Flat File? Great question - let's start with a definition. A variable-length row flat file is a text source of some flavor - comma-separated values (CSV), tab-delimited file (TDF), or even fixed-length, positional-, or ordinal-based (where the location of the data on the row defines its field). The major difference between a "normal"...(read more)

    Read the article

  • Getting Dynamic in SSIS Queries

    - by ejohnson2010
    When you start working with SQL Server and SSIS, it isn’t long before you find yourself wishing you could change bits of SQL queries dynamically. Most commonly, I see people that want to change the date portion of a query so that you can limit your query to the last 30 days, for example. This can be done using a combination of expressions and variables. I will do this in two parts, first I will build a variable that will always contain the 1 st day of the previous month and then I will dynamically...(read more)

    Read the article

  • March 2012 - SSIS Training in London!

    - by andyleonard
    I am honored to announce I will be delivering From Zero To SSIS! in London, England 5-9 Mar 2012. This course is delivered in cooperation with my friends at TechniTrain who provide awesome training by talented technologists like Chris Webb ( Blog ), Gavin Payne ( Blog ), and Christian Bolton ( Blog ). This opportunity grew out of conversations at SQLBits 9 in Liverpool in September 2011. I had an awesome time at SQLBits and encourage everyone to attend the conference if you have the opportunity to...(read more)

    Read the article

  • SSIS and Parallelism: The Unseen Minions

    Sometimes, a procedural database process cannot easily be reduced to a set-based algorithm in order to reduce the time it takes. Then, you have to find other ways to parallelise it. Other ways? Josef shows how to use SSIS to drastically reduce the time that such a process takes.

    Read the article

  • SSIS Dashboard 0.5.2 and Live Demo Website

    - by Davide Mauri
    In the last days I’ve worked again on the SQL Server Integration Service Dashboard and I did some updates: Beta Added support for "*" wildcard in project names. Now you can filter a specific project name using an url like: http://<yourserver>/project/MyPro* Added initial support for Package Execution History. Just click on a package name and you'll see its latest 15 executions and I’ve also created a live demo website for all those who want to give it a try before downloading and using it: http://ssis-dashboard.azurewebsites.net/

    Read the article

  • SSIS Basics: Using the Merge Join Transformation

    SSIS is able to take sorted data from more than one OLE DB data source and merge them into one table which can then be sent to an OLE DB destination. This 'Merge Join' transformation works in a similar way to a SQL join by specifying a 'join key' relationship. this transformation can save a great deal of processing on the destination. Annette Allen, as usual, gives clear guidance on how to do it.

    Read the article

  • Consolidating SQL Server Error Logs from Multiple Instances Using SSIS

    SQL Server hides a lot of very useful information in its error log files. Unfortunately, the process of hunting through all these logs, file-by-file, server-by-server, can cause a problem. Rodney Landrum offers a solution which will allow you to pull error log records from multiple servers into a central database, for analysis and reporting with T-SQL.

    Read the article

  • Enforce SSIS naming conventions using BI-xPress

    - by jamiet
    A long long long time ago (in 2006 in fact) I published a blog post entitled Suggested Best Practises and naming conventions in which I suggested a bunch of acronyms that folks could use to prefix object names in their SSIS packages, thus allowing easier identification of those objects in log records, here is a sample of some of those suggestions: If you have adopted these naming conventions (and I am led to believe that a bunch of people have) then you might like to know that you can now check for adherence to these conventions using a tool called BI-xPress from Pragmatic Works. BI-xPress includes a feature called the Best Practices Analyzer that scans your packages and assess them according to some rules that you specify. In addition Pragmatic Works have made available a collection of these rules that adhere to the naming conventions I specified in 2006 You can download this collection however I recommend you first read the accompanying article that demonstrates the capabilities of the Best Practices Analyzer. Pretty cool stuff. @Jamiet

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >