Integration Services :: SSIS View Code After Encrypting

Sep 15, 2015

Before we release an SSIS package into our test environment and then eventually into our production environment, we  set the package protection level to "EncryptAllWithPassword"

The "View Code" feature is a nice tool to find things that might be buried in the package somewhere (e.g an complex expression to a variable).

PROBLEM : After one sets the package protection level to "EncryptAllWithPassword", one cannot see the xml source code any longer. It's like compiling and saving cs to the bin. Is their a way to view the code again??

View 2 Replies


Integration Services :: SSIS Package - Create Different TXT File For Each Code

Jun 25, 2015

EDate Datetime,
Code varchar(255),
Cdate int,
Price int
drop table Test

[Code] ....

I have this Query and the below output:

EDate Code CDate Price
2015-06-24  RX 20150701 22
2015-06-24  RX 20150701 28
2015-06-24  RX 20150701 43

[Code] ....

Now the task is to create  SSIS package which will create different .txt file for each Code

1) RX20150624.txt
2015-06-24 00:00:00.000 RX 20150701 22
2015-06-24 00:00:00.000 RX 20150701 28
2015-06-24 00:00:00.000 RX 20150701 43

2) NG20150623.txt
2015-06-23 00:00:00.000 NG 20150701 43

3) HO20150624.txt
2015-06-24 00:00:00.000 HO 20150701 43
And so on..

But the requirement is to have a dynamic query where we can have more number of Codes or less number of codes and similarly the package should generate dynamic text files, one .txt file per code. What is the best way to create a package which can meet the above requirement?

View 6 Replies View Related

Integration Services :: How To Execute Custom Class Library Code Through SSIS

Jun 11, 2015

I have a requirement in which i have to create a custom .net class library for Ex:-I retrieve password(s) from a thrid party component. Below is what i am doing.

(1) Created a custom class library which reads a custom .xml file from a drive Ex:- "D:MyAppMYAppCofig.xml" and sets to my properties of my custom class library and inside it i created an instance of third party component's class and passed these values. Since i need to use this .net custom class library both in web and ssis/database side i am using this custom .xml file.

(2) After validating passed data (properties set in custom .net class library) the thrid party component instance object created in my custom .net class libraty returs a password to me own custom .net class libray.

(3) This password I use in my web app for connecting to database. This code is working fine.

(4) My question is how to execute a custom .net class library code through ssis and to use the my same custom .net class library and pass the password to my SSIS component / taks so that that code block also uses the returned password to connect and do any needed tasks? In other words how to use custom .net class library from SSIS.

My Environment is as follows:-
SQL Server is : 2008 R2
VS.NET 2013

View 5 Replies View Related

Integration Services :: SSIS 2012 Script Component Binary Code Not Found

Apr 21, 2015

After adding Service Reference  to WebService, the Script Component has Binary Code not found, red circle not showing these are the steps I followed:

1) Add Script Component as Source
2) Add 3 x Output Columns Col1,2 and 3
3) Add HTTP Connection URL>..Binary Code not found, red circle showing
4) Add test code to Sub CreateNewOutputRows     Dim i As Integer = 6       Binary Code not found, red circle not showing
5) Add Service Reference URL...Binary Code not found, red circle showing again

Should just adding Service Reference cause Binary Code not found, red circle to appear. I have to set  precompilescripttobinary option , however cannot see this option in 2012

View 3 Replies View Related

Integration Services :: Code To Show StartDateTime And EndDateTime In Body Of Email In SSIS

Nov 23, 2015

Code to show the package StartDateTime and EndDateTime in body of the email in ssis send email task.

View 3 Replies View Related

Integration Services :: Unable To Get Return Code Executing SSIS Package From Stored Procedure?

Jun 11, 2015

We are executing a SSIS package using a xp_cmdshell command in a SP as shown below. This package does consumes time to execute almost 90 minutes and does get executed successfully too. But the strange thing is we don't get the result in @result variable just because somehow the next sql statement after the below highlighted statement doesn't get executed at all.  After checking execution stats for the SP using the query attached below we observed that somehow the SP vanishes out of the execution stats for the server.

 SELECT @cmd = 'dtexec /FILE "D:Program FilesMicrosoft SQL Server100DTSPackages.....PopulateReport.dtsx"'          
  SELECT @cmd = @cmd + ' /Decrypt T@!0er '          
  SELECT @cmd = @cmd + ' /set package.variables[vAppID].Value;' + CONVERT(VARCHAR(10),@appId)          
  SELECT @cmd = @cmd + ' /set package.variables[vDBName].Value;' + '"' + @db + '"'          
  SELECT @cmd = @cmd + ' /set package.variables[vBuildMFF].Value;' + CONVERT(VARCHAR(10),@BuildMFF)          

View 6 Replies View Related

Integration Services :: Create Flat File From Stored Proc And View Using SSIS

Aug 25, 2015

I have a Stored proc which on execution, will generate data in the view .

My requirement is using SSIS, how can I create a Flat file pointing to this view ?

View 2 Replies View Related

Access C# Code From Integration Services

Apr 26, 2007

I have an application in C# in which i have few events like download,Import and I have written all the code for that and its working fine but I have to schedule these processes so that I need not to run it manually daily. I am trying to write an Integration services package through which I could handle the required event, without rewriting the whole code.

Please Reply.



View 4 Replies View Related

Integration Services :: C# Code To Create File From Variable Value?

May 5, 2015

My Execute SQL Task will store the file name into a variable(mFile) of type string datatype.

Now I wanted a script task (C# code) to create a filename from the variable (mFile) value.

Since it is a common issue I tried a lot in the internet but none of the queries worked.

View 10 Replies View Related

Integration Services :: XML Code Is Coming When Open Dtsx Package?

May 13, 2015

When I double click on dtsx package , i am getting XML code. I am unable to get the Graphical View. Recently I have installed Visual 2013.

View 6 Replies View Related

Integration Services :: How To Write ETL Package Addressing Different Collations Or Code Pages

Jul 13, 2015

Default code page in ETL package is 1252 which will not work if the collation is different e.g in Japanese_CI_AS, it is 932.

My question is how to write a generic ETL package so that it can cater any collation or any code page.

View 5 Replies View Related

Integration Services :: Comma Separated Value In A Flat File With Two Columns (Code / Name)

Oct 21, 2015

I have one requirements below..

Table input
Eno        ename                  Eloc       
1              Sid                         
Pune     101,201,301,401,501,601

Eno        ename                  Eloc       
1              Sid                         
Pune     101
1              Sid                         
Pune     201
1              Sid                         
Pune     301
1              Sid                         
Pune     401
1              Sid                         
Pune     501
1              Sid                         
Pune     601

View 5 Replies View Related

Integration Services :: Connect SharePoint List View To SSMS

Aug 31, 2015

I need to install SSIS/SSRS in SSMS. I have connected the SharePoint list view to excel for reporting purpose but due to data size the performances is very slow in excel and now I would like to connect the SharePoint List to SSIS/SSRS

I have windows 8, 64 bit system

I have installed SQL Server Management studio 2012 but I am unable to enable the Integration and Reporting Services

May be I have missed to choose the option during installation.

View 5 Replies View Related

Integration Services :: Unable To Remove View From Maintenance Plan

Sep 9, 2015

We have a maintenance plan in place for updating the statistics on daily basis. Now, i would like to remove the view from maintenance plan. How can i remove that from Update statistics task?

View 8 Replies View Related

Integration Services :: Executing Child SSIS Package In Parent SSIS

Oct 9, 2015

I want to achieve the following in (SSIS/SSDT for SQL 2012) - 

I have a generic SSIS package which simply sends out email notifications using SMTP email task (this package is within its own project, and has project level input parameters).

I need to be able to call this package in the Event handler section of every package (numbering in about less than 60) that we have. These packages are within their own respective projects.

I thought I could use the "execute package task", but it turns out , using this, I cannot call a package that is part of some other project. I also cannot call a package that is stored in the CATALOG. Is there any way I can do this ?

When I call the child package , I should be able to send in parameters like - error information and package name of the Parent package.

View 8 Replies View Related

How Do I Use SendMail Option In The Integration Services - I Found The Code On MSDN, But, How Do I Make It Workable?

May 14, 2007

if I have the following code:

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.SendMailTask;

class TestSendMailTask

public static void Main()

Package pkg = new Package();

ConnectionManager smtpCM;
smtpCM = pkg.Connections.Add("SMTP");
smtpCM.Name = "SMTP Connection Manager";
smtpCM.ConnectionString = "smtphost";

Executable exe = pkg.Executables.Add("STOCKendMailTask");
TaskHost thSendMailTask = (TaskHost)exe;
thSendMailTask.Properties["SmtpConnection"].SetValue(thSendMailTask, "SMTP Connection Manager");
thSendMailTask.Properties["ToLine"].SetValue(thSendMailTask, "");
thSendMailTask.Properties["CCLine"].SetValue(thSendMailTask, "");
thSendMailTask.Properties["BCCLine"].SetValue(thSendMailTask, "");
thSendMailTask.Properties["FromLine"].SetValue(thSendMailTask, "");
thSendMailTask.Properties["Priority"].SetValue(thSendMailTask, MailPriority.Normal);
thSendMailTask.Properties["FileAttachments"].SetValue(thSendMailTask, "C:\test_image.jpg");
thSendMailTask.Properties["Subject"].SetValue(thSendMailTask, "Testing the SendMail Task");
thSendMailTask.Properties["MessageSourceType"].SetValue(thSendMailTask, SendMailMessageSourceType.DirectInput);
thSendMailTask.Properties["MessageSource"].SetValue(thSendMailTask, "This is only a test.");

DTSExecResult valResults = pkg.Validate(pkg.Connections, pkg.Variables, null, null);

if (valResults == DTSExecResult.Success)




How do I make it a workable package so it compiles , w/ javadoc style comments and instructions, so, other people can use it?

View 20 Replies View Related

Integration Services :: How To Get Content Of Cache Connection Manager In Script Component By Code

Jul 27, 2015

ConnectionManager manager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.GetWrapper(base.Connections.Connection);
IDTSConnectionManagerCache100 cache = manager.InnerObject as IDTSConnectionManagerCache100;
if (cache != null)
  System.Windows.Forms.MessageBox.Show("Cache is found.");
and use
IDTSConnectionManagerCacheColumn100 id = connMgr.Columns["Id"]; get the column info.

but how do i get the cache connection content ?I want to look in the content in a script component code. 

View 4 Replies View Related

Integration Services :: Text Was Truncated Or One Or More Characters Had No Match In Target Code Page

Aug 11, 2015

I'm trying to import data in Excel into SQL Server table which you would think would be an absolute doddle seeing as they're both key Microsoft products in the BI family..One of the columns in Excel spreadsheet is Comments1 and a couple of the values in this column are over 300 characters in length yet when I set up the Excel source and then open Advanced Editor and look at Input and output properties this column has a data-type of Unicode string [DT_WSTR] with length of 255 which leads to the truncated error in the title.

I've researched this and on find going into the registry and updating the TypeGuessRows value from 8 to zero. I've done this and yet the data-type is still showing as Unicode string [DT_WSTR] with length of 255. I've even moved the row with the largest number of characters to the top of the spreadsheet and changed the TypeGuessRows value to 1 but the data-type still stays the same.I can't believe that it's soooo difficult to import data from one of Microsoft's key BI applications to another using their 'world-class' integration tool.

View 7 Replies View Related

Integration Services (SSIS)

Jul 25, 2007


I want to insert datas from a txt-file into a sql-table.
Therefor i would use a xml-file for the structure!

How can i refer this xml-file to a measurement insertion task?

Tanks for your help and sorry for my bad english :rolleyes:

View 1 Replies View Related

Integration Services :: How To Use UDF In SSIS

Oct 23, 2015

I want to use UDF in SSIS where function accept 4 parameter value and return 1 value.

How can i achieve that. I need workflow.

View 13 Replies View Related

Integration Services :: If Condition In SSIS

Apr 30, 2015

How to achieve the below condition in ssis

if person_id  is like '123%' or '124%'
then details='xyz'
else details='yxz'

View 7 Replies View Related

Integration Services :: SSIS CPU And RAM Sizing

Oct 20, 2015

if there is any way to accurately size a single server using SSIS.  The server will be a virtual machine.  The data being loaded will be approximately 200 MB per load with loading to a 150 GB database on a separate server.

View 3 Replies View Related

Integration Services :: SSIS Job Schedule

Aug 31, 2015

I have scheduled SSIS package through Sql Agent and when I right click on job start job as step package runs successfully but when I schedule job it dosent run.

View 12 Replies View Related

SQL Server Integration Services(SSIS)

Nov 14, 2007

Can anyone help regarding the SQL server integration Services(SSIS), ETL
We have requirement like this:
We have Live Database( LIVE_DB ) and Reports Database (REP_DB)
I want to trasfer the few tables data from LIVE_DB into the REP_DB for end of the day using SSIS
If any new records are added, updated or deleted in LIVE_DB, these should reflect in the REP_DB, Our requirement is not to delete the old data, we should append or delete or insert the new transaction data in REP_DB.

Thanks in advance, if anyone help me in resolving this issue.


View 3 Replies View Related

Integration Services :: SSIS Failed At Last

Apr 20, 2015

I have a maintenance plan which consist db full backup and log backup ( in two subplans), I execute both on SQL agent, and both failed.The DB Log Backup : DB FULL BACKUP LOG:

View 14 Replies View Related

Integration Services :: Use Windows Authentication In SSIS

Jun 17, 2015

I have a ssis package with an oledb connection using windows authentication. i want to understand when i promote this package to the server and add it to a job, then a user login to the server with sql server authentication and run this job. Which/what  windows authentication this package gonna to use to connect to the server ?

View 3 Replies View Related

Integration Services :: Convert Seconds To HH:MM:SS Using SSIS

Oct 7, 2015

converting seconds to HH:MM:SS using SSIS.  I know how to do it in T-SQL.

View 13 Replies View Related

Integration Services :: SSIS ToolBar Empty

Jun 8, 2015

I have installed VS community edition 2013 on a widows 7 PC. When I try to create a ssis package the ssis tool bar comes up empty.It just says "loading" on the panel .

View 2 Replies View Related

Integration Services :: How To Design SSIS Package

Nov 21, 2015

Win 7 SP1 x64 PC. I installed SQL Server 2014 Dev Edition + Visual Studio 2015.

I'd like to create some basic ETL SSIS packages, and I worked very comfortably in 2008R2.

For 2014, I started with this tutorial:[URL]However, it says to go to Start->All Programs->Microsoft SQL Server->SQL Server Data Tools. 

I did explicitly install SSDT when I installed VS2015.  I also installed it separately.  I see SSDT listed in Programs, and SSIS is running according to SQL Server Config Manager, and Services.  Half of Microsoft's docs seem to be 2012 era, which is a shame because 2014 is out and it's nearly 2016...

how do I get to the GUI where I can design ETL packages? 

View 7 Replies View Related

Integration Services :: Using SSIS To Populate Excel

Sep 25, 2015

We'll be using 2014 enterprise to populate some excel spreadsheets.  We may not have the option of using ssrs so here is the question.

The excel spreadsheets have some pretty fancy heading structures, multiple tabs and in a few cases graphs that i'm sure are dependent on data sitting somewhere in the spreadsheet.  The heading have variable info in them (eg FYxx where xx is a parameterized fiscal year, month names etc).  Sometimes those headings have sub headings (with variable info) and so on.

Generally speaking how (if at all) do folks deal with this kind of excel challenge when limited to using ssis? I don't know if templates are going to be a good idea. I don't know if i'll be looking at a fair amount of c# etc code behind the scenes. I can be more specific depending on the feedback.

This is part of a conversion from 2 of the larger BI and statistical tools out there to the MS sql stack.  Obviously with some shortcuts that we may wish we didn't take.  

View 5 Replies View Related

Integration Services :: Export XML Column In SSIS?

Apr 19, 2015

 we have a table with xml column. This column has a large xml data . I am trying to use ssis to import xml from sql column (table a) to destination (another table).

steps which i did in ssis:

1.  execute sql task:

    fetch the xml column by query and store "full result set" into an object variable.

2. foreach loop:

select Ado enumerator option and select variable which has reset set of execute sql task. In variable mapping selected a new variable of type string.

when I run package I get below error:

"Error: ForEach Variable Mapping number 1 to variable "User::variable" cannot be applied".

View 14 Replies View Related

Integration Services :: Updating Database From SSIS?

Jun 23, 2015

Have Visual Studio 2008 R2 with SP 2 installed.  Due to a merger we now have a MySQL database that we need to update from SSIS.  Everything works except for the table insert or update. Would upgrading to SP 3 or SP 4 maybe useful with that? 

We have installed the latest driver from MySQL.  Have tried the ADO.Net and ODBC drivers with similar results when we try to update the database.

View 7 Replies View Related

Integration Services :: Tracking SSIS ETL Process?

Apr 29, 2015

I have a ETL ( SSIS ) Process in which i am loading around 150 tables in each run. ( Truncate and Insert ). I have four packages each from different sources. ( Each package loads different tables and different numbers )These are run on weekly basis one after the other. Each package is taking around 60 to 90 minutes each. Now i want to track the progress of the ETL on my front End application. 

We want this in two ways. 

First Way : I need to show the user how much percent of ETL Process is completed 

Second Way : I need to show the No of tables completed and how many rows have been completed in the ongoing table ( which is in process )

how to design the table and ssis process.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved