Integration Services :: Generate CSV File Dynamically With A Fixed Amount Of Rows
Jun 5, 2015
I need to generate a csv file from another csv file, seems to be simple but let's go the trick thing:
Needs to have maximum 1000 lines, if I reach to this, I need to create another csv and fill that new one.
Exemplifying:
I have a csv file called fileA and this has 2000 lines and another csv called fileB with 1500 lines.
I need to loop a folder and get the fileA, create an output called FileAOutput and start to fill that, if I reach to 1000 lines, I need to create a FileAOutput_2 and fill the other 1000 lines...so I'll go to fileB and do the same thing, but in the second case, I'll have 500 lines in the second output.
View 5 Replies
ADVERTISEMENT
Oct 5, 2015
I have a table with 3 columns (ID Int , Name Varchar(25), Course Varchar(20))
My source data looks like below
ID Name Course
1 A Java
1 A C++
2 B Java
2 B SQL Server
2 B .Net
2 B SAP
3 C Oracle
My Output should look like below...
ID Name Course(1) Course(2) Course(3) Course(4)
1 A Java C++
2 B Java SQL Server .Net SAP
3 C Oracle
Basically need t-sql to Convert non fixed rows to non fixed columns...
Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)
Create SQL:
Create table Sample (ID Int null , Name Varchar(25) null, Course Varchar(20) null)
Insert SQL:
INSERT Sample (ID, Name, Course)
VALUES (1,'A','Java'),
(1,'A','C++'),
(2,'B','Java'),
(2,'B','SQL Server'),
(2,'B','.Net'),
(2,'B','SAP'),
(3,'C','Oracle')
View 12 Replies
View Related
Jun 17, 2015
I am looking for a way to load the SQL server table where the source xml file format is not fixed.
It has to dynamically check for the attributes and load into a SQL Server table with changing source xml file format.
The levels of the attribute can change in side the xml tags, the C# code has to parse the file get the attribute name and load the associated value.
View 5 Replies
View Related
Oct 1, 2014
I have an issue where I have multiple rows of data and I need to reduce a dollar amount by a fixed maximum. I am going to throw some code in here to give a rudimentary idea of the data and what the final result should be.
declare @tbl table
(LineNum int,
Code varchar(2),
Amt money,
MaxAmt money
[Code] ....
I need to run an update so that the result of the following query:
select LineNum, Code, Amt, MaxAmt from
@tblLooks like this:
LineNum Code Amt MaxAmt
----------- ---- --------------------- ---------------------
1 AA 10.00 50.00
2 AA 20.00 50.00
3 AA 20.00 50.00
(3 row(s) affected)
I have tried cursors but got unexpected results or the MaxAmt always defaulted to the original even if I updated it. This seems like a simple problem but I have been banging my head against the wall for 2 days now. I've written some pretty complicated updates with less effort than this and I must have some mental block that is keeping me from figuring this out.
View 3 Replies
View Related
May 22, 2015
I'm generating XML file at runtime using c# code. Using the same XML file i want to generate XSD file at runtime.
How can i generate XSD and pass the location value of XSD file in XML Source at runtime?
View 2 Replies
View Related
Apr 2, 2014
I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
View 6 Replies
View Related
Sep 30, 2015
I have requirement like to develop dynamic package for inserting data from flat file to table.
Find below points for more clarification :--
1) if I changed the flat file values and name in source variable AND the table name should be also changed based on variable value .
2) it should dynamically mapped with column values with source file as we have to insert data in target table.
See below diagram for more clarification.
View 10 Replies
View Related
Oct 5, 2015
I have a .xlsx file, in that file I get data from the 3rd row. Using SSIS I am converting .xlsx file into .csv file. I am able to convert it but in the .csv file the data are populating from the first row itself. I want to get data in 3rd row it self.
View 2 Replies
View Related
Jun 16, 2015
I got a flat file like:
Location CurrencyRates
ALBERTA #15U.S.$ 0.2930 1 Can$ 0.0900
BRITISH COLUMBIA #14U.S.$ 0.6891 2 Can$ 0.2117
MANITOBA #18U.S.$ 0.4557 3 Can$ 0.1400
If there a way I can use SSIS to transfer this file to something like:
locationUSDUSDrateflagCADCADrateALBERTA #15U.S.$ 0.29301Can$ 0.0900BRITISH COLUMBIA #14U.S.$ 0.68912Can$ 0.0900MANITOBA #18U.S.$ 0.45573 Can$ 0.1400
View 6 Replies
View Related
May 28, 2015
i need to Delete/Overwrite Rows in Excel without using Script Task in SSIS.
View 4 Replies
View Related
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
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
Sep 10, 2015
i want to break 2 by 2 rows in reportceiling(rownumber(nothing)/2).i used this expression in row group. URL....but i want to use this expression in matrix and that matirx is with in list . so i am getting error . how to use rownumber in list.here i used list to break the page wise id
View 3 Replies
View Related
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
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
Nov 16, 2011
Every day an application creates new tables and dumps static info into them.
I would like to create a package to dynamically export those database tables to raw files for long term archive, one file per table. Here is what I have so far and the issue I am having.
1) Get a list of un-archived tables.
2) Foreach table do the following.
a. Export the table into raw file.
b. Zip the raw file.
c. Update archive tracking table.
As long as the metadata for each table is the same this package seems to work fine. However, I have many tables with different metadata. How can I dynamically get the package update the metadata column collection when it hits a new table? When it hits a table with different metadata I am getting warnings like this:
The column "some_column" needs to be added to the external metadata column collection.
The "external metadata column "someother_column" (103)" needs to be removed from the external metadata column collection.
Then I get this error:
Error: 0xC004706B at dump the table into a raw file, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA"
View 7 Replies
View Related
Jul 31, 2015
I need to create a DB dynamically in the Server from the SSIS only....
How to implement this....
View 4 Replies
View Related
Feb 19, 2015
I have a stored procedure. It can be executed like this
exec test @p = 1;
exec test @p = 2
exec test @p = n;
n can be hundred.
I want the sp being executed in parallel, not sequence. It means the 3 examples above can be run at the same time.
If I know the number in advance, say 3, I can create 3 different Execution SQL Tasks. They can be run in parallel.
However, the n is not static. It is coming from a table.
How can I execute Stored Procedures in PARALLEL and DYNAMICALLY ?
I think about using script task. In the script, I get the value of n, and the list of p, from the table, then running a loop with. In the loop, I create a threat and in the threat, I execute the sp like : exec test @p = p. So the exec test may be run parallel. But I am not sure if it works.
View 4 Replies
View Related
Sep 29, 2015
I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?
View 14 Replies
View Related
Jun 29, 2015
I have a scenario where we have to handle dynamically changing source columns.
For example , some times in the source files the number of columns will be increased or decreased, new columns can be added in the middle or in the end of the source file.
How to handle this kind of scenario in the SSIS ?
View 9 Replies
View Related
Aug 10, 2015
I am using the following script to check existence of table in the Database and create it dynamically...
This is working when table not existed, it error-ed when the table existed...
This script i am using in the Exec Sql Task.....
[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50)
declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not
set correctly, or connection not established correctly.
declare @ODSDB varchar(50)
declare @SQLSTMT varchar(max)
set @ODSDB = 'SampleDB'
begin
set @SQLSTMT = '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')
[Code] ...........
View 8 Replies
View Related
Jun 27, 2015
how to create excel with sheetname and column name dynamically
View 4 Replies
View Related
Sep 11, 2015
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
View 4 Replies
View Related
May 29, 2015
how do you load the multiple flat files to into destination dynamically?
View 9 Replies
View Related
Jul 9, 2015
I've created a SSIS package which takes a matrix from Excel file and insert into SQL table. It works perfectly! However, if I would add a new column into that matrix in Excel. Unpivot tool should take into process dynamically. Is there a way to provide this automatically?
View 4 Replies
View Related
Jun 4, 2014
I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
View 10 Replies
View Related
Sep 30, 2015
I am using the below code to send HTML Email body to multiple recipients and CC, its working fine. Now i have to attach multiple files in that mail. Is there any possibilities to attach multiple files with the below code else provide any other code to achieve this task.
Code:
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
[code]...
View 6 Replies
View Related
Jul 12, 2012
I have create packages which loads the data from flat file to sql server table, now I want to make my destination table connection dynamic what is format of connection string. I also need to pass user name and password for sql server dynamically in this case, what is the format for the connection string.
Also in package i used ADO.net as source for *.mdb files how i can set the commection to .mdb files dynamically which is used as source in my package.
View 8 Replies
View Related
Sep 4, 2015
I want to export the data into multiple sheets with same template, all the worksheets have to split dynamically with specific Sheet Name and template also copied to all other sheets
For Example:
Sheet Name: Guru
Name Age
Guru 24
Sheet Name: Johnson
Name Age
Johnson 32
it goes on......
View 5 Replies
View Related
Oct 13, 2015
I am facing an issue that Data flow task failing after loading 29000 rows out of 2lakhs rows.
I am loading data from .csv file to OLE DB Destination.
This data flow task is placed inside For each loop container.
is this issue because of any performance issue in SSIS packages such as buffer size.
find the error below:
DFT Load Data from FlatFile:Error: The conditional operation failed.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "DER Add Calc Columns" failed because error code 0xC0049063 occurred, and the error row disposition on "DER Add Calc Columns.Outputs[Derived Column Output].Columns[M_VALUE_NUM]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "DER Add Calc Columns" (48) failed with error code 0xC0209029 while processing input "Derived Column Input" (49). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[code]....
View 8 Replies
View Related
Jul 16, 2015
Can I assign values to variables in 2012 using below command? I have used the same command in 2008 and it works fine.
DTEXEC
/SERVER"XXXXXXXXSQLSERVER2012"/SQL"Mypackage.dtsx"/SETPackage.Variables[FilePath].Value;"C:Test estvariable.csv"
Wondering is there a different way in 2012 to pass values to variables dynamically.
View 2 Replies
View Related
Oct 13, 2015
We have a SQL enterprise server 2014 with two installed instance.
First instance for SharePoint 2013 DBs and second instance Dynamics AX 2012 R3 DBs.
The server has 32 GB of RAM, currently the process usage for first instance is 26 GB and second instance is 700 MB.
Which mean that SharePoint DBs in the first instance is consuming most of the RAM.
My question is there any way to assign a fixed RAM usage for a specific instance without affecting the other instance?
for example 16 GB only first instance and 16 GB to second instance.
View 6 Replies
View Related
Sep 2, 2015
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
View 2 Replies
View Related
