MS SQL Server :: Generate Thousands Of SSRS PDFs Programmatically With SP Or SSIS

Integration Services :: Generate SSRS PDF From SSIS

Oct 1, 2014

I am trying to write a SSIS package to auto generate a SSRS PDF. I have tried several threads on the net but all seem to require a web service.I am using VS2012 and SQL2014..The report accepts one parameter @Licence

View 6 Replies View Related

Integration Services :: Generate PDF Report From SSIS Without Using SSRS?

Oct 15, 2013

I have a requirement, want to generate the PDF report from source data sql table records through SSIS package without using the SSRS tool. i came to know calling this SSRS report generate the PDF report. but my case without using the SSRS tool, needs to prepare the PDF report.

View 4 Replies View Related

Integration Services :: Generate SSRS Report Using SSIS

Jun 1, 2015

I have created a report using SSRS 2012. To create a pdf report, I use SSIS 2008. When I run debugging in SSIS, the pdf report is generated correctly.

But when I run the SSIS as a job in SQL 2012, the pdf report is generated with o bytes. When I tried to open it with Adobe, there is error, something like - Adobe Reader could not open PPAA.pdf because it is either not supported file type or because the file has been damaged.

View 15 Replies View Related

Methods To Generate ER Diagrams Programmatically

Sep 4, 2007

Hi All,

Can anyone tell me if its possible to generate ER diagrams programmatically using SQL Server.
Any help would be highly appreciated.

Thanks
-Akash

View 1 Replies View Related

T-SQL (SS2K8) :: Programmatically Generate A Number Sequence?

Sep 3, 2014

I'm trying to do a simple insert into a table, something like this:

insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)

So, the SELECT would generate something that looks like this:

ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)

Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:

ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)

What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.

Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.

What I'm hoping to do is rewrite my SQL like this:

insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'

Is there an easy way to do this?

View 9 Replies View Related

Programmatically Generate Rdl Files With Image File

Oct 1, 2007

Hello,

I would like programmatically generate rdl files with image.

I am using this XML Text Writer to create the rdl file.

But i have no idea how can i carryon on this task.

Can anyone help me in this topic?

Thanks.

View 4 Replies View Related

Programmatically Adding Users To SSRS?

Nov 9, 2007

Hi,

I��m looking for a way to add users to view reports in SSRS. We have set up SSRS to use forms authentication and have been running with no problems for a few months now. When we add new users to the aspnetdb we also have to go into management studio and give them permission to view reports. (Currently all users have the same permission which is set in the root folder policy in SSRS)

We have now set up a .NET application which allows our client to manage their users online, however they don��t have the knowledge or access to Management Studio in order to permit new users to access reports.

Is there a way, through SP or .NET script to programmatically add users to the policy / security descriptor in SSRS. I have already been playing with the XML Description in the SecDesc table of the ReportingServices database. This seems to contain all the necessary data, but modifying doesn��t work. In the same table is a Binary SecDesc field which I suspect has complementary data to go with it. Is there a way of generating this data?

Any help with this, or another method will be greatly appreciated.

Sean.

View 9 Replies View Related

Programmatically Creating And Executing Data Delivery Extensions In SSRS

Nov 15, 2006

Hi All,

I have been stuck with this problem since few days, need help regarding the same. I am enclosing the problem description and possible solutions that I have found.

Can anyone please help me out here?

Thanks and regards,
Virat

Problem Description:

I have a requirement for which I have created a data driven subscription in
SQL Server 2005, the whole thing works like this:

I have a report on Report Server which executes a stored procedure to get
its parameters; then it calls another stored procedure to get data for the
report; then it creates the report and copies it to a file share. This is
done using data driven subscription and the time set for repeating this
process is 5 minutes.

You can assume that following are working fine:

1. I have deployed the report on the Report Manager (Uploaded the report,
created a data source, linked the report to data source) - manually, the
report works fine.

2. Created a data driven subscription.

3. The data driven subscription calls a stored procedure, say
GetReportParameters which returns all the parameters required for the report
to execute.

4. The Report Manager executes the report by calling a stored procedure, say
GetReportData with the parameters provided by GetReportParameters stored
procedure; after it has generated the report file (PDF) is copied to a file
share.

For each row that GetReportParameters stored procedure returns a report (PDF
file) will be created and copied to file share.

Now, my question is

1. How to I get a notification that this file was successfully created
or an error occurred?
2. The only message that reporting service shows on 'Report Manager >
My Subscriptions' is something like "Done: 5 processed of 10 total; 2
errors."
How do I find out which record was processed successfully and which ones
resulted in an error?

Based on above results (success or failure), I have to perform further
operations.

Solutions or Work around that I have found:

1. Create a windows service which will monitor the file share folder
and look for the file name (each record has a unique file name) for the
reports that were picked up for PDF creation. If the file is not found, this
service will report an error. Now, there's a glitch there; if a report takes
very long time to execute it will also be reported as error (i.e. when this
service checks for the PDF file, the report was currently being generated).
So, I can't go with this solution.

2. I have also looked at following tables on ReportServer database:

a. Catalog - information regarding all the reports, folders, data
source information, etc.
b. Subscriptions - all the subscriptions information.
c. ExecutionLog - information regarding execution of the subscriptions
and the also manual execution of reports.
d. Notifications - information regarding the errors that occurred
during subscription execution.

For this solution, I was thinking of doing a windows service which will
monitor these tables and do further operations as required.

This looks like most feasible solution so far.

3. Third option is to look at DeliveryExtensions but in that case I
will have to manually call SSRS APIs and will have to manage report
invocation and subscription information. What is your opinion on this?

My environment details:

Windows XP SP2

SQL Server 2005

Reporting Services 2005

Please let me know if I am missing something somewhere...

View 9 Replies View Related

Programmatically Assigning The Datasource In The Server Report Ssrs 2005

Jan 2, 2008

Dear All:

I am new to SSRS 2005.

I have created many shared datsources and reprots at http://localhost/resportserver.

I am trying to access these reports from ReportViewer in the web application. Actually I want to use these reports for different customers by just changing the datsource programitically.

Is this is a possible?

Thank you
Theju

View 2 Replies View Related

Reporting Services :: Changing Credential Used For SSRS Data Source Programmatically

Jun 15, 2015

I am looking command (cmd/powershell/c#) for setting/updating credential for SSRS data source.

View 2 Replies View Related

SSRS: Generate Report Without RDL File

Jan 23, 2007

Hi all the good people here,

Can I generate a report without having RDL file physically at the server?

According to any "Generic Report Builder" logic, the report builder must stores the properties of Report in one form (RDL/XML) or the other (in database) and at the time of generating the "Report Output", builder must be reading those properties, assigning those to any class object's (say objRPT) properties/members and calling the method to generate the report in perticular file format.

I need a way where I can assign these values stored in the database (i.e. meta data of report is stored in database) to the objRPT directly. Can I do that.

I need this because I want to see whether I can switch from "Active Reports for .Net" to "SSRS" or not. I store all the meta data of the report into the database (including its control information, parameter mapping etc.) I have used Active Report's Events as well to show/hide controls/sections conditionally.

So in short as the metadata is in predefined-database, i don't want to bother with the RDL files. Can I do that?

Please help me with this.

View 1 Replies View Related

SSRS :Generate Live Data Report (Example Stock)

Feb 21, 2008

Hello,

I have requirement to cache report server as soon as data get refreshed in datbase.My database get refreshed every 10 minutes.
I am working in stock and bond domain were data changes very frequently.My user want to see almost live data whenever accessing report in report manager.they also want to cache some data for better performance.Can any one tell me step's to improve performance of reporting services .
Kindly suggest me.

Thanks.
Monika Singh

View 11 Replies View Related

Can We Generate Reports Out Of SSRS That Can Be Viewed On A Mobile Device?

Feb 14, 2008

Hi All,

Can we render a report directly on to a mobile device/ interface from SSRS?

View 6 Replies View Related

Reporting Services :: How To Generate Column Name Dynamically In SSRS Report

Oct 5, 2015

I have to display three months name as column name in ssrs reports. These month column will be dynamically. So i have to display the column dynamically.

View 3 Replies View Related

Programmatically Creating SSIS Package

Jan 4, 2007

Hi guys,

I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.
Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?
I am giving my code here too.
I will appreciate any kind of suggestion on this.

Code snippet:

public void CreatePackage()
{
string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;
      Package pkg = new Package();
      pkg.PackageType = DTSPackageType.DTSDesigner90;
ConnectionManager oledbConnectionManager =              CreateOLEDBConnection(pkg);
ConnectionManager flatfileConnectionManager =
             CreateFileConnection(pkg);
      // creating the SQL Task for table creation
Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);
ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;
      execSqlTask.Connection = oledbConnectionManager.Name;
      execSqlTask.SqlStatementSource =
"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) GO";
      // creating the Data flow task
Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");
      TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;
      MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;
      // Put a precedence constraint between the tasks.
PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);
      pcTasks.Value = DTSExecResult.Success;
      pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;
      // Now adding the data flow components
IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
      sourceDataFlowComponent.Name = "Source Data from Flat file";
      // Here is the component class id for flat file source data
      sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";
CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate();
      managedInstance.ProvideComponentProperties();
            sourceDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;
            sourceDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedInstance.AcquireConnections(null);
      managedInstance.ReinitializeMetaData();
      managedInstance.ReleaseConnections();
      // Get the destination's default input and virtual input.
IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];
                        // Here I dont find any columns at all..why??
      // Now adding the data flow components
IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
      destinationDataFlowComponent.Name =
"Destination Oledb compoenent";
      // Here is the component class id for Oledvb data
      destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate();
      managedOleInstance.ProvideComponentProperties();
destinationDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID;
destinationDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oledbConnectionManager);
      // Set the custom properties.
      managedOleInstance.SetComponentProperty("AccessMode", 2);
managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");
      managedOleInstance.AcquireConnections(null);
      managedOleInstance.ReinitializeMetaData(); // Throws exception
      managedOleInstance.ReleaseConnections();
      // Create the path.
IDTSPath90 path = dataFlowTask.PathCollection.New();            path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],
      destinationDataFlowComponent.InputCollection[0]);
      // Get the destination's default input and virtual input.
IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];
      IDTSVirtualInput90 vInput = input.GetVirtualInput();
      // Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
      {
          managedOleInstance.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
      }
      DTSExecResult res = pkg.Execute();
}
public ConnectionManager CreateOLEDBConnection(Package p)
{
      ConnectionManager ConMgr;
      ConMgr = p.Connections.Add("OLEDB");
      ConMgr.ConnectionString =
"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";
      ConMgr.Name = "SSIS Connection Manager for Oledb";
      ConMgr.Description = "OLE DB connection to the Test database.";
      return ConMgr;
}
public ConnectionManager CreateFileConnection(Package p)
{
      ConnectionManager connMgr;
      connMgr = p.Connections.Add("FLATFILE");
      connMgr.ConnectionString = @"D:MyCSVFile.csv";
      connMgr.Name = "SSIS Connection Manager for Files";
      connMgr.Description = "Flat File connection";
      connMgr.Properties["Format"].SetValue(connMgr, "Delimited");
connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);
      return connMgr;
}

And my CSV files is as follows

NAME, AGE, GENDER
Jon,52,MALE
Linda, 26, FEMALE

Thats all. Thanks.

View 4 Replies View Related

Run SSIS Programmatically In 32-bit Mode On 64-bit Server

Jan 15, 2008

Environment:

Windows Server 2003 64-bit

SSIS 32-bit & 64-bit installed

.NET 2.0 application (Platform target x86) which executes an SSIS package programatically (using Microsoft.SQLServer.ManagedDTS.dll)
The issue I am having is that we connect to a Sybase server and even though the .NET app is executing in 32-bit mode, the SSIS pack appears to try load Sybase 64-bit OLEDB drivers.

The error I receive is DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

When I run on a 32-bit machine all runs fine or if I run using the 32-bit version of DTEXEC the package also runs fine.

Any ideas on how I can force SSIS to use 32-bit drivers when run on 64-bit server programatically?

View 5 Replies View Related

Create SSIS Package Programmatically In VB6

Nov 28, 2007

Hi,

I am working on modifying a VB6 app that dynamically creates DTS packages to copy data from one database to another depending on the selections made in UI. The project currently uses DTSPackage object library and DTSDataDump Scripting object library.
We are in the process of upgrading the server to SQL 2005. I am exploring the possibility of replacing code that generates DTS packages on the fly with SSIS packages.

Is it feasible to do this in VB6 ? I have referred to similar posts which focus mainly on VB.NET or C#.
Any help with white paper or sample code would be appreciated.

Thanks in advance

View 6 Replies View Related

SSIS FlatFileSource To OleDBDestination Programmatically

Jan 21, 2008

I cannot seem to get my code to work. I have read all of the samples I could find (about 30 of them), a chapter on SSIS programming in a SQL Server 2005 book, and all of the MS online documentation. I would like to programmatically create an SSIS package that can load data from a flat file source into an oledb destination. I am not doing any transformations.

For the test, my source file has now degenerated to two columns. The entire file looks like this:

hello,goodbye
here,there

That's it, two rows, two column, no headers.

My destination table is TestTable(col1 varchar(150), col2 varchar(150))

This ought to be trivial. Here is my code. If someone can point out what I have done incorrectly, I would be truly grateful.

//// Create the package
Package myPackage = CreatePackage("SSISPackage", "Sample CSV Import Package");
//// Events.
PackageEvents myPackageEvents = new PackageEvents();
ComponentEvents myPipelineEvents = new ComponentEvents();

//// Create the data flow task
TaskHost th = myPackage.Executables.Add("DTS.Pipeline") as TaskHost;

th.Name = "DataFlow";
th.Description = "The DataFlow task in the Onvoy CSV Import sample.";
MainPipe myDataFlowTask = th.InnerObject as MainPipe;
myDataFlowTask.Events = myPipelineEvents as wrap.IDTSComponentEvents90;

//// Add the flatfile source connection manager.
ConnectionManager cmFlatFile = myPackage.Connections.Add("FLATFILE");
// Set the stock properties.
cmFlatFile.Properties["ConnectionString"].SetValue(cmFlatFile, "c:\temp\testimport.txt");
cmFlatFile.Properties["Format"].SetValue(cmFlatFile, "Delimited");
cmFlatFile.Properties["DataRowsToSkip"].SetValue(cmFlatFile, 0);
cmFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmFlatFile, false);
cmFlatFile.Properties["Name"].SetValue(cmFlatFile, "FlatFileConnection");
cmFlatFile.Properties["RowDelimiter"].SetValue(cmFlatFile, "
");
cmFlatFile.Properties["TextQualifier"].SetValue(cmFlatFile, """);

//// Add the OLEDB destination connection manager.
ConnectionManager cmSubscriber = myPackage.Connections.Add("OLEDB");
// Set stock properties.
cmSubscriber.ConnectionString = @"Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=subscriber;Data Source=MyDataSource;Auto Translate=False;";
cmSubscriber.Name = "SubscriberConnection";
cmSubscriber.Description = "SubscriberConnection";
//// Add Flat File Source
// Add the component to the dataFlow metadata collection
IDTSComponentMetaData90 myFlatFileSource = myDataFlowTask.ComponentMetaDataCollection.New();
myFlatFileSource.ComponentClassID = "DTSAdapter.FlatFileSource";
CManagedComponentWrapper instFlatFileSrc = myFlatFileSource.Instantiate();
instFlatFileSrc.ProvideComponentProperties();
instFlatFileSrc.SetComponentProperty("RetainNulls", true); // Treat empty columns as null.
// Set the common properties
myFlatFileSource.Name = "FlatFileSource";
myFlatFileSource.Description = "Flat file source";
// Associate the runtime ConnectionManager with the component
myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManagerID
= myPackage.Connections["FlatFileConnection"].ID;
myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(myPackage.Connections["FlatFileConnection"]);

// Add columns to the FlatFileConnectionManager
// Hardcode the columns for now
List<string> srcColumns = new System.Collections.Generic.List<string>();
srcColumns.Add(""Column1"");
srcColumns.Add(""Column2"");
//srcColumns.Add(""Column3"");
//srcColumns.Add(""Column4"");
//srcColumns.Add(""Column5"");
//srcColumns.Add(""Column6"");
//srcColumns.Add(""Column7"");
//srcColumns.Add(""Column8"");
//srcColumns.Add(""Column9"");
//srcColumns.Add(""Column10"");
//srcColumns.Add(""Column11"");
//srcColumns.Add(""Column12"");
//srcColumns.Add(""Column13"");
// get the actual connection manager instance
//wrap.IDTSConnectionManagerFlatFile90 ff = myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManager as wrap.IDTSConnectionManagerFlatFile90;
wrap.IDTSConnectionManagerFlatFile90 ff = cmFlatFile.InnerObject as wrap.IDTSConnectionManagerFlatFile90;
if (ff != null)
{
wrap.IDTSConnectionManagerFlatFileColumn90 column;
wrap.IDTSName90 name;
foreach (String colName in srcColumns)
{
// iterate
// now create a new column for the connection manager
column = ff.Columns.Add();
// if this is the last row
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
column.ColumnDelimiter = "
";// add the row delimiter
else
column.ColumnDelimiter = ",";
name = (wrap.IDTSName90)column;
name.Name = colName.Replace(""", "");
column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 150;
column.MaximumWidth = 150;
column.DataPrecision = 0;
column.DataScale = 0;
}
}
instFlatFileSrc.AcquireConnections(null);
instFlatFileSrc.ReinitializeMetaData();
instFlatFileSrc.ReleaseConnections();
//// End Add Flat File Source

//// Add OLEDB Destination
IDTSComponentMetaData90 myOledbDestination = myDataFlowTask.ComponentMetaDataCollection.New();
myOledbDestination.ComponentClassID = "DTSAdapter.OLEDBDestination";
CManagedComponentWrapper instOleDbDest = myOledbDestination.Instantiate();
instOleDbDest.ProvideComponentProperties();
// Set stock properties.
myOledbDestination.Name = "OLEDBDestination";
myOledbDestination.Description = "Destination for data";
// Associate the runtime connection manager
// The connection manager association will fail if called before ProvideComponentProperties
myOledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID
= myPackage.Connections["SubscriberConnection"].ID;
myOledbDestination.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(myPackage.Connections["SubscriberConnection"]);
// set custom component properties
instOleDbDest.SetComponentProperty("OpenRowset", "[dbo].[TestTable]");
instOleDbDest.SetComponentProperty("AccessMode", 0);
// Acquire Connections and reinitialize the component
//instOleDbDest.AcquireConnections(null);
//instOleDbDest.ReinitializeMetaData();
//instOleDbDest.ReleaseConnections();

//// Map the source to the destination
myDataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
myFlatFileSource.OutputCollection[0], myOledbDestination.InputCollection[0]);

instOleDbDest.AcquireConnections(null);
instOleDbDest.ReinitializeMetaData();
instOleDbDest.ReleaseConnections();
//// Map the source columns to the destination columns
// Get the destination's default input and virtual input.
IDTSInput90 input = myOledbDestination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
//foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
//{
// // Call the SetUsageType method of the destination
// // to add each available virtual input column as an input column.
// instOleDbDest.SetUsageType(
// input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
// instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
//}
IDTSVirtualInputColumn90 vColumn = vInput.VirtualInputColumnCollection[0];
instOleDbDest.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection["col1"].ID);

vColumn = vInput.VirtualInputColumnCollection[1];
instOleDbDest.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection["col2"].ID);
instOleDbDest.AcquireConnections(null);
instOleDbDest.ReinitializeMetaData();
instOleDbDest.ReleaseConnections();

//// Validate and execute the package
DTSExecResult status = myPackage.Validate(null, null, myPackageEvents, null);
if (status == DTSExecResult.Success)
{
DTSExecResult result = myPackage.Execute(null, null, myPackageEvents, null, null);
}

View 8 Replies View Related

Acquiring Connection Manager Programmatically In SSIS

Jul 9, 2006

hi all,

I m new to this forum and hope i ll get warm welcome from all of you.... thank you

I m Praveen kumar Dayanithi... a master student doin my assistantship in a Company. Kindly help me with this...

Here is my issue.... i would like to know how can i pass arguments to connection manager programmatically. In other words how can i acquire connection manger through SCRIPT task(Vb script). I know it is very easy to manually select n specify database name, table name by right clicking in dataflow task but in my company if i do that it will be very cumbersome for production people to change all the database and table names manually when the project is moved to production. Rather if i programmatically acquire connection manager using global variables it will make production people's job very easy. They have to just change the value of the variables. So can any one help me with this issue.

Thanks n regards

View 1 Replies View Related

Reporting Services :: SSRS- How To Generate Sample Subscription Report Before Scheduling Time

Sep 24, 2015

How to achieve by using SSRS. How we can "quick run" report within the subscription menu to ensure report was set up correctly during creation. Is there any way to generate sample subscriped report without waiting for scheduled time.

I mean whether we can verify the report parameters & data in generated the report. I have verified SSRS report manager .i cant see any button or option to test run the report in subscription feature.

View 6 Replies View Related

SSIS Custom Component DerivedColumn Programmatically Problems

Feb 13, 2006

dear experts,
i'm trying to build a package programmatically from client c# application. I'm working to create three dataflow components:
- OleDB Source
- Derived Column Transformations
- OleDb Destination.

My package works good, but i have several problems when insert an expression as Value of an IDTSCustomPropriety90 object, like this one:
[LEN](#firsname.lineageID) &gt 5 ? <<if true>> : <<if false>>

Simple expressions like concatenate strings, for example "#firstname.lineageID" + "#lastname.lineageID" seem to work good.

Thanks a lot in advanced.

Paganelli Francesco

View 19 Replies View Related

Executing A SSIS Package Programmatically Where Only SQL Express Is Installed

Apr 23, 2007

Hi,

I have created an application that loads a package and executes it using DTS runtime classes. But when I run the application on a machine where only SQL Express edition is installed it's throwing

"Retreiving COM class factory for component with CLSID E44847F1-FD8C-4251-B5DA-B04BB22E236E failed due to the following error : 80040154"

Can someone help?

View 1 Replies View Related

Reporting Services :: SSRS - Auto Generate Multiple PDF Files And Save Into Window Folder?

Jul 15, 2015

We have the following requirement :

1. In daily basis auto loop through each item in the invoice table.

2. passing invoice number into a Summary SSRS report as parameter.

3. Auto download ALL generated PDF reports into a window folder with a special file name format i.e. <INVOICE_NO>_<DATE>.pdf

how to achieve this via SSRS, Store Procedure or Power Shell?

View 4 Replies View Related

Passing A Table Programmatically To Datareader Source Component In Ssis

Feb 26, 2007

Hi There,

I am loading/executing packages from c# and I need to populate a temp table from user input and pass this table as a variable to the datareader source components sql command. I am using expression to build this query, but I am getting design time error when I have this command..

"select id, (SysDate + 28) as ExpiresDate from Table1 where id in (Select Id from" +@[User::Table2]+")"..

I have declared Table2 as a variable of type Object and I am creating Table2 in C# and I am assigning that Table to the user Table. But in the design mode, I am getting an error...expression cannot be evaluated.

Can anybody please tell me when I cannot do this?

Thanks,

View 3 Replies View Related

Programmatically Created SSIS Package, CSV File To OLDDB (SQLSever 2005)

Sep 23, 2007

Hi everyone,

I wanted to thank everyone for posting a ton of valuable information in these forums. I also want to thank all the moderators that have been replying with really insightful help!

I am trying to programmatically create an SSIS package to take .CSV data and put it into a SQL Server 2005. I am assuming that this is pretty common scenario.

I have used many of the examples in this forum as well as heavily borrowing from this example http://www.codeproject.com/csharp/Digging_SSIS_object_model.asp written by Moim Hossain.

I can get my package to create and execute properly but no data is being written to the SQL Server table. This has puzzled me for the last 2 days!

I know the issue isnt with the server itself because I tested it by graphically creating a test SSIS package and it transfers the .CSV data to the table perfectly.

Would anyone know why this would happen? The Execution results are returning success but no data is written to the table!

Could anyone please provide insight as to what my issue may be?

Thanks in advance!

Code Snippet

using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace SumCodeApp
{
class SumCodeApp
{
// Variables.
private Package package;
private ConnectionManager flatFileConnectionManager;
private ConnectionManager destinationDatabaseConnectionManager;
private Executable dataFlowTask;
private List<String> srcColumns;

int file_count;
SqlConnection connection;

String folder_path;
String username;
String password;
String DB_server;
String catalog;

// Default Constructor.
public SumCodeApp()
{
}

// Constructor taking in user info.
public SumCodeApp(String folder_path, String username, String password,
String DB_server, String catalog)
{
this.folder_path = folder_path;
this.username = username;
this.password = password;
this.DB_server = DB_server;
this.catalog = catalog;
}

private void CreatePackage()
{
package = new Package();
package.CreationDate = DateTime.Now;
package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive;
package.Name = "SumCode Package";
package.Description = "Upload the SumCode files to the database";
package.DelayValidation = true;
package.PackageType = DTSPackageType.DTSDesigner90;
}

private void CreateFlatFileConnection()
{
String flatFileName = ".1105.csv";
String flatFileMoniker = "FLATFILE";
flatFileConnectionManager = package.Connections.Add(flatFileMoniker);
flatFileConnectionManager.Name = "SSIS Connection Manager for Files";
flatFileConnectionManager.Description = String.Concat("SSIS Connection Manager");
flatFileConnectionManager.ConnectionString = flatFileName;

// Set some common properties of the connection manager object.
//flatFileConnectionManager.Properties["ColumnNamesInFirstRow"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, """);
flatFileConnectionManager.Properties["RowDelimiter"].SetValue(flatFileConnectionManager, "
");
flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);

// Create the source columns into the connection manager.
CreateSourceColumns();
}

private void CreateSourceColumns()
{
// Get the actual connection manager instance
RuntimeWrapper.IDTSConnectionManagerFlatFile90 flatFileConnection = flatFileConnectionManager.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;

RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 column;
RuntimeWrapper.IDTSName90 name;

// Fill the source column collection.
srcColumns = new List<String>();
srcColumns.Add("CreateDate");
srcColumns.Add("CorpID");
srcColumns.Add("SumCodeID");
srcColumns.Add("Priority");
srcColumns.Add("SumCodeAbv");
srcColumns.Add("SumCodeDesc");
srcColumns.Add("SumCodeGroupID");

foreach (String colName in srcColumns)
{
column = flatFileConnection.Columns.Add();
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
//column.ColumnDelimiter = "
";
column.ColumnDelimiter = "{CR}{LF}";
else
//column.ColumnDelimiter = ",";
column.ColumnDelimiter = "Comma {,}";

name = (RuntimeWrapper.IDTSName90)column;
name.Name = colName;

column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 0;
column.MaximumWidth = 255;
column.DataPrecision = 0;
column.DataScale = 0;

}

}

private void CreateDestinationDatabaseConnection()
{
destinationDatabaseConnectionManager = package.Connections.Add("OLEDB");
destinationDatabaseConnectionManager.Name = "Destination Connection - SumCodeCorpGroup";
destinationDatabaseConnectionManager.Description = "Connection to the temporary table SumCodCorpGroup";
destinationDatabaseConnectionManager.ConnectionString = "Data Source=DIVWL-356KCB1;Initial Catalog=SumCode;Provider=SQLOLEDB;Persist Security Info=True;User ID=sum;Password=code";
}

public class Column
{
private String name;
private Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType;
private int length;
private int precision;
private int scale;
private int codePage = 0;

public String Name
{
get { return name; }
set { name = value; }
}

public Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType DataType
{
get { return dataType; }
set { dataType = value; }
}

public int Length
{
get { return length; }
set { length = value; }
}

public int Precision
{
get { return precision; }
set { precision = value; }
}

public int Scale
{
get { return scale; }
set { scale = value; }
}

public int CodePage
{
get { return codePage; }
set { codePage = value; }
}
}

private Column GetTargetColumnInfo(string sourceColumnName)
{
Column cl = new Column();
if (sourceColumnName.Contains("CreateDate"))
{
cl.Name = "CreateDate";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (
sourceColumnName.Contains("CorpID"))
{
cl.Name = "CorpID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeID"))
{
cl.Name = "SumCodeID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("Priority"))
{
cl.Name = "Priority";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeAbv"))
{
cl.Name = "SumCodeAbv";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeDesc"))
{
cl.Name = "SumCodeDesc";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeGroupID"))
{
cl.Name = "SumCodeGroupID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
return cl;
}

private void CreateDataFlowTask()
{
String dataFlowTaskMoniker = "DTS.Pipeline";
dataFlowTask = package.Executables.Add(dataFlowTaskMoniker);

}

public void ImportFile(String directory_path)
{
// Create the package.
CreatePackage();

// Create Flat File Source Connection.
CreateFlatFileConnection();

// Create Database Destination Connection.
CreateDestinationDatabaseConnection();

// Create DataFlowTask.
CreateDataFlowTask();

// Create the DataFlowTask
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
sourceComponent.Name = "Source File Component";
sourceComponent.ComponentClassID = "DTSAdapter.FlatFileSource";

PipeLineWrapper.CManagedComponentWrapper managedFlatFileInstance = sourceComponent.Instantiate();
managedFlatFileInstance.ProvideComponentProperties();
sourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID;
sourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatFileConnectionManager);

managedFlatFileInstance.AcquireConnections(null);
managedFlatFileInstance.ReinitializeMetaData();

Dictionary<String, int> outputColumnLineageIDs = new Dictionary<String, int>();
PipeLineWrapper.IDTSExternalMetadataColumn90 exOutColumn = null;

foreach (PipeLineWrapper.IDTSOutputColumn90 outColumn in sourceComponent.OutputCollection[0].OutputColumnCollection)
{
exOutColumn = sourceComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
managedFlatFileInstance.MapOutputColumn(sourceComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
outputColumnLineageIDs.Add(outColumn.Name, outColumn.ID);
}
managedFlatFileInstance.ReleaseConnections();

String a = sourceComponent.RuntimeConnectionCollection[0].Name.ToString();
String b = sourceComponent.OutputCollection[0].Name;
String c = sourceComponent.OutputCollection[0].Description;
String d = sourceComponent.OutputCollection[0].OutputColumnCollection.Count.ToString();

// Create DataFlowTask Destination Component.
PipeLineWrapper.IDTSComponentMetaData90 destinationComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
destinationComponent.Name = "OLEDB SQL Connection";
destinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination";

PipeLineWrapper.CManagedComponentWrapper managedOleInstance = destinationComponent.Instantiate();
managedOleInstance.ProvideComponentProperties();

// Create a path and attach the output of the source to the input of the destination.
PipeLineWrapper.IDTSPath90 path = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).PathCollection.New();
path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0], destinationComponent.InputCollection[0]);

destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = destinationDatabaseConnectionManager.ID;
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(destinationDatabaseConnectionManager);

managedOleInstance.SetComponentProperty("AccessMode", 0);
managedOleInstance.SetComponentProperty("OpenRowset", "[SumCode].[dbo].[SumCodeCorpGroup]");
managedOleInstance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
managedOleInstance.SetComponentProperty("DefaultCodePage", 1252);
managedOleInstance.SetComponentProperty("FastLoadKeepIdentity", false); // Fast load
managedOleInstance.SetComponentProperty("FastLoadKeepNulls", false);
managedOleInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", 0);
managedOleInstance.SetComponentProperty("FastLoadOptions","TABLOCK,CHECK_CONSTRAINTS");

managedOleInstance.AcquireConnections(null);
managedOleInstance.ReinitializeMetaData();

PipeLineWrapper.IDTSInput90 input = destinationComponent.InputCollection[0];
PipeLineWrapper.IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (PipeLineWrapper.IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
//if (outputColumnLineageIDs.ContainsKey(vColumn.LineageID.ToString()))
//{
managedOleInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSUsageType.UT_READONLY);
//}
}

List<String> tmp = new List<String>();
foreach(PipeLineWrapper.IDTSInputColumn90 inc in destinationComponent.InputCollection[0].InputColumnCollection)
{
tmp.Add(inc.Name);
}

PipeLineWrapper.IDTSExternalMetadataColumn90 exColumn;
foreach (PipeLineWrapper.IDTSInputColumn90 inColumn in destinationComponent.InputCollection[0].InputColumnCollection)
{
exColumn = destinationComponent.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
Column mappedColumn = GetTargetColumnInfo(exColumn.Name);
String destName = mappedColumn.Name;

exColumn.Name = destName;

managedOleInstance.MapInputColumn(destinationComponent.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}

managedOleInstance.ReleaseConnections();

DTSExecResult result = package.Execute();
a = "0";
}

}
}

View 3 Replies View Related

Thousands Databases ?

Feb 12, 2008

Hi,
i just started working on a new project, a RIA with a backend on SQL 2005.

The question that I'm asking - about the databases architecture - is it possible:
a) (this is the preferred solution) to have up to 5 000 (five thousands) databases inside a SQL 2005 instance (each client with his own small db)
b) would it be better to have one large database with tables eventually having near a billion of rows ?
c) one single database with thousands of small tables.

Thank You All.

Edit :
The clients databases will be identical in structure.
The motivaion for having each client with his own database is to ease management (backups, restores, roll-backs) and tools in this direction will be created (scripts, automated procedures, scheduled management plans etc). Each client is proprietary, and to have things separated seems more legal.
The hardware is not a problem, the db server will be a blade center runing a clustered MS Windows Server.

Code Snippet

(07:51:49) him: i haven't followed sql recently but let me see (seems difficult question)
(07:52:36) him: what is the motivation to have different datbases for each client?
(07:52:46) me: (the hardware is not a problem, probably ibm blade center, about the management - tools will be created to ease)
(07:52:52) him: it is it the size/efficiency or you want to separate them
(07:53:02) me: for the back-ups and restores and roll-backs
(07:53:13) me: the db-s r identical
(07:53:30) him: identical in design or data inside them
(07:53:33) me: yes, design
(08:02:12) him: so the limitation is Databases per instance of SQL Server 32,767
(08:02:21) him: so it seems that in that respect you are going to be fine
(08:02:45) me: yes, i've seen the specs
(08:02:50) him: yep
(08:03:03) him: in terms of design
(08:03:13) him: managing 5000 databases would be a nightmare
(08:03:22) him: unless there is some automatic way to do it
(08:04:18) me: that's for shure, and that's what is intented
(08:04:29) me: automadet procedures, scripts etc
(08:05:27) him: so again what is the motivation to partition each client in different database
(08:06:05) me: there is a need to keep their data separate
(08:06:19) me: each client is proprietary
(08:06:44) me: and to have things separated is more legal
(08:07:25) him: well you try with several databases and see how it scales
(08:08:37) him: one thing that would also prevent confusing databases connections and may be helpful if you decide to change the to a single database model is to design the database as if it would be used with many clients but use it just for one
(08:09:09) him: then, if you decide to merge the databases in the future it would be relatively easy
(08:09:16) him: unless this would create too much overhead
(08:09:41) me: yes, this is allready the case
(08:09:58) me: so, i go for a test u say
(08:10:12) me: and see how it's working
(08:10:16) him: ok. then it seems that the issue is maintaince then anything else
(08:10:29) him: yeah. i don't think the sql server would be a problem
(08:10:44) him: there would be some overhead of the different databases but should be small
(08:11:12) me: :)
(08:11:24) me: k, thank you verry much
(08:12:53) him: http://www.thescripts.com/forum/thread503521.html
(08:13:09) him: i think either way has plus and minuses
(08:16:56) him: also read the forum link i sent you
(08:17:00) me: ty, i'm on it
(08:17:16) him: there are some interesting considerations i did not think of
(08:17:58) me: i just saw about caching maintainance plans, stored procs

View 9 Replies View Related

Deploying Pdfs

Jan 30, 2008

Newbie question for which I ask your patience:

I had set my ProjectpropertiesTarget Server URL some time ago and now need to deploy additional files in specific folders and cannot remember how to map a url back to a file location. I have searched for the deployed files on target server and have not found them.

Can someone explain how SSRS deploys; does it physically place files on the target server (.pdfs)? Suggest how I can map the URL back to a directory location on some server?

thanks, MDM

View 3 Replies View Related

Logging Thousands Of Errors

May 4, 2007

I'm receiving these two errors again and again:

Windows cannot load extensible counter DLL MSSQL$MSFW, the first DWORD in data section is the Windows error code.

Windows cannot load extensible counter DLL MSSQL$SBSMONITORING, the first DWORD in data section is the Windows error code.

What might be generating them and how can I cure it?

The system is SBS 2003 R2 Premium.

Thanks.

View 6 Replies View Related

How To Generate Reports From SSIS

Jan 30, 2007

Hi All,

I want to know how to generate a report from SSIS, for example, a report telling what has changed to a table.

Thanks,

TPK

View 4 Replies View Related

Rendering Extensions And PDFs

Jan 25, 2008

Quick question...

Is a report rendered as PDF, a real PDF?

I want to deliver reports for an application we are developing via or current ECM system. I need the report to be full text indexed. A TIFF wrapped in a PDF isn't always the best thing for full text indexing.

View 1 Replies View Related

Deleting Thousands Of Rows In A Table

Apr 10, 2003

How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:
Delete from transactions
WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())

increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.
With the command truncate table I cannot use query unfortunately but this would be faster.

Anyone has an idea?

mipo

View 3 Replies View Related

Slow Query Thousands Of Records.

Jul 20, 2005

I have a query that returns raw tick data from a table. Unfortunately aftereven a few days there are hundreds of thousands of rows so the followingquery is not efficient.SELECT * FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? ANDRawTickID = (SELECT Max(RawTickID) FROM RAWTICKDATA WHERE Status = 'I' ANDContractCode = ? AND PRICE =(SELECT Min(Price) FROM RAWTICKDATA WHERE Status= 'I' AND ContractCode = ?))The most obvious solution then is to get all tick data with status ='I'(Imported) for a contract, process it and then move it to another table forarchiving. I am faced with a problem however: After selecting all data for acontract with status='I' the application was updating these records to a newstatus of 'P' (processed). Unfortunately another application is continuningto feed in live data and so it is possible that we will inadvertantly updateunprocessed data to 'P'.Question: Is it possible to select all records with status 'I' (from abovequery) and update their status to 'P' in one sequence?I am not a programmer, but if this is possible I should be able to implementthe query.Many thanks.Steve

View 3 Replies View Related