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

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

Script tasks in SSIS 2005

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server


Can anybody tell me if we can use the C#.net while encoding in the script tasks components in BI 2005?

View Complete Post

More Related Resource Links

How to generate an excel and send it as email attachment in SSIS 2005 Script Task Component?


I am using a "Script Task" component. I am trying with following three simple steps:

1) Obtain the excel content in HTML

Dim reportInHtml As String = GenerateHTMLReport()

2) Generate an excel with below code:

Dim message As MailMessage

message = New MailMessage(fromAddress, toAddress, subject, String.Empty)

Using memoryStream As MemoryStream = New MemoryStream()

        Dim contentAsBytes As Byte() = Encoding.UTF8.GetBytes(reportInHtml)
        memoryStream.Write(contentAsBytes, 0, contentAsBytes.Length)
        memoryStream.Seek(0, SeekOrigin.Begin)

        Dim contentType As ContentType = New ContentType()
        contentType.MediaType = "application/ms-excel"
        contentType.Name = "Excel_Report.xls"

        Dim excelAttachment

SSIS 2005 - Multiple data flows from a Script task


I have a variable that the user sets to an excel file. Because the file can be of a different version of Excel, I'm testing the version first in a script task. Depending upon the version I then want to call a specific DataFlowTask and also want to set the connection Manager accordingly for that DataFlow task.

i.e. if XLS extension then use the DataFlowTask with the Excel OLEDB Provider setting the connection to the variable passed.

if XLSX extension then use the DataFlowTask that uses the Office OLDEDB Provider with the Extension of Excel 12.0 again using the Variable passed as the connection setting.

An example on how to achieve this would be good.


How to Call .Exe in Shared Folder using Script Task Component in SSIS 2005 ?


Hi friends,

I have a Shared Folder where Two .NET .Exes are placed.

My requirment is to call this two Exes using Script Task Component.

I am very bigginer in SSIS.

Please provide me Assistance to solve this issue.

if you need more information regarding problem, let me know.


C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

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

SSIS 2005 - Foreach loop container - Stopping the loop after processing X number of file?

I need to stop the Foreach loop container from processing more files in the folder than desired. Scenario:  If I only want 1 files processed, i need to stop the loop after it finds 1 file.  No matter what the contraints or tests or variables I set, the loop processes all the files in the folder.  This is bad because I need the value of the 1st mapped variable and not the last one it finds. I have tried counting records and setting variables used in the contraints but to no avail  Nothing seems to stop the loop. Please advise

Diffrences in SSIS 2005 and SSIS 2008?

Hi All,   How to convince interviewer in  major differences in ssis 2005 and ssis 2008.In my system I have ssis 2008.Can I install ssis 2005? How to install? Thanks CMK…

Using Foreach loop Container in SSIS 2005 package and scheduling the package using SQL job on 64-bit

I've an SSIS package 2005 which uses a for each loop container, this package runs fine when I run it on the local machine . My server is 64 bit SQL 2005 and I 've successfully deployed my package on the server both to the File system and SQL server. I've also set Run64bitruntime to false in my pacakge. Now I need schedule the package using SQL job. Since Microsoft Jet Provider 4.0 is not available for 64 bit, I had to write script to schedule the package. Here is my script. declare   @ssisstr varchar(8000) declare @returncode int set   @ssisstr = 'dtexec /sq Package1 /DE 123' EXEC   @returncode = xp_cmdshell @ssisstr select   @returncode I'm getting the following error when I execute the job. Could not load package "Package1" because of error 0xC0010014. Description: One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors. I'm getting the same error if I run this from commandline. Any help would be really appreciated. Thanks in advance. PARC

SSIS Script Task

I am trying get user input from a form created via script but the form flashes on the screen and then disappear...is there a special implementation for this?? The following code is in my main; FormIU   from = new FormIU();   from.Show();    

I need to create a script in SSIS which creates a data source that connects to an Access database.

I need to create a script in SSIS which creates a data source that connects to an Access database. The Access database file name needs to be set as a variable as it will change from month to month. I have no idea what I am doing can anybody give me some tips? Mr Shaw

Loading Decimal Number From Excel into SQL Server 2005 using SSIS Package

If you have an excel spreadsheet that displays a number .02 (because it is formated) but internally it is stored as .2654329 and you want to load .2654329 how can you do that in the SSIS package?  When we load the spreadsheet it is loading .02.  Is there some kind of option in the SSIS package to tell the package to load the internal number?lcerni

Need step by step help for Slowly Chaging Dimension In SSIS 2005

Hi I have never used Slowly Changing Dimension ,my manager told me that i  have to use use type 2 for this i am not sure what is business key and all,please help me step by step process I have a source table like this [dbo].[CapabilitySpecialty](  [SpecialtyCd] [varchar](30) NOT NULL,  [SpecialtyDesc] [varchar](256) NULL,  [CapabilityCd] [varchar](30) NULL,  [CapabilityDesc] [varchar](256) NULL,  [WorkgroupCd] [varchar](15) NULL,  [WorkgroupDesc] [varchar](100) NULL,  [WorkforceGPSCapabilityCd] [varchar](15) NULL,  [WorkforceDesc] [varchar](100) NULL Now  I have destination table looks like this [Price].[DimCapabilitySpecialty](  [CapabilitySpecialtyKey] [int] IDENTITY(1,1) NOT NULL,  [SpecialtyCd] [varchar](30) NOT NULL,  [SpecialtyDesc] [varchar](256) NULL,  [CapabilityCd] [varchar](30) NULL,  [CapabilityDesc] [varchar](256) NULL,  [WorkgroupCd] [varchar](15) NULL,  [WorkgroupDesc] [varchar](100) NULL,  [WorkforceGPSCapabilityCd] [varchar](15) NULL,  [WorkforceDesc] [varchar](100) NULL  

SSIS Package not logging all tasks when run as a job

We have few packages where we have enabled logging to msdb (sysssislog). And events selected are OnError, OnExecStatusChanged, OnPostExecute, OnPreExecute, OnTaskFailed, OnWarning. When running this package from BIDS solution, all the event log to the table. However, when it is deployed and run as a job only some of the events get logged to msdb. The package however does run correctly and the data is loaded fine. What we want is that every time a package runs, we should be able to see that each of the task has succeded or failed (like we used to in DTS) Could anyone advise on what the issue seems to be?

In SSIS 2005 how can I implement Slowly Changing Dimension Type 6

Hi I can't see SCD Type 6 in SSIS 2005 ,how do I get add  into my data flow task and if anybody can explain me step by step process for SCD type 6

BIDS wont let me edit script tasks, Package wont open without a "temp" folder in appdata???

I have a very stupid problem, because the way BIDS works is just hilariousy idiotic.    Short Story: 1) When i try to edit script tasks it will complain about "failed to create project at some random path in temp folder" and wont open anything. I cannot edit script tasks anymore. THe project is essentialy dead to me. REboot or restart wont help. I spent five hours on this project and i dont want to do all that work from scratch! 2) I cannot port my solution to antoher computer or place because it links to the files in appdata/local/temp folder of the pc for some reason. If i delete that folder or port the solution, it will complain about some file in temp folder called "3jews....tmp" and will say that solution is unopenable. Need help to resuce my project and be able to continue working on it!   Long Story: When you make a project, BIDS stores its files in a separate folder: a solution file, a project file, a package file etc. You might have thought, you can now carry your solution over by copying this folder. WRONG! The BIDS actually stores files of  the solution in /Users/name/AppData/Local/Temp folder! And It cannot open a solution/package/project without files from that folder, now what kind of idiotism is that?   It all started with BIDS not saving my script task changes, then telling me "cannot create project in folder

Insert error description in sysjobhistory from ssis script task

hi. in ssis i have script task that, in some situations, raise error (Dts.TaskResult = ScriptResults.Failure). But in sysjobhistory table there is only shor description what happend (Description: The script returned a failure result.). Is there any way that i can set some system variable to describe what is error so that description ens up in sysjobhistory table? in the end, i want to look in job activity monitor and see that job is "red" and see the description that couses this failure.

SSIS 2005 Logging different events under BIDS and Scheduler

I have a package that is working and logging correctly under BIDS 2005 (SS 2005). It logs what I want (in particular "Information" log messages) to a text file. But when I run the package under the scheduler, all it logs to the text file is "Package Started" and "Package Ended". How do I get it to log the same messages under the scheduler as under BIDS?
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